Point-in-time recovery in MySQL - Step-by-Step Execution

Concept Flow - Point-in-time recovery

Start: Backup available

↓

Restore full backup

↓

Apply binary logs from backup time

↓

Stop at desired point-in-time

↓

Database recovered to specific moment

Point-in-time recovery restores a database by first loading a full backup, then applying changes from logs up to a chosen time.

Execution Sample

MySQL

mysql> STOP SLAVE;
mysql> RESET SLAVE ALL;
mysqlbinlog --stop-datetime='2024-06-01 10:30:00' binlog.000001 | mysql -u root -p
mysql> START SLAVE;

This sequence stops replication, resets logs, applies binary logs up to a specific time, then restarts replication.

Execution Table

Step	Action	Input/Condition	Result	Notes
1	Restore full backup	Backup file	Database restored to backup state	Starting point for recovery
2	Stop replication	STOP SLAVE command	Replication paused	Prevents new changes during recovery
3	Reset slave logs	RESET SLAVE ALL command	Slave logs reset	Clears old logs to avoid conflicts
4	Apply binary logs	mysqlbinlog with --stop-datetime='2024-06-01 10:30:00'	Changes applied up to 10:30 AM	Recovers data changes after backup
5	Start replication	START SLAVE command	Replication resumed	Database ready for normal operations
6	End	All steps done	Database recovered to point-in-time	Recovery complete

💡 All binary logs applied up to specified stop time, recovery finished

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	Final
Database state	Corrupted or outdated	Restored to backup	Replication paused	Logs reset	Logs applied up to 10:30	Replication running	Recovered to point-in-time

Key Moments - 3 Insights

Why do we stop replication before applying binary logs?

Stopping replication (Step 2) prevents new changes from arriving during recovery, ensuring the database state matches the recovery point exactly.

What happens if we don't specify --stop-datetime when applying binary logs?

Without --stop-datetime (Step 4), all changes in the logs are applied, which may recover data beyond the desired point-in-time, losing the ability to recover to a specific moment.

Why reset slave logs before applying binary logs?

Resetting slave logs (Step 3) clears old logs to avoid conflicts and ensures only relevant logs are applied during recovery.

Visual Quiz - 3 Questions

Look at the execution table, at which step is the database restored to the backup state?

A Step 4

B Step 1

C Step 2

D Step 5

💡 Hint

Check the 'Result' column in Step 1 for database restoration

According to the variable tracker, what is the database state after applying binary logs?

A Logs applied up to 10:30

B Replication paused

C Logs reset

D Recovered to point-in-time

💡 Hint

Look at the 'After Step 4' column for 'Database state'

If you forget to stop replication before recovery, what could happen?

A Binary logs will not apply

B Recovery will stop at the correct point-in-time

C New changes might overwrite recovery progress

D Backup will be corrupted

💡 Hint

Refer to key moment about stopping replication before applying logs

Concept Snapshot

Point-in-time recovery:
1. Restore full backup.
2. Stop replication to pause changes.
3. Reset slave logs to clear old data.
4. Apply binary logs up to desired time.
5. Restart replication.
This recovers data to a specific moment.

Full Transcript

Point-in-time recovery in MySQL involves restoring a full backup first, then applying binary logs to replay changes up to a chosen time. The process starts by restoring the backup, then stopping replication to prevent new changes. Next, slave logs are reset to avoid conflicts. Binary logs are applied with a stop time to recover data precisely. Finally, replication is restarted to resume normal operations. This method helps recover data exactly as it was at a specific moment, useful after accidental data loss or corruption.