Post
Ask Question

RESYNC SLAVE WITH MASTER SERVER IN MYSQL REPLICATION

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

  1. mysql> stop slave;  
  2. mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;  
  3. mysql> -- manually apply your fix here ....  
  4. mysql> start slave;  
  5. 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.
  1. mysql> stop slave;  
on the master
  1. SHOW master STATUS\G  
  2. *************************** 1. row ***************************  
  3. File: mysql-bin.000346  
  4. Position: 1278  
  5. Binlog_Do_DB: mydb  
  6. Binlog_Ignore_DB:  
  7. 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
  1. mysql> CHANGE master TO MASTER_LOG_FILE='mysql-bin.000346', MASTER_LOG_POS=1278;  
  2. mysql> start slave;  
  3. mysql> SHOW slave STATUS\G  
This should do the trick.