MySQL Point in Time Recovery

Point in Time Recovery (PITR) is a feature in MySQL that allows you to recover your database to a specific point in time.

This is useful when you accidentally delete or update data in your database and want to recover it to a previous state. In this post, I will show you how to perform a Point in Time Recovery in MySQL.

1 Scenario

Let’s say you have a database named mydb and you accidentally deleted a table named mytable at 2024-06-15 12:00:00. You want to recover the database to a point in time before the deletion occurred.

I am going to replicate this scenario by creating a database named mydb and a table named person and then drop the databse:

mysql> create database mydb;
Query OK, 1 row affected (0.03 sec)

mysql> use mydb;
Database changed
mysql> CREATE table person(id int not null, name varchar(255), primary key(id));
Query OK, 0 rows affected (0.08 sec)

To make it more realistic, let’s insert some data into the table:

mysql> INSERT INTO person VALUES (1, 'alice'), (2, 'bob');
Query OK, 3 rows affected (0.01 sec)
mysql> select * from person;
+----+-------+
| id | name  |
+----+-------+
|  1 | alice |
|  2 | bob   |
+----+-------+
2 rows in set (0.00 sec)

Now, let’s drop the database:

mysql> drop database mydb;
Query OK, 1 row affected (0.01 sec)

Assuming I dropped the database at 2024-06-15 14:58:00. Now, let’s recover the database to where it was before the deletion occurred.

2 Recover the Database to a Specific Point in Time

2.1 Step 1: Check if Binary Logging is Enabled

First and foremost, we need to check if binary logging is enabled in our MySQL server. We can do this by running the following command:

SHOW VARIABLES LIKE '%log_bin%';

If binary logging is enabled, you will see something like this:

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql_bin       |
| log_bin_index                   | /var/lib/mysql/mysql_bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

Binary logging is used to record changes to the database so that you can use it for recovery purposes. In most cases, binary logging is enabled by default. If binary logging is not enabled before the deletion occurred, you won’t be able to perform a Point in Time Recovery. Sorry, you’re out of luck.

2.1.1 Binary log format

There are three binary log formats in MySQL:

1. STATEMENT: Logs the SQL statement that made the changes -> not always safe but it is faster and requires less disk space.
2. ROW: Logs the changes to individual rows -> always accurate but requires more disk space.
3. MIXED: Logs the changes in either STATEMENT or ROW format, depending on the statement.

Which format to use depends on your use case. The default format is ROW.

2.1.2 Binary log retention

You can set the retention period for binary logs using the expire_logs_days variable. This variable specifies the number of days before binary logs are automatically deleted. This option is crucial to determine how far back you can recover your database.

mysql> show variables like '%expire_logs_days%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 7     |
+------------------+-------+
1 row in set (0.01 sec)

2.2 Step 2: Restore the Database from a most recent backup

Before you can recover the database to a specific point in time, you need to identify and restore the most recent full backup taken before the specific time you want to recover to. This is because you need a base state of the database from which you can apply incremental changes (from binary logs) to reach the desired point in time.

For example, if you dropped the database at 8:00 AM on Monday, you need to restore the database using the backup from 11:59 PM on Sunday. Then, you apply incremental changes from 11:59 PM on Sunday to just before 8:00 AM on Monday, using the binary logs to roll forward to the precise point in time before the database was dropped.

mysql -u root -p < backup.sql

In this post, I won’t restore the database from a full backup because all the data of my database is in the binary log file. But in a real-world scenario, you need to restore the database from a full backup before you can apply changes from the binary log files.

2.3 Step 3: Find the Position in the Binary Log File

Next, we need to find the binary log file that contains the data we want to recover. We can do this by running the following command:

SHOW BINARY LOGS;

The ouput is something like this:

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql_bin.000003 |      6582 |
| mysql_bin.000004 |      2084 |
+------------------+-----------+
2 rows in set (0.00 sec)

In this case, the binary log file that contains the data we want to recover is mysql_bin.000004. Now, we need to find the position in the binary log file where the drop database mydb command was executed. Given that the deletion occurred sometime between 2024-06-15 14:57:00 and 2024-06-15 14:59:00, we can use the following command to find the position:

mysqlbinlog --start-datetime="2024-06-15 14:57:00" --stop-datetime="2024-06-15 14:59:00" ./mysql_bin.000004 | grep -C 12 "drop database"

The output will look something like this:

/*!*/;
# at 1015
#240615 12:58:14 server id 20001160  end_log_pos 1080 CRC32 0xbd018834 	Anonymous_GTID	last_committed=5	sequence_number=6	rbr_only=no	original_committed_timestamp=0	immediate_commit_timestamp=0	transaction_length=0
# original_commit_timestamp=0 (1970-01-01 08:00:00.000000 +08)
# immediate_commit_timestamp=0 (1970-01-01 08:00:00.000000 +08)
/*!80001 SET @@session.original_commit_timestamp=0*//*!*/;
/*!80014 SET @@session.original_server_version=0*//*!*/;
/*!80014 SET @@session.immediate_server_version=0*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1080
#240615 12:58:14 server id 20001160  end_log_pos 1174 CRC32 0x9cab636d 	Query	thread_id=948945	exec_time=0	error_code=0
SET TIMESTAMP=1718431094/*!*/;
--
#240615 14:58:44 server id 20001160  end_log_pos 1992 CRC32 0xe87b3c2f 	Anonymous_GTID	last_committed=9	sequence_number=10	rbr_only=no	original_committed_timestamp=0	immediate_commit_timestamp=0	transaction_length=0
# original_commit_timestamp=0 (1970-01-01 08:00:00.000000 +08)
# immediate_commit_timestamp=0 (1970-01-01 08:00:00.000000 +08)
/*!80001 SET @@session.original_commit_timestamp=0*//*!*/;
/*!80014 SET @@session.original_server_version=0*//*!*/;
/*!80014 SET @@session.immediate_server_version=0*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1992
#240615 14:58:44 server id 20001160  end_log_pos 2084 CRC32 0x8308d0e4 	Query	thread_id=957579	exec_time=0	error_code=0
SET TIMESTAMP=1718438324/*!*/;
SET @@session.pseudo_thread_id=957579/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
drop database mydb
/*!*/;

We can see that starting from position 1992 in the binary log file mysql_bin.000004, the drop database mydb command was executed. We need to restore the database to a point before this position.

2.4 Step 4: Restore the Database to a Specific Point in Time

Now that we have the binary log file and the position, with no further ado, let’s restore the database. Assuming we have a backup of the database ending at 1015, we can use the following command to restore the database from that position to 1992:

mysqlbinlog --start-position=1015 --stop-position=1992 mysql_bin.000004 | mysql -u root -p

This command will read the binary log file mysql_bin.000004 starting from position 1015 and ending at position 1992 and apply the changes to the database.

Now if we check the database, we will see that the mydb database has been restored and all the data is back:

mysql> use mydb;
Database changed
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| person         |
+----------------+
1 row in set (0.00 sec)

mysql> select  * from person;
+----+-------+
| id | name  |
+----+-------+
|  1 | alice |
|  2 | bob   |
+----+-------+
2 rows in set (0.00 sec)

2.4.1 Restoring from Multiple Binary Log Files

If your backup was created before the changes in mysql_bin.000004, you can still restore the database by applying changes from multiple binary log files. For instance, if your last full backup was taken before the changes recorded in mysql_bin.000002, you need to apply the changes from both mysql_bin.000002 and mysql_bin.000003 to your database:

mysqlbinlog mysql_bin.000002 mysql_bin.000003 | mysql -u root -p

2.5 Further steps

After you have recovered the database to a specific point in time, if you also want to reaply the changes that were made after the point in time you recovered to, you can use the binary logs to apply the changes.

To determine the position in the binary log file immediately after the execution of the drop database mydb command (which is at position 1992), we need to examine the binary log file again. Here’s the relevant section of the log:

# at 1992
#240615 14:58:44 server id 20001160  end_log_pos 2084 CRC32 0x8308d0e4 	Query	thread_id=957579	exec_time=0	error_code=0

From this log entry, we can see that the next position after the drop database mydb command is 2084. We can use this position to apply the changes made after the drop command:

mysqlbinlog --start-position=2084 mysql_bin.000004 | mysql -u root -p

3 Conclusion

Dayum, that was a long process. But it’s worth it :) . We have successfully recovered our database to a specific point in time using binary log. Remember to always have backups and binary logging enabled to avoid potential data loss.