Databases Reference
In-Depth Information
When a binary log is corrupt, how much data you can recover depends on the type of
corruption. There are several common types:
Bytes changed, but the event is still valid SQL
Unfortunately, MySQL cannot even detect this type of corruption. This is why it
can be a good idea to routinely check that your replicas have the right data. This
might be fixed in a future version of MySQL.
Bytes changed and the event is invalid SQL
You might be able to extract the event with
mysqlbinlog
and see garbled data, such
as the following:
UPDATE tbl SET col?????????????????
Try to find the beginning of the next event, which you can do by adding the offset
and length, and print it. You might be able to skip just this event.
Bytes omitted and/or the event's length is wrong
In this case,
mysqlbinlog
will sometimes exit with an error or crash because it can't
read the event and can't find the beginning of the next event.
Several events corrupted or were overwritten, or offsets have shifted and the next event
starts at the wrong offset
Again,
mysqlbinlog
will not be much use.
When the corruption is bad enough that
mysqlbinlog
can't read the log events, you'll
have to resort to some hex editing or other tedious techniques to find the boundaries
between log events. This usually isn't hard to do, because recognizable markers separate
the events.
Here's an example. First, let's look at log event offsets for a sample log, as reported by
mysqlbinlog
:
$
mysqlbinlog mysql-bin.000113 | egrep '^# at '
# at 4
# at 98
# at 185
# at 277
# at 369
# at 447
A simple way to find offsets in the log is to compare the offsets to the output of the
following
strings
command:
$
strings -n 2 -t d mysql-bin.000113
1 binpC'G
25 5.0.38-Ubuntu_0ubuntu1.1-log
99 C'G
146 std
156 test
161 create table test(a int)
186 C'G
233 std
243 test
