Point in Time Recovery (or PITR for short) enables restoration of data changes until a certain point in time. In MySQL, point in time recovery can be useful if, for example, a table holding important data was deleted and you want to restore the server to right before the time that happened. We will discuss two ways of performing point in time recovery – we will look at how we can use the binary log and how we can perform point in time recovery using event times.
Performing Point In Time Recovery Using the Binary Log
In a nutshell, to restore data using the binary log, you need to:
- Restore the last full backup (our earlier blog post regarding Incremental and Full Backups in MySQL should provide some insight on how full backups work) taken before the point in time and remember the binary log position up to which you have restored the server. Restart the server;
- Find the binary log position that matches the time you want to restore your data. Use the
--stop-datetimeoptions to achieve this (these options tell MySQL to start and stop reading the binary log at specified positions). For example:
$ mysqlbinlog --start-datetime=”2020-01-01 00:00:00” --stop-datetime=”2020-01-01 00:01:00” --verbose /var/lib/mysql/bin.123 | grep -C 15 “ALTER TABLE”
The above query would tell MySQL to start reading the binary log at 2020-01-01 00:00:00 and stop reading at 2020-01-01 00:01:00. In this case, the binary log would be searched for the ALTER TABLE query (replace the query according to your needs). You would also need to know the rough time frame when the action you want to restore occurred and replace the dates according to your needs, then scan the output for contents around that time frame.
- Apply the events found in the binary log file to the server:
$ mysqlbinlog --start-position=100 --stop-position=200 /var/lib/mysql/bin.123 | mysql -u root -p
Replace the values of the –
--stop-positionsuch that the
--start-positionrepresents the binary log position noted in step 1 and –stop-position represents the byte point in the log file (the binary log position found in step 2).
- If you want to restore all statements only after a specific point in time, omit the –stop-position:
mysqlbinlog --start-position=100 /var/lib/mysql/bin.123 | mysql -u root -p
Performing Point in Time Recovery Using Event Times
To specify the start and end times for recovery, specify the
--stop-datetime options. Event times for recovery can be useful if you know that, for example, at 2020-01-01 on 00:00:00 your database crashed and you want to restore data up until 2020-01-01 23:59:00. Usage example:
$ mysqlbinlog --stop-datetime=”2020-01-01 23:59:00” /var/log/mysql/bin.123 | mysql -u root -p
You can also recover the activity that occurred after a specific time by specifying the –start-datetime parameter:
$ mysqlbinlog --start-datetime=”2020-01-01 23:59:00” /var/log/mysql/bin.123 | mysql -u root -p
When the above command is run, all SQL statements made from 23:59:00 onwards will be re-executed.Point in time recovery of MySQL & MariaDB data can also be accomplished via ClusterControl – this blog post on Severalnines should provide more information on how to accomplish that.
Point in Time Recovery (PiTR) in MySQL refers to the recovery of data changes that occurred since a given period of time. We covered two ways that could help you perform point in time recovery – these are performing point in time recovery using the binary log and using event times. You might also want to see how to perform point in time recovery with ClusterControl.