What is the fastest method to restore MySQL replication?

Question

I have a MySQL (5.1) master-slave replication pair and replication to the slave has failed. It failed because the master ran out of disk space and the relay-logs became corrupt. The master is now back online and working properly. Since there is this error in the log the slave process can't simply be restarted.

The server has a single 40GB InnoDB database and I would like to know what is the fastest method for getting the slave back in sync to minimize downtime.

Answer 1

You can try reset slave mysql command to ignore the current log file. However, you should be careful as this may result in data loss/inconsistency.

I think the safe way is to rebuild the replication from the beginning to make sure you have no data loss/inconsistency.

Copying the data dir is much more faster than exporting database using mysqldump.

First, lock the tables using:

> FLUSH TABLES WITH READ LOCK;

Then, copy the data dir. So, the downtime will be the time of copying the data dir. For a more complete guide, have a look at this. You need to copy the data dir instead of using mysqldump. You need to fix the permissions after copying the data files/folders onto the slave server.

Answer 2

If you want to start all over on the slave (which I think is safest), use myqldump with the --master-data switch. Your dump will now contain CHANGE MASTER TO MASTER_LOG_FILE and MASTER_LOG_POS. Load the the dump into your slave and your good to go.