Point-in-Time Recovery

Restore the database to a specific second by replaying binary log events.

In this lesson, you will learn how to surgically restore your database to a precise second by replaying specific events from the binary log.

The Logic of "Rewinding"

Point-in-Time Recovery (PITR) is your safety net when an accidental DROP TABLE or unintended DELETE occurs after your last full backup. Since a snapshot backup (from lesson 24) only captures data as it existed at the moment the backup finished, you must bridge the gap between that backup and the current disaster using binary logs.

The binary log contains a sequential record of every data-modifying event. By identifying the exact timestamp or log position of the "oops" moment, you can tell the database to play back the logs up to—but not including—that specific second.

Executing the Recovery

The process follows a two-step workflow:

Restore the Snapshot: First, restore your most recent full backup to a clean environment. At this point, your data is "frozen" at the time the backup was taken.
Replay the Binary Logs: Use the mysqlbinlog utility to extract and execute the SQL statements from the binary logs that occurred after the backup, stopping right before the error.

For example, if you discovered an error at 2023-10-27 14:00:00, you would run a command similar to this:

mysqlbinlog --stop-datetime="2023-10-27 13:59:59" /var/lib/mysql/mysql-bin.00000X | mysql -u root -p

Why Precision Matters

The --stop-datetime flag is powerful because it allows you to define the exact end-point for replaying logs. However, if your binary logs are not properly configured (lesson 3) or if your retention policy (lesson 4) caused them to be purged, the recovery will be impossible. PITR is essentially "replaying history" until the exact moment you want to diverge from the past. Always review your logs using mysqlbinlog first to ensure the events you are replaying are what you expect before piping them directly into your database.

Key Takeaway: Point-in-Time Recovery allows you to undo data loss by restoring a full backup and then re-executing binary log events up to the precise second before the accidental transaction occurred.