I am migrating away from some old MySQL 5.1 servers to some new MySQL 5.6 servers. During this process, I'm creating a new MySQL 5.6 slave from an existing MySQL 5.1 slave, using the procedure in the mysqldump reference guide.
For example, if my MySQL 5.1 servers are named 'master1' and 'replica1' and I have a new MySQL 5.6 server named 'replica2', the following should make replica2 a second slave of 'master1':
replica2 % mysqldump --login-path=replica1 --all-databases --dump-slave --include-master-host-port --apply-slave-statements --lock-all-tables --add-drop-database > all.sql
replica2 % mysql < all.sql
And this seems work well, but replication fails with the following error complaining about duplicate entries for the primary key.
2015-06-12 10:00:00 1234 [ERROR] Slave SQL: Worker 0 failed executing transaction '' at master log mysql-bin.009332, end_log_pos 12341234; Error 'Duplicate entry '8072' for key 'PRIMARY'' on query. Default database: 'DATABASE'. Query: 'INSERT INTO "Member" ("Created") VALUES (NOW())', Error_code: 1062
Can I assume that 'INSERT INTO "Member" ("Created") VALUES (NOW())' is triggering the error here? Can I get replication to work without skipping rows with SET GLOBAL sql_slave_skip_counter = 1;
?
Some additional details:
- I'm using classic MySQL replication, and GTIDs are currently disabled.
- The MySQL 5.1 servers are using STATEMENT-based replication, but the new MySQL 5.6 servers are using ROW-based replication.
- I don't own the application code, and I cannot change the SQL.
SHOW CREATE TABLE