Resync slave with master server in MySQL replication

From Cosmin's Wiki

Jump to: navigation, search

Home > MySQL > Resync slave with master server in MySQL replication



There are situation when a slave server goes out of sync with the master. There might be several reasons for this, so I'm not going to go through this. Of course, when such a situation happens, it's always best to look at what went wrong. For this, the easiest solution would be to look at the 'Last Error' value from

SHOW slave status\G
.

A more complete history of what happened is of course to have a look at the log files:

grep mysql /var/log/syslog

Option 1

If there is only 1 log entry that you want to skip, because of an error that occured and you want to fix it manually on the slave, then you could do it like this:

mysql> stop slave;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> -- manually apply your fix here ....
mysql> start slave;
mysql> SHOW slave STATUS\G

Option 2

If there are too many problems to fix, you always have the option of resync-ing the entire database/databases. For this: on the slave:

mysql> stop slave;

on the master:

SHOW master STATUS\G
*************************** 1. row ***************************
            File: mysql-bin.000346
        Position: 1278
    Binlog_Do_DB: mydb
Binlog_Ignore_DB:
1 row IN SET (0.00 sec)

then backup your database from the master and restore it to the slave. Now, back to the slave:

mysql> CHANGE master TO MASTER_LOG_FILE='mysql-bin.000346', MASTER_LOG_POS=1278;
mysql> start slave;
mysql> SHOW slave STATUS\G

This should do the trick.