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.
  • Was that slave ever written to, other than from replication? Are there collation differences? – Rick James Jun 12 '15 at 23:51
  • I am only using this slave as a replica of the master. I've had a couple of false starts, so it has hosted data before but I have purged the databases after previous replication failures. – Stefan Lasiewski Jun 17 '15 at 21:45
  • SHOW CREATE TABLE – Rick James Jun 18 '15 at 22:35

Can I assume that 'INSERT INTO "Member" ("Created") VALUES (NOW())' is triggering the error here?

Yes that's exactly what the error telling

Error 'Duplicate entry '8072' for key 'PRIMARY'' on query. 
Default database: 'DATABASE'. Query: 'INSERT INTO "Member" ("Created") VALUES (NOW())', 
Error_code: 1062

Can I get replication to work without skipping rows with SET GLOBAL sql_slave_skip_counter = 1;?

Yes, You can go and delete the row with the ID 8072 in the table.. you can add the option of slave-skip-errors = 1062 in my.cnf and restart mysql

But you have to be extremely wary about doing this, as the referential integrity is there for a reason, and without it, your slave could not be satisfactorily promoted to take the place of the master in the case of something going wrong with your master hardware...

The only clean way is:

In the master

  • You FLUSH TABLES WITH READ LOCK; in the database you want to replicate

  • SHOW MASTER STATUS and copy the file name and the position

  • Excute a full backup to the database you want to replicate
  • Then release the tables with UNLOCK TABLES;

In slave

  • Restore the databse
  • Excute this CHANGE MASTER TO MASTER_HOST = 'masterhost', MASTER_USER = 'masteruser', MASTER_PASSWORD = 'masterpass', MASTER_LOG_FILE = 'filename', MASTER_LOG_POS = 'position';
  • Then start slave;

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.