MySQL Point in Time Recovery: How Does it Work?

If you have ever worked with MySQL, you have probably heard the term “Point-in-Time Recovery” or PITR for short. PITR enables you to restore a database up to a certain time that you can specify using certain commands and it refers to the recovery of data changes made since a given point in time. PITR in the context of databases involves a set of actions that allows a DBA or a developer to restore or recover a set of data or a setting from a time in the past.

When is PITR Useful?

Point-in-Time Recovery can be useful if you know that, for example, on a certain date (e.g Monday) at 12:00 a query was executed that accidentally deleted (dropped) a table. In that case, you could use PITR to restore the server right before the query execution.

How to Perform PITR?

In order to perform PITR, here’s what you have to do:

1. Restore the last full backup created the point-in-time you want to recover your database to.
2. Find the binary log event position that matches the point in time up to which you want to restore your MySQL data.
3. Apply the events in the binary log file to the server starting with the log position you found before.

In general, here’s how everything goes:

1. Restore the full backup.
2. Use mysqlbinlog to find the log position you need to use:
   mysqlbinlog --start-datetime="2021-01-01 12:00:00" --stop-datetime="2021-01-01 12:05:00" --verbose /var/lib/mysql/bin.123456 | grep -C 12 "DROP TABLE"
3. This query should provide you with the approximate location of the statement – for example, assume the statement could be found between the lines #1000 and #2000.
4. Apply the events in the binary log file to the server:
   mysqlbinlog --start-position=1000 --stop-position=2000 /var/lib/mysql/bin.123456 | mysql -u root -p

All of the transactions from the starting position until just before the stop position should be recovered.

MySQL Point-in-Time recovery can also be accomplished using the binary log. For more information, check the MySQL documentation.

Summary

MySQL point in time recovery is a very important feature of MySQL that enables you to restore your data up to a certain point in time. In order to actually make use of the feature, restore a full backup and use the benefits provided by mysqlbinlog. To find more information about backing up MySQL, consider reading the Backup Ninja’s blog.