(mysql.info) point-in-time-recovery-positions

Info Catalog

(mysql.info) point-in-time-recovery-times

(mysql.info) point-in-time-recovery

 
 5.10.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.
 

Info Catalog

(mysql.info) point-in-time-recovery-times

(mysql.info) point-in-time-recovery