You can restore your database to its state at an arbitrary time using the binary log files included in the backups. The process assumes that following conditions are met:
The backed-up MySQL Server has had its binary logging enabled (which is true by default for MySQL 8.0). To check if this condition has been satisfied, perform this query on the server:
mysql> SHOW VARIABLES LIKE 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec)
A series of backups, consisting typically of a full backup followed by a series of incremental backups, has been created for the server. The last backup in the series covers the targeted point in time for recovery. The example below illustrates such a typical case.
The last backup in the backup series you have taken include in itself the relevant binary log files. (To ensure this requirement is satisfied, do not use any of the following MySQL Enterprise Backup options when creating the backup:
These are the steps for a point-in-time recovery:
Restore the series of backups to the server, except for the last incremental backup in the series (which covers the targeted point in time for recovery). When finished, note the binary log position to which you have restored the server. The information is available from the
backup_variables.txtfile in the restored data directory of the server: look for the value of the entry
binlog_positionin the file. For example:
This means after the restore of the backup series, the server is now at log position 426 found in the binary log file
binlog.000012. You will need the information later.Note
While the last binary log position recovered is also displayed by InnoDB after the restore, that is not a reliable means for obtaining the ending log position of your restore, as there could be DDL events and non-InnoDB changes that have taken place after the time reflected by the displayed position.
Extract the binary log from the last incremental backup in the backup series (that is, the backup that covers the targeted point in time for recovery). You do this by unpacking the incremental backup image into a backup directory using the
image-to-backup-dircommand; for example:
mysqlbackup --backup-dir=incr-backup-dir2 --backup-image=incremental_image2.bi image-to-backup-dir
Next, go into the resulting backup directory (
incr-backup-dir2in this example) and, under the data directory inside, find the binary log file[s] (
binlog.000012in this example):
incr-backup-dir2$ ls datadir binlog.000012 ibbackup_logfile mysql pets undo_002 ...
Roll forward the database to its state at the targeted point in time for recovery, identified as
tRin this example, using the binary log file extracted in the last step. Then, using the mysqlbinlog utility, replay to the server the SQL activities recorded in the binary log file[s], from the log position the server has been restored to in Step 1 above (which is 426 in our example) all the way to time
tR. Specify the range of binary log events to replay using the
--start-positionoption and the
--stop-positionoption (which indicates the corresponding binary log position for
tR), and pipe the output to the mysql client:
mysqlbinlog --start-position="binary-log-position-at-the-end-of-backup-restores" \ --stop-position="binary-log-position-corresponding-to-tR" \ binary-log-filename | mysql -uadmin -pNotes
--stop-datetimeoption to specify the range of binary log segment to replay is not recommended: there is a higher risk of missing binary log events when using the option. Use
If you have more than one binary log files in your incremental backup and they are all needed for bringing the server up to its state at
tR, you need to pipe all of them to the server in a single connection; for example:
mysqlbinlog --start-position="426" --stop-position="binary-log-position-corresponding-to-tR" \ binlog.000012 binlog.000013 binlog.000014 | mysql -u admin -p
You can also dump all the mysqlbinlog output to a single file first, and then pipe or play the file to the mysql client.
For more explanations on using the binary log for point-in-time recovery, see Point-in-Time (Incremental) Recovery Using the Binary Log.
Check that the server has been restored to the desired point in time.