If you want to be able to do a point-in-time recovery, you need to start your preparation already when creating the backup and then apply the correct steps during the restoration. This post will first discuss how to create the backups for point-in-time recoveries, then how to perform the restore.
In order to create a point-in-time recovery, you need both a backup and the changes made since the backup to the point you want to restore to:
- Base backup: This can be a single full backup or a combination of a full backup and differential/incremental backups.
- Changes since the backup: These are recorded in the binary log.
The backup should be created as normal backups. Whether you choose to only use full backups or also use differential or incremental backups should depend on your needs. In general, if your database is not very large, the simplicity of only using full backups is preferred over the more complex scheme with differential or incremental backups.
MySQL Enterprise Backup by defaults include the binary logs in the backup. However, what you need for a point-in-time recovery are the binary log events created after the backup. So, you should ensure you have a backup of the binary logs that is recent enough to fulfill your restore requirements. If it is acceptable that the latest backup is the most recent you can restore to, then you can just use the binary logs from the regular backups. However, if you need to be able to restore to a more recent point-in-time, then you need to backup your binary logs separately. Two options are:
- mysqlbinlog: The mysqlbinlog utility that ships with MySQL Server can be used to create near real-time backup (similar delay as for replication).
- Syncing: You can use a tool like rsync on Linux or Robocopy on Microsoft Windows to regularly copy the binary logs to your backup destination. This is particularly an option on Linux where rsync can include open files (Robocopy cannot).
With the data and binary logs backed up, you can do a point-in-time recovery if needed.
A point-in-time recovery consists of the following steps:
- Restore the backup.
- Determine which binary logs are required.
- Retrieve the required binary logs from a backup or from the local disk.
- Replay the binary logs.
Once the backup has been restored, you need to determine which point in the binary logs the backup corresponds to. The best solution is to look in the backup_variables.txt file in the meta directory of the backup directory, for example:
$ grep binlog_position meta/backup_variables.txt binlog_position=binlog.000008:775
- binlog.000008 from position 775 (as in the example backup_variables.txt output)
- binlog.000009 in its entirety
- binlog.000010 until position 123456
$ mysqlbinlog --start-position=775 --stop-position=123456 binlog.000008 binlog.000009 binlog.000010 | mysql --user=root --password