I found that when replication breaks and the slave has corrupted, it’s best to start from scratch. I use just two commands to be able to do this properly.

The first is performed on the master and copies over the specified database ‘dbname’ below to the slave machine via SSH.


mysqldump -uroot --single-transaction --routines --triggers dbname | gzip -c | ssh user@slave_machine "cat > /directory/dbname.sql.gz"


Simultaneously, make sure you run ‘show master status’ on the master and take a note of the position of the binlog prior to starting the dump.

Then, when you’re on the slave, make sure the replication has stopped by running ‘stop slave’. Drop any existing data that you have on the db, then import the new dump into MySQL.

On the master, using the same position and information you grabbed from running ‘SHOW MASTER STATUS’ on the master, update the code block below accordingly.


RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=12345678;


You should then run ‘SHOW MASTER STATUS’ and watch the position catch up with the master and depending how far ‘behind’ replication is, you’ll need to wait for some time for replication to fix.