0

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

Community
  • 1
myk00
  • 73
  • 6

5 Answers5

1

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

theotherreceive
  • 8,235
  • 1
  • 30
  • 44
1

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.

quanta
  • 50,327
  • 19
  • 152
  • 213
Craig
  • 1,354
  • 6
  • 14
0

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!

mlschechter
  • 321
  • 1
  • 3
0

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.

malonso
  • 335
  • 2
  • 9
0

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

Martin
  • 506
  • 2
  • 4
  • 13