We had to rebuild a slave, that mysteriously had the slave reset?!?! This environment is a single master and a single slave. The slave was just a backup server, and with it failed for over 3 days replaying bin-logs wasn’t an option. We needed a fast way, with little impact to production to get a snap shot of 125G of data and move it over. RSYNC seemed to be our best option, but still getting the final sync would take some significant downtime. A new DBA’s to the team brought the idea to the table of using rsync to a local directory and when the first rounds were done shut down MySQL record the last bin-log (so we have the start slave position on the next bin-log) do a final rsync and start MySQL. This turned out to be a great idea, so we went forth. After all said and done we rebuilt the slave with a minimal impact to customers. We moved the newly create directory to the slave and was able to get replication going. I have added the steps below that we achieved a rebuild of a production slave with little downtime.

Step 1:

Determine if you have enough disk space available on the master to have 2 copies of the same data.

Step 2:

Create a directory to make the copy. /var/tmp/mysql_backup

Step 3:

Start a rsync from the original directory to the temporary directory

Step 4:

Do a few more rsync to get as close as possible to exact data(the original delta takes time to create)

Step 5:

Shutdown MySQL

Step 6:

Locate last binlog and record the name

Step 7:

Do a final rsync

Step 8:

Start MySQL

Step 9:

Move duplicate data from master server to slave server using one of the methods here.

Step 10:

Start slave, and set up replication.