Specify Recovery Position

• 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 log position numbers rather than dates are specified.
  • Using position allows for a more precise recovery.

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

  mysqlbinlog --start-date="2007-06-07 7:59:00" \
  

    --stop-date="2007-06-07 8:10:00" \
  

    /var/log/mysql/bin.1234 > /tmp/mysql_restore.sql

  • Open the file with a text editor and look for the offending statement.
  • 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.

  mysqlbinlog --stop-position="30122" /var/log/mysql/bin.1234 \
  

    | mysql -u root -p
  

  mysqlbinlog --start-position="30125" /var/log/mysql/bin.1234 \
  

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

How do I do table maintenance?

---

---

© 2007 John Michael Pierobon

Notes