Script Day: automatically locate the next valid transaction in MySQL binlog

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

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

Enhanced by Zemanta
  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++ []

One Response to “Script Day: automatically locate the next valid transaction in MySQL binlog”

  1. klarson:

    Hello,

    Please feel free to add your blog to the planet.mysql.com site.
    http://planet.mysql.com/new

Leave a Reply