Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?)

share|improve this question
    
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

5 Answers 5

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

share|improve this answer

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.

share|improve this answer

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!

share|improve this answer

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.

share|improve this answer

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

slave-skip-errors=1062

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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