Skipping Errors in Binary Log with mysqlbinlog

Short Version:

When running…

mysqlbinlog mysql-bin.000123 | tee progress.000123 | mysql

…we encounter SQL errors (duplicate key). How can we get it to skip those errors and continue processing the binary log instead of stopping and exiting?

Long Version:

I recently had a hardware failure (bad sectors on a disk) that resulted in MySQL crashing and a corrupted ib_logfile. We were unable to run MySQL without setting force-innodb-recovery=6. Even with this setting set, we were unable to read data out of our largest table because it was in an inconsistent state without the logged transactions.

I’m attempting to recover from a month-old backup and binary logs. It’s going smoothly except for the following problem:

On occasion, MySQL reports a duplicate key error when executing one of the queries, which causes mysqlbinlog to stop reading the log. We are running the processes like so:

mysqlbinlog mysql-bin.000123 | tee progress.000123 | mysql

When this dies with the duplicate key error, I tail progress.000123, find the start of the last transaction in that file (which may mean we missed a few transactions, which is okay), and start executing again with a --start-position of where we left off.

I don’t know why there are errors at all in the binary log unless transactions were recorded twice in the log, but the pressing question is how can we get it to continue on after receiving an error? We have 38 GB of logs, and when it gets stuck in the middle of the night, it just extends how long it is taking us to get back up and running.

Thanks in advance; I’ve been searching for a solution for about a week since we started the recovery.

you can collect all your binlog statements in the file progress.000123 and then use some script to add the following to all the insert statements

ON DUPLICATE KEY IGNORE

HTH,

-srini

Actually mysqlbinlog does not stop after error, mysqlbinlog just converts log file to text format, nothing more. The problem is that mysql client stops after error. Please try ‘mysql -f’.

2 Likes