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.

(more…)

  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 []