How to Use MySQL Enterprise Backup (MEB) for point-in-time recoveries (PITR)

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.

Creating Backups

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).

Caution: Do not rely on the binary logs on the MySQL host itself as being available for a point-in-time recovery. If the disk gets damaged or there is file corruption, you will need to restore the binary logs from a backup as well as the data.

With the data and binary logs backed up, you can do a point-in-time recovery if needed.

Point-in-Time Recovery

A point-in-time recovery consists of the following steps:

1. Restore the backup.
2. Determine which binary logs are required.
3. Retrieve the required binary logs from a backup or from the local disk.
4. Replay the binary logs.

Tip: If you have the required binary logs on the host you are restoring to, then keep a copy of them instead of deleting them before the restore.

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

The endpoint of the restoration depends on your need. If you need to restore all updates, you need to replay the binary log until the end of the last file you have. If you need to stop before a specific event - such as one deleting data that should not be deleted - you need to use the mysqlbinlog utility to determine the end position of the last event before the one you do not want to apply.

Finally, you need to apply the binary logs. Consider the case where you need to apply the following events:

• binlog.000008 from position 775 (as in the example backup_variables.txt output)
• binlog.000009 in its entirety
• binlog.000010 until position 123456

Then you can perform the restore similar to the following command:

$ mysqlbinlog --start-position=775 --stop-position=123456 binlog.000008 binlog.000009 binlog.000010 | mysql --user=root --password

Tip: If you need to replay a large amount of binary log, then in MySQL 5.7 and later, it is possible to replay them in parallel. However, this is an advanced technique and if you plan to use it, please make sure you practice it thoroughly before performing a restore of a production system.