I'm trying to setup MySQL replication but I am getting duplicate keys when I start the slave.
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?