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>
- 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.
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.