We're using pretty simple MASTER->SLAVE MySQL query-based replication.

Every midnight a cron script on the slave stops the replication SQL_THREAD, backs up the slaves databases with mysqldump and then restarts the replication SQL_THREAD.

After replication restarts though, it immediately stops, with this errornous output in SHOW SLAVE STATUS\G

Last_Errno: 1062
Last_Error: Error 'Duplicate entry '54149' for key 1' on query. Default database: 'dbname'. Query: 'insert into emails (emailsubject,emailmessage,html) values (blah','blah', false);'

It seems like the problem is that the replication log on the slave is replaying statements it has already processed.

MySQL bug tracker seems to agree with me: http://bugs.mysql.com/bug.php?id=38205#c200483

Because replication seems like such a basic and standard procedure, my question is: how do we get around replication trying to replay statements?

Addendum: I wanted to use the tags 'start-stop' and 'replaying' and 'statements' to create this post, but my reputation isnt high enough (seems silly?)

  • I have added the tags you wanted. The more you put in the more you are allowed to do here. – Richard Holloway Jul 6 '10 at 16:08

Do you need to stop replication to run your dump, mysqldump can get a global read lock with the --lock-all-tables option.


We have a similiar setup and run backups using mysqldump, without stopping replication.

mysqldump --host=$HOST -u $USER --password=$PASSWORD --max_allowed_packet=512M --port=3306 --single-transaction --skip-add-locks --quick --master-data=2

This works for us.


The bug thread you referenced seems to indicate that the problem will not affect InnoDB tables, and has been fixed as of 5.1.35. Am I reading it correctly?

What table type are you using, and what release of MySQL? Also, are you using transactions?

Hope this helps!


Stupid question but do you "address" the error each time you see it - by setting sql_slave_skip_counter = 1 for example - and then successfully restart the slave? If the error is never addressed, the position in the replication logs will remain the same, and you will run into this error every time until appropriate steps are taken.


You could configure MySQL so that replication continues past this particular error:


See: http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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