Sometimes the MySQL replication breaks due to some corruption in the binary log files(1). When your binary log files are corrupted, the only option (other then trying to rebuild a database of hundreds of gigabytes) is to try to skip over the corrupted region and get the slave to pick up from where the transactions are valid.
Locating the correct position in the binary log from which the server can carry on is difficult but can be made easier by the
mysqlbinlog utility that can scan the binary log files and show you which position is valid using the
--start-position to try random positions in the binary log file and see which position will let you read from the file(2).
Still, testing each and every possible byte position by hand is very time consuming, not to say very boring. The following small script will allocate the next readable position in the binary log, given a beginning position (that you can see in the MySQL slave status IO position).
start=1008443439 end=$(($start+20000)) binlog=mysql-bin.021468 for(3); do if mysqlbinlog -vv $binlog --start-position=$pos --stop-position=$end > /dev/null 2>&1; then echo $pos break fi done
[All numbers are made up, obviously – substitute your own]
This will echo the first position in the specified MySQL binary log, from which the log can be read correctly (assuming the corruption is smaller then 20K). You then can restart the slave after the corrupted part by issuing these commands to MySQL:
STOP SLAVE; CHANGE MASTER TO MASTER_HOST='my-master', MASTER_USER='this-slave', MASTER_PASSWORD='my-password', MASTER_LOG_FILE='mysql-bin.021468', MASTER_LOG_POS=1008444916; STOP SLAVE;
[Where 1008444916 is the position the script reported]
Important note: This is only relevant when it’s affordable to lose few transactions (you should pay attention to the distance between the last position of the IO thread and the position that this script find as usable). If you fear that there may be important data in the missing transactions – don’t use this method and do rebuild your database.
- MySQL Replication Setup (sqldbpool.com)
- How To Set Up SSH Encrypted MySQL Replication (kozar.wordpress.com)
- R1Soft Discusses CDP Backup Software for MySQL Databases at 2011 MySQL Conference and Expo (prweb.com)
- Five Things You Need to Know About MySQL (pcworld.com)