Unexpected SLAVE STATUS after CHANGE MASTER

Question

I'm trying to setup MySQL replication but I am getting duplicate keys when I start the slave.

I've got master and relay. I've also got backup, which is intended to be slaved to relay, but my issue is on relay. I create a dump from master using the following:

mysqldump \
    --all-databases \
    --flush-logs \
    --single-transaction \
    --master-data \
    | gzip > dump.sql.gz

This dump includes something like the following:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=120;

I then import the dump into relay, and execute the following:

FLUSH PRIVILEGES;
CHANGE MASTER TO
    master_host='master',
    master_user='username',
    master_password='secret';
START SLAVE;
SHOW SLAVE STATUS\G

I'm getting what I find to be unexpected results from the slave status:

Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 800917736
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 635
Relay_Master_Log_File: mysql-bin.000001
Last_Error: Could not execute Write_rows event on table myschema.mytable; Duplicate entry '5191176' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 778

A few things I don't understand:

• how does it have a duplicate key, when I just pulled in a fresh dump?
• why is it loading from mysql-bin.000001 when the CHANGE MASTER and Master_Log_File are mysql-bin.000012?
• how has it progressed to Read_Master_Log_Pos: 800917736 if it encountered an error on mysql-bin.000001?

Answer 1

So after fiddling more, I've discovered the order of CHANGE MASTER ... is important. Once I figured this out, I found this in the docs explaining in further detail.

If you specify the MASTER_HOST or MASTER_PORT option, the slave assumes that the master server is different from before (even if the option value is the same as its current value.) In this case, the old values for the master binary log file name and position are considered no longer applicable, so if you do not specify MASTER_LOG_FILE and MASTER_LOG_POS in the statement, MASTER_LOG_FILE='' and MASTER_LOG_POS=4 are silently appended to it.

MySQL docs - CHANGE MASTER

Answer 2

Answers to A few things You didn't understand:

1)How does it have a duplicate key, when I just pulled in a fresh dump?

• Issue here is that you added dump with latest data but slave is reading from a previous position on the log as mentioned by you in your answer , so there is a duplicate entry for that.

2)why is it loading from mysql-bin.000001 when the CHANGE MASTER and Master_Log_File are mysql-bin.000012?

• You seem to have answered this yourself , you will need a single comand to do this.

CHANGE MASTER TO master_host='master', master_user='username', master_password='secret' master_log_file='mysql-bin.000012', master_log_pos=120;

3)how has it progressed to Read_Master_Log_Pos: 800917736 if it encountered an error on mysql-bin.000001?

• Read_Master_Log_Pos is a number set for every file individually, should not be bothered by this number it will reset on next file.

Also as better approach to avoid duplicate entry errors use mysqldump with --single-transaction flag.

Thanks,