| Skipping Errors in Binary Log with mysqlbinlog [message #3078] |
Wed, 14 May 2008 10:01  |
|
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.
|
|
|
|
| Re: Skipping Errors in Binary Log with mysqlbinlog [message #3085 is a reply to message #3078 ] |
Fri, 16 May 2008 04:47  |
 debug
Messages: 99
Registered: March 2008 |
Member |
|
|
|
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'.
[Updated on: Fri, 16 May 2008 04:47]
Mikhail Solovyev, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
|
|
|
Members
Search
Help
Register
Login
Home
