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:
CHANGE MASTER TO MASTER_HOST=’IP_ADDRESS’, MASTER_USER=’UserCreatedOnMasterServerForReplication’, MASTER_PASSWORD=’PasswordForUserOnMasterServer’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=106;
SHOW SLAVE STATUS \G
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;