Documentation Home
MySQL Enterprise Backup 3.9 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 0.9Mb
PDF (A4) - 0.8Mb

5.3 Point-in-Time Recovery from a Hot Backup

Using MySQL Enterprise Backup on its own, you can restore your data as it was at certain moments in time: every N hours, every day at 2 AM, and so on depending on your backup schedule. To reproduce data based on an arbitrary time somewhere in between backup jobs, you can use MySQL Enterprise Backup in combination with the MySQL binary log feature.

To recover the database to a specific point in time:

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

  • Find the binary log position that corresponds to the time of the backup. InnoDB only stores the binary log position information to its tablespace at a transaction commit. To make InnoDB aware of the current binary log position, you must run at least one transaction while binary logging is enabled. When you run the apply-log operation on your backup, mysqlbackup prints the latest MySQL binary log position the backup knows of. Also, mysqld prints it when you start it on the restored data:

    $ mysqld --defaults-file=/export/mysql/my.cnf
    040122 15:41:57  InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Last MySQL binlog file position 0 27183537, file name ./binlog.000005
    mysqld: ready for connections.

    The MySQL version must be >=  5.1.

    The printed position is the MySQL binary log byte position from the moment when MySQL Enterprise Backup finished copying your data files.

  • Use the mysqlbinlog to dump all the SQL activity that happened after the binary log position of the backup. Direct the output of the mysqlbinlog command to an output file, instead of piping it directly to mysql. This output file contains timestamps for all SQL statements in the binary log.

    $ mysqlbinlog --start-position=27183537 /sqldata/binlog.000005 >partial_binlog
    $ mysqlbinlog /sqldata/binlog.000006 >> partial_binlog 
    $ mysqlbinlog /sqldata/binlog.000007 >> partial_binlog 
    $ mysqlbinlog /sqldata/binlog.00000N >> partial_binlog
  • In an editor, remove all statements after the point in time to which you intend to restore.

  • Replay the SQL statements to update the backup data with the same operations that happened after the backup. Process the modified file with mysql, like this:

    $ mysql < partial_binlog 
  • To recover the database to the latest possible time, skip the steps of saving the mysqlbinlog output in a file and removing recent SQL statements from it. Pipe the output from mysqlbinlog --start-position=... directly to mysql to replay all the SQL statements after the last backup.

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
Sign Up Login You must be logged in to post a comment.