Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

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

migrated from stackoverflow.com Mar 4 '12 at 8:52

2 Answers

up vote 0 down vote accepted

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.

share|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
up vote -2 down vote

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:

SELECT * FROM `table_name` WHERE 1 ORDER BY id DESC LIMIT 10;
share|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

 
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.