Recovering From Corrupted MySQL Binlog Files

The WunderCounter.com has a replicated MySQL setup. Today I had a slave MySQL database server crash and the slave would not restart after reboot. The error was the following:

Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave

By using the mysqlbinlog utility, I was able to confirm that the one of the binlogs downloaded from the master was indeed corrupt and this is where the slave had gotten stuck.

In the past, I was under the impression that there was nothing that could be done. AFAIK, the log files can not be repaired, so what I did the last time this happened was simply to wipe out the slave databases and to restart replication fresh. This isn't problematic in a lot of cases, but the WunderCounter databases are around 40 GB in total. That's a lot of data to copy and transfer to a different machine. The copying isn't even the real issue. You need to issue a "READ Lock" on the master machine while you create a snapshot. In the case of the WunderCounter, it's not really a viable option to shut things down for this length of time.

After doing some looking around today, I came across the following helpful info in the first comment on this blog:

In the case of a corrupt binlog on the slave server, there is no need to delete all logs and start over. Run a “show slave status;” and record two entries: Relay_Master_Log_File and Exec_Master_Log_Pos. Then execute:

stop slave; CHANGE MASTER TO master_log_file='(binlog name in Relay_Master_Log_File', master_log_pos=(exec_master_log_pos number); start slave;

This will re-read the binlog from the master and you should be all set. This especially helps when you’ve purged older binlogs from the master server.

This is confirmed at mysql.com, even though it's buried in the following article.

In my case, I stopped the slave server and issued a "show slave status" to get the master_log_file and master_log_pos info. I used the listed info in the CHANGE MASTER TO statement and then started the slave. My slave immediately came back online and then began catching up on 3 hours of data remaining to be synced.

This saved me a massive amount of unnecessary work. I hope someone else will find it helpful as well.