Mysql – Unexpected SLAVE STATUS after CHANGE MASTER

MySQLreplication

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?

Best Answer

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

Related Question