Resync a double replicated MySQL Database

Posted by on November 1, 2011 in Database, MySQL | 0 comments

Resync a double replicated MySQL Database

Certain scenarios need a master database replicated to multiple slaves instead of just one. If you have a very heavy read application you might benefit from a number of slaves. It also provides a sort of double backup if one slave drifts away from the data it’s supposed to have. Double replication also allows you to test a backup system. I have a separate article dedicated to that.

With a double replication you’ll have a setup like the one shown above. Your slaves will be on separate machines. With replication they could even be on separate networks to decrease the chances of catastrophic loss. If one of the slaves gets off base, use the steps below to get it back online and sync’d.  We will assume that slave 2 is the machine that is out of sync for the example.

  1. Stop replication on both slaves.  (log into mysql and run “stop slave”)
  2. On slave 1 – the good slave – run “show slave status\G” – Take note of the master log file and position.
  3. On slave 2 – the bad slave – run “reset slave”.  This will make sure that replication does not automatically restart when you restart MySQL.  If replication automatically restarts, it will be starting from the wrong position on the master binaries.
  4. Copy the good database from slave 1 to slave 2.  If you have a large database, run mysqlhotcopy, or stop MySQL and copy the raw database files.  If you have a small database, you could use a text file mysql dump.
  5. Restart MySQL on both slaves.
  6. On Slave 1 – the good slave – you should just have to run “start slave”.  This will pick up the replication from the position where you stopped it at.
  7. On Slave 2 – you will need to run the commands below. If you run “SHOW SLAVE STATUS \G” afterwards, it should match Slave 1
CHANGE MASTER TO MASTER_HOST=’MasterIPAddress’, MASTER_USER=’slaveUserOnMaster’, MASTER_PASSWORD=’theslavespassowrdonmaster’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=106;

If you do this correctly, both slaves should now be sync’d with the master server. They may have to catch up if it took a while to complete the above actions, but your master will never be bothered while this is going on. That’s the great thing about multiple slaves.

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 *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>