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