Things n' Stuff Thoughts about the universe in general

Sometimes the MySQL replication breaks due to some corruption in the binary log files¹. 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².

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

Related articles

• 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)

1. I have yet to find a good explanation to why it happens and how to prevent that [↩]
2. because in the binary log transactions can have any size, so they can end and start at any point [↩]
3. pos=$start;$pos<$end;pos++ [↩]

This entry was posted on Wednesday, July 27th, 2011 at 20:08 and is filed under Articles, Script Day, Software. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.