MySQL Replication Re-Syncing

Posted by on October 26, 2011 in Database, MySQL | 0 comments

MySQL Replication Re-Syncing

I may be in the minority here, but I’ve spent alot of time resyncing my MySQL replication slaves. They just seem to drift away from an exact duplicate of the master. Here’s a quick list of tips to remember when performing this task.

  • Make sure you STOP the MySQL service on the master before you make your snapshot.  Otherwise you’ll have no idea where to start your slaves.  When you restart the service, you’ll see what master binary log you’re on, and you can tell your slaves to start at the beginning of that file which is position 106.
  • Make sure you STOP the MySQL service on the slaves before restoring your snapshot database.
  • If you have a sizable database and are not using mysqlhotcopy, remember to set the permissions of the data files and the owner to mysql.
  • If the slave was running stop the slave and reset it using “stop slave;” and “reset slave;” respectively, before you shut down the mysql service.  This way the slave won’t try to pick up where it left off on the master when you restart the service.
  • Use the “Show Table Status” command in mysql to check for corruption of your tables.  You can often repair these before they cause replication errors.
  • If you need to skip a query because it’s causing an error use: “stop slave;SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;start slave;”.  this will ignore the query causing the issue and move to the next one.  I only recommend doing this if you don’t want your slaves to perform the query because it doesn’t affect any data.  For instance, if you create a new user on the master that you won’t need on the slave.

Some useful commands:

STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST=’IP_ADDRESS’, MASTER_USER=’UserCreatedOnMasterServerForReplication’, MASTER_PASSWORD=’PasswordForUserOnMasterServer’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=106;
START SLAVE;
SHOW SLAVE STATUS \G
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

Leave a Comment

Your email address will not be published. Required fields are marked *