MySQL :: MySQL Enterprise Backup 8.0 User's Guide :: 5.3 Point-in-Time Recovery

version 8.0

4.1
4.0
3.12
3.11

3.11 Japanese

MySQL Enterprise Backup 8.0 User's Guide/.../ Point-in-Time Recovery

5.3 Point-in-Time Recovery

You can restore your database to its state at an arbitrary time using thebinary logfiles 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)
```
If the value oflog_binisOFF, binary logging has not been enabled. SeeThe Binary Logon how to enable binary logging for the server.
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:--skip-binlog,--use-tts,——无固定, or--start-lsn.)

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 thebackup_variables.txtfile in the restored data directory of the server: look for the value of the entrybinlog_positionin the file. For example:
```
binlog_position=binlog.000012:426
```
This means after the restore of the backup series, the server is now at log position 426 found in the binary log filebinlog.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 theimage-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 ast_Rin this example, using thebinary logfile extracted in the last step. Then, using themysqlbinlogutility, replay to the server the SQL activities recorded in thebinary logfile[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 timet_R. Specify the range of binary log events to replay using the--start-positionoption and the--stop-positionoption (which indicates the corresponding binary log position fort_R), and pipe the output to themysqlclient:
```
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 -p
```
Notes
- 使用the--start-datetimeor--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--start-positionand--stop-positioninstead.
- 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 att_R, 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 themysqlbinlogoutput to a single file first, and then pipe or play the file to themysqlclient.
For more explanations on using the binary log for point-in-time recovery, seePoint-in-Time (Incremental) Recovery.
Check that the server has been restored to the desired point in time.

PREV HOME UP NEXT