MySQL Point-in-Time Recovery Using Binary Logs

Point-in-time recovery (PITR) in MySQL allows you to restore a database to a specific moment in time, minimizing data loss after an accidental deletion, corruption, or system crash. This process requires a previous full database backup and the binary logs recorded since that backup. By restoring the backup and then replay-playing the binary logs up to a specific timestamp or log position, you can reconstruct the database to its exact state right before the failure occurred.

Step 1: Ensure Prerequisites are Met

To perform a point-in-time recovery, your MySQL server must have had binary logging enabled before the data loss occurred. This is controlled by the log_bin system variable. You also need a full backup of the database created at a known point in time, along with the specific binary log filename and position corresponding to when that backup was taken.

Step 2: Restore the Full Backup

First, restore the database to the state it was in when the last full backup was created. This provides the baseline data. If you are using a logical backup created with mysqldump, restore it using the following command:

mysql -u [username] -p [database_name] < backup.sql

Do not allow users to write to the database during this recovery process to prevent data inconsistencies.

Step 3: Identify the Recovery Target

Next, you must find the exact point where you want to stop recovering data. This is typically right before the damaging event (e.g., an accidental DROP TABLE statement).

You can inspect the contents of your binary logs using the mysqlbinlog utility to search for the specific time or log position of the unwanted query:

mysqlbinlog --start-datetime="2023-10-25 09:00:00" /var/lib/mysql/binlog.000001 | grep -i "drop table" -B 3 -A 3

This command helps you identify either the exact timestamp (--stop-datetime) or the exact log position (--stop-position) of the bad transaction. Recovery by position is highly recommended because timestamps can overlap if multiple transactions occur within the same second.

Step 4: Apply the Binary Logs

Once you have the target stop point, use mysqlbinlog to extract the transactions from the binary logs and pipe them directly into the MySQL client to execute them.

If you are recovering up to a specific timestamp:

mysqlbinlog --stop-datetime="2023-10-25 10:15:30" /var/lib/mysql/binlog.000001 /var/lib/mysql/binlog.000002 | mysql -u [username] -p

If you are recovering up to a specific log position (safer and more precise):

mysqlbinlog --stop-position=154832 /var/lib/mysql/binlog.000001 /var/lib/mysql/binlog.000002 | mysql -u [username] -p

Note that you must list all binary log files in chronological order, starting from the log file that was active when the backup was taken up to the log file containing the recovery target.

Step 5: Verify the Database State

After the command completes, log into your MySQL server and inspect the restored schemas and tables. Confirm that the data has been successfully recovered to the desired state and that the erroneous query was not executed. Once verified, you can safely re-enable user access to the database.