Resyncing a Slave to a Master in MySQL

Recently one of my clients had a problem with their MySQL setup. They were running a two server setup where one server was acting as a master server that was replicating to the second server which was acting as a slave. Unfortunately they ran a really bad query on their master server which pushed the load extremely high and made the server unresponsive. They had their hosting provider restart the master server, which unfortunately caused table corruption, so when the server restarted their database was in an unusable state.

This is where I came in. I shutdown the MySQL instance running on that server and began a myisamchk to correct the corruption. However I quickly realized that this was going to take a very long time, as they had an extremely large database. So we agreed to switch their application over from using the master database to the slave to limit the amount of downtime they would have.

Unfortunately when you switch to a slave your master and slave become out of sync. This was known when we did the switch, and was done to retain their uptime. So how do you re-sync your slave to the master, and get the replication up and going again? Here is my quick guide.

  1. Since the re-syncing of the servers will cause downtime, it is important to schedule a time to do the sync, and notify any major clients on your server that will be affected by taking down the database. A maintenance page should also be created if your load balancer doesn’t automatically display one.
  2. When it is time to do the sync you should setup your maintenance page to display from all sites that utilize the database.
  3. On the slave server log into mysql and make sure to reset the slave, before issuing a read lock.

    STOP SLAVE;
    RESET SLAVE;
    FLUSH TABLES WITH READ LOCK;

  4. Shutdown mysql on the slave server (shutdown the master server if it was running).
  5. If your application has a config file pointing to the database IP address, it is best to change that back to the master so that there is no chance writes start getting sent to the slave after you start back up the servers.
  6. Prepare the new mysql data directory on the master. What I generally do is backup the old data directory then create a new folder called mysql. I then copy the mysql database into this folder.
  7. Next you will want to dump/copy all your databases from the slave to the master. Mysqldump is the safest way to do this (yes you will need to start the slave for this), however with large datasets this usually is not a very time efficient, so I generally will just transfer all the database files. When transferring the files, make sure you don’t send the master.info file, any binary log or relay log files, as well as the mysql database. I find the fastest way to send data from one server to another is to use netcat. Here is a sample of how to use netcat to send a listing of files:On the Slave (from within the mysql data directory):

    tar c database1 database2 database3 … databasexx | nc -l 7878

    On the Master (from within the mysql data directory):

    nc slave-ip 7878 | tar xv

    This will tar all the databases and files then send to the master via netcat.

  8. Once this transfer is complete you can now start your master. Once it is started you will need to reset the master then gets the new coordinates for when you start the slave (if there are issues with the replication).

    RESET MASTER;
    SHOW MASTER STATUS;

    Record the information from the SHOW MASTER STATUS.

  9. Start the slave server. Then check to see if the server is replicating data. If it is replicating fine you are set, if not you will need to run the following command on the slave:

    CHANGE MASTER TO
    MASTER_HOST='host',
    MASTER_USER='user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='recorded_log_file_name',
    MASTER_LOG_POS=recorded_log_position;

  10. Release the table lock on the master server:

    unlock tables;

  11. Remove the maintenance page on your webservers and return your application to a usable state.

The step that takes the longest amount of time is generally the transfer of the database files. To estimate the amount of downtime you will have you can simply calculate how long it will take you to transfer the database from one server to another. Then add in a buffer in case of any problems that may arise in the process.

With the steps above you should be able to quickly and easily re-sync a master and slave, and have replication working between the two servers again.

Leave a Reply

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