Restore a MySQL Database to a Specific Point in Time (2026)

Restoring a MySQL database to a specific point in time is a surprisingly delicate dance, and it’s not about simply picking a date and time from a calendar. The real trick is understanding that you’re not restoring a backup; you’re replaying transactions on top of a full backup.

Let’s say you’ve got a critical production database and a rogue DELETE statement just wiped out your entire users table. You need to get that table back, but you can’t afford to lose the transactions that happened after your last full backup and before the disaster.

Here’s a typical scenario:

The Setup:

Full Backup: backup_full.sql taken at 2023-10-27 08:00:00.
Binary Logs (Binlogs): MySQL has been actively writing transaction logs since the full backup. These are named sequentially, e.g., mysql-bin.000001, mysql-bin.000002, etc.
Disaster: DELETE FROM users; executed at 2023-10-27 10:35:15.
Target Restore Time: 2023-10-27 10:35:00 (just before the bad DELETE).

The Process:

Ensure Binlogs are Enabled: This is non-negotiable for point-in-time recovery. If they weren’t enabled, you can only restore to the exact moment of your last full backup.
- Diagnosis: Check your my.cnf or my.ini file for log_bin and binlog_format.
- Fix: If log_bin is commented out or not present, add/uncomment log_bin = /var/log/mysql/mysql-bin.log and set binlog_format = ROW (ROW is generally preferred for PITR). Restart MySQL.
- Why it works: The log_bin directive tells MySQL to record every committed transaction in sequentially named files. binlog_format=ROW ensures that each row affected by a transaction is logged, making it precise for replaying.
Identify the Correct Binlog Files and Position: You need to know which binlog files contain the transactions between your full backup and your target restore time.
- Diagnosis:
  - First, find the binary log file and position corresponding to your full backup. When mysqldump creates a backup, it typically outputs the binlog position it was at. If you didn’t capture this, you’ll need to look at the binlog files themselves.
  - Let’s assume your full backup was taken at 2023-10-27 08:00:00 and your mysql-bin.log files start from mysql-bin.000001.
  - Use mysqlbinlog to inspect the contents of your binlogs.
```
mysqlbinlog --start-datetime="2023-10-27 08:00:00" /var/log/mysql/mysql-bin.000001 /var/log/mysql/mysql-bin.000002 ... | less
```
  - Scroll through the output. You’re looking for events that occurred after your full backup. The output will show timestamps and SQL statements.
  - Locate the transaction just before your target restore time (2023-10-27 10:35:00). Note the end_log_pos for that transaction. Let’s say it’s 12345 in mysql-bin.000002.
- Fix: You’ll use these values in the restore command. The crucial pieces are the binlog file(s) and the position after the last transaction you want to keep. So, if your target is 10:35:00 and the last transaction before that ends at position 12345 in mysql-bin.000002, you’ll use mysql-bin.000002 and position 12345.
Prepare the Target Environment: You need a clean MySQL instance or a database where you can apply the backup and binlogs.
- Diagnosis: Ensure you have a MySQL server running. If you’re restoring to an existing database, you’ll need to stop writes to it immediately to prevent further divergence from your desired point in time.
- Fix:
  - Option A (New Database/Server): Set up a fresh MySQL instance. This is safer.
  - Option B (Existing Database): Stop all application writes to the database. You can do this by stopping application servers, or by revoking write privileges for the relevant users:
```
FLUSH TABLES WITH READ LOCK;
-- Run this from a separate mysql client
-- This locks all tables and prevents writes until you UNLOCK TABLES.
```
Restore the Full Backup: Load your backup_full.sql into the target environment.
- Diagnosis: The restore might fail if the database already exists and has data.
- Fix:
  - If restoring to a new database:
```
mysql -u root -p production < backup_full.sql
```
  - If restoring to an existing (but locked) database, you might need to drop tables first or use a temporary database name, then rename. For simplicity, assume a new database or a dropped/recreated one.
- Why it works: This brings the database to the state it was in at 2023-10-27 08:00:00.
Apply Binary Logs Up to the Target Time: This is where the point-in-time magic happens. You’ll use mysqlbinlog to read the transactions from the binlogs and pipe them into the mysql client.
- Diagnosis: If you apply the wrong binlog files or the wrong positions, you’ll either miss data or apply the bad transaction.
- Fix:
```
mysqlbinlog --stop-datetime="2023-10-27 10:35:00" /var/log/mysql/mysql-bin.000001 /var/log/mysql/mysql-bin.000002 | mysql -u root -p production
```
  Or, if you identified a specific stop position:
```
mysqlbinlog --stop-position=12345 /var/log/mysql/mysql-bin.000002 | mysql -u root -p production
```
  - Crucially, if you had to manually identify the start position from your full backup:
```
# Find the position in mysql-bin.000001 that's just after 08:00:00
# Let's say it's position 500
mysqlbinlog --start-position=500 --stop-datetime="2023-10-27 10:35:00" /var/log/mysql/mysql-bin.000001 /var/log/mysql/mysql-bin.000002 | mysql -u root -p production
```
- Why it works: mysqlbinlog reads the recorded transactions. The --stop-datetime or --stop-position flags tell it to stop just before that exact moment or position. Piping this output to mysql executes those transactions sequentially on your target database, effectively replaying history up to your desired point.
Release the Lock (if applicable): If you used FLUSH TABLES WITH READ LOCK, you need to release it.
- Diagnosis: The database will remain read-only until the lock is released.
- Fix:
```
UNLOCK TABLES;
```
  - Why it works: This command removes the global read lock, allowing writes again.
Verify: Check your users table. It should contain the data it had at 2023-10-27 10:35:00, before the DELETE statement.

The next error you’ll hit is if your binlog files are missing or corrupted, preventing you from replaying transactions beyond your last full backup.