5.9.3.2. Specifying Positions for Recovery
Instead of specifying dates and times, the
--start-position and
--stop-position options for
mysqlbinlog can be used for specifying log
positions. They work the same as the start and stop date
options, except that you specify log position numbers rather
than dates. Using positions may enable you to be more precise
about which part of the log to recover, especially if many
transactions occurred around the same time as a damaging SQL
statement. To determine the position numbers, run
mysqlbinlog for a range of times near the
time when the unwanted transaction was executed, but redirect
the results to a text file for examination. This can be done
like so:
shell> mysqlbinlog --start-date="2005-04-20 9:55:00" \
--stop-date="2005-04-20 10:05:00" \
/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
This command creates a small text file in the
/tmp directory that contains the SQL
statements around the time that the deleterious SQL statement
was executed. Open this file with a text editor and look for
the statement that you don't want to repeat. Determine the
positions in the binary log for stopping and resuming the
recovery and make note of them. Positions are labeled as
log_pos followed by a number. After
restoring the previous backup file, use the position numbers
to process the binary log file. For example, you would use
commands something like these:
shell> mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \
| mysql -u root -p
shell> mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 \
| mysql -u root -p
The first command recovers all the transactions up until the
stop position given. The second command recovers all
transactions from the starting position given until the end of
the binary log. Because the output of
mysqlbinlog includes SET
TIMESTAMP statements before each SQL statement
recorded, the recovered data and related MySQL logs will
reflect the original times at which the transactions were
executed.
