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 binlog position that corresponds to the time of the
backup. InnoDB only stores the binlog position information to
its tablespace at a transaction commit.
To make InnoDB aware of the current
binlog position, you must run at least one transaction while
binlogging is enabled. When you run the
apply-log operation on your backup,
mysqlbackup prints the latest MySQL binlog
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 binlog 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 binlog position of the
backup. Direct the output of the
mysqlbinlog command to an output file,
instead of piping it directly to
This output file contains timestamps for all SQL statements in
$ 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
--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.