Documentation Home
MySQL Enterprise Backup 4.1 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.3Mb
PDF (A4) - 1.3Mb
HTML Download (TGZ) - 194.1Kb
HTML Download (Zip) - 223.4Kb

5.2 Point-in-Time Recovery from a Hot Backup

Using MySQL Enterprise Backup and the binary log files included by default in the incremental backups it creates (except when they are created with the --use-tts option), you can restore your database to its state at an arbitrary time tR in between a full backup and an incremental backup or in between two incremental backups. To do so:

  1. Binary logging must be enabled in MySQL before taking the full backup that serves as the base for this restore operation.

  2. Using the full and incremental backups that were created before tR, restore the database to a time as close to tR as possible and note that time (let us call it tLB). Make sure the restored database is in a consistent state. This can be achieved by, for example, using the copy-back-and-apply-log command for backup restore.

  3. Roll forward the database to its state at the desired time tR using the binary log file[s] included in the incremental backup that covers tR. Use the mysqlbinlog utility to extract from the binary log file[s] all the SQL activities that happened in between the tLB (which you noted in step 2 above) and tR, specifying those times with the --start-datetime and --stop-datetime options, and pipe the output to the mysql client, to be replayed on the server:

    mysqlbinlog --start-datetime="tLB" \
             --stop-datetime="tR" \
             binlog.000005 binlog.000006 binlog.000007  |   mysql -u root -p

    An alternative is to identify the beginning and endpoint of the extraction not by the start and stop times, but by their corresponding binary log positions:

    mysqlbinlog --start-position="binary-log-position-corresponding-to-tLB" \
             --stop-position="binary-log-position-corresponding-to-tR" \
             binlog.000005 binlog.000006 binlog.000007  |   mysql -u root -p

    Note that you need to pipe all the binary log files in a single connection to the server. You can also dump all the SQL activities to a single file first, and then pipe or play the file to the mysql client.

For more tips on using the binary log for point-in-time recovery, see Point-in-Time (Incremental) Recovery Using the Binary Log.

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.