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-logoperation 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
mysql. This output file contains timestamps for all SQL statements in the binlog.
$ 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 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.