How To: Mysql Master Slave Resync

If you are using the nice master-slave replication feature of MySQL (available since early version 3.x if I remember well), it may happens that the slave may get out of sync for various reasons.
One typical error is like this:
090908 12:58:48 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)
090908 12:58:48 [ERROR] Got fatal error 1236: ‘Client requested master to start replication from impossible position’ from master when reading data from binary log
this error can happen for example on the slave if the master crashes and the binlog isn’t updated correctly in the fsck recovery.

So here is a quick description of how to resynchronize the slave in a single master single slave configuration.

This procedure involves a stop of the slave server, while the master will have the tables in lock mode for the time of taking a snapshot/export of your databases to be copied on the slave.

Step 1. On the Slave
Issue the following commands to mysql:
STOP SLAVE; # stop the Slave I/O threads
RESET SLAVE; # forget about all the relay log files
/etc/init.d/mysql stop # stop the database

Step 2. On the Master
Issue the following commands to mysql:
mysql> RESET MASTER; # reset the bin log counter and wipe out bin log files
mysql> FLUSH TABLES WITH READ LOCK; # flush buffers and LOCK tables (both MyISAM and InnoDB)
mysql> show master statusG
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 98
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

mysql>

Plaase take note of the file (mysql-bin.000001) and the position (98). You’ll need it later on the slave.

Now, don’t exit from the current mysql client, since you will release the lock, and we don’t want this. Take another shell and do the following:
– make a copy (gzipped tar for example) of the datafiles of MyISAM databases
– make an export of the InnoDB databases (via mysqldump –databases –lock-all-tables DBNAME1 [DBNAME2…] > dumpfile.sql ).
To reduce the down time of the master database, If you don’t have InnoDB databases and you are using LVM on the MySQL volume, you can for example take a snapshot instead of copying the datafiles (take the snapshot and then UNLOCK TABLES;).

Go back to the mysql client where you issued the “FLUSH TABLES WITH READ LOCK;” and type “UNLOCK TABLES;” or logout from the client.
Now the master is available again to mysql clients.

Step 3. Go back on the Slave
Verify that the slave mysql is STOPPED.
Copy the dumps and the archives of the databases you made in previous steps on the master to the slave.
Replace the datafiles of the MyISAM databases you got from the master. These steps may vary and can be done in different ways (scp of the datafiles, mysqldump from the slave connecting to the master and so on… that’s your choice .
Edit /etc/my.cnf and insert “skip-slave-start” in the mysqld section of the file to avoid the start of the slave I/O threads.
Start the mysql instance on the slave. Delete the InnoDB databases and import them from the SQL dumps.
Now issue the following to mysql using the right parameters taken from SHOW MASTER STATUSG above:
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
mysql> SLAVE START;

Verify that the the slave connects to the master and that is getting the binlog.
mysql> show slave statusG
....
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
....
Seconds_Behind_Master: 1634

Slave_IO_Running tells you that the slave is connected to the master, while Slave_SQL_Running says you that the slave is applying the binlog and updating the local datafiles. If they are both set to “Yes” all is fine, replication is working.
Seconds_Behind_Master is the “lag” in seconds from the slave to the master, and should decrease to 0 in a short time (depending also on the activity on the master from the UNLOCK TABLES instant).

Don’t forget to comment out the “skip-slave-start” in /etc/my.cnf so that next time mysql restart on the slave the I/O threads are automatically started.

That’s all folks. Beware that the “Seconds_Behind_Master” status counter is completely unreliable.

If you want to precisely monitor the lag between the master and the slave or better to periodically check the right sync status of the slave with the master, I’d suggest to use the utility from the Maatkit package, which is a must for a MySQL administrator.

Please refer to the MySQL Reference Manual about replication for more details.

To purge old binlog files I use this script running on the slave from root’s crontab once a week:

#!/bin/bash
CURRENT_LOGFILE=$(/usr/bin/mysql -e "SHOW SLAVE STATUSG" | awk '$1 == "Master_Log_File:" {print $2}')
/usr/bin/mysql -h MASTER -e "PURGE MASTER LOGS TO '${CURRENT_LOGFILE}'"
exit $?

((enjoy))