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:
- Restore the last full backup created the point-in-time you want to recover your database to.
- Find the binary log event position that matches the point in time up to which you want to restore your MySQL data.
- 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:
- Restore the full backup.
- 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"
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.
- 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.
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.