I'm new to MySQL load balancing, I want to try the master-slave concept.

Whenever I setup master-slave for the first time, and both have up-to-date fresh data, that works perfectly fine. However, whenever the master goes down, or I stop and then restart it, the slave starts generating errors because of duplicate keys.

Why would this be happening and how can I prevent this?

My application has a lot of update queries something like 5 SELECTs then 3 UPDATEs or INSERTs

My data will grow with time and I'm expecting quite a high load in the future.

  • Are you writing to the slave when the master goes down? – Aaron Brown Mar 4 '12 at 13:23
  • The next time replication breaks, please post your output from SHOW SLAVE STATUS\G. You cannot effectively troubleshoot something like this unless you start with the Last_error message. I say this because a duplicate key error would produce Last_errno : 1062 in the SHOW SLAVE STATUS\G but I would need to see Last_error. – RolandoMySQLDBA Aug 12 '12 at 23:04

Don't stop a master while any slaves are connected to it. If you need to stop it, then stop the slaves first and the master at the end. This way, you won't have any errors like the one you described.

| improve this answer | |
  • This is just plain common sense. I would like to add further to this: Before you STOP SLAVE on all slaves, I would run FLUSH LOGS on the Master. Since almost all DBAs and sysadmins hardly ever use sync_binlog=1 to perfectly (or rather ideally) flush binary logs with each write and rely on the operating system to do this, this IMHO causes binary log entries to get read twice. +1 for this answer. – RolandoMySQLDBA Sep 10 '12 at 17:47

Firstly, check the slave status with:

 show slave status;

If the slave is reporting something like ”Last_Error: Error Duplicate entry.....”, this can be fixed by the following:

stop slave;
set global sql_slave_skip_counter=1;
start slave;
show slave status;

It will take some time after issuing those commands for the slave to rebuild itself from the master, but you can check the progress by querying the db to see what record we are up to, for example:

| improve this answer | |
  • Setting the skip counter doesn't rebuild the slave from the master, it just skips one statement from executing on the slave. – Aaron Brown Aug 11 '12 at 19:37

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.