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.

Practice

1. What is the main purpose of point-in-time recovery in MySQL?

easy

A. To create a full backup of the database

B. To restore the database to a specific moment using binary logs

C. To optimize query performance

D. To delete old data automatically

Solution

1. Step 1: Understand point-in-time recovery concept

   Point-in-time recovery allows restoring the database to a specific moment in time, usually after a backup, by replaying changes from binary logs.

2. Step 2: Identify the correct purpose

   Options A, C, and D describe other database tasks, not point-in-time recovery.

3. Final Answer:

   To restore the database to a specific moment using binary logs -> Option B

4. Quick Check:

   Point-in-time recovery = restore specific moment [OK]

Hint: Point-in-time recovery = restore using binary logs [OK]

Common Mistakes:

• Confusing point-in-time recovery with full backup
• Thinking it optimizes queries
• Assuming it deletes data automatically

2. Which of the following is the correct syntax to start point-in-time recovery using mysqlbinlog from a binary log file named binlog.000001 starting at '2024-06-01 10:00:00'?

easy

A. mysqlbinlog --start-datetime='2024-06-01 10:00:00' binlog.000001 | mysql -u root -p

B. mysqlbinlog --stop-datetime='2024-06-01 10:00:00' binlog.000001 | mysql -u root -p

C. mysqlbinlog --start='2024-06-01 10:00:00' binlog.000001 | mysql -u root -p

D. mysqlbinlog --datetime='2024-06-01 10:00:00' binlog.000001 | mysql -u root -p

Solution

1. Step 1: Recall mysqlbinlog options for point-in-time recovery

   The correct option to specify start time is --start-datetime.

2. Step 2: Check the syntax correctness

   mysqlbinlog --start-datetime='2024-06-01 10:00:00' binlog.000001 | mysql -u root -p uses --start-datetime correctly. Options B, C, and D use incorrect or non-existent flags.

3. Final Answer:

   mysqlbinlog --start-datetime='2024-06-01 10:00:00' binlog.000001 | mysql -u root -p -> Option A

4. Quick Check:

   Use --start-datetime to begin recovery [OK]

Hint: Use --start-datetime to specify recovery start time [OK]

Common Mistakes:

• Using --stop-datetime instead of --start-datetime
• Using incorrect option names like --start or --datetime
• Omitting the binary log file name

3. Given the command:
mysqlbinlog --start-datetime='2024-06-01 09:00:00' --stop-datetime='2024-06-01 10:00:00' binlog.000002 | mysql -u root -p
What will this command do?

medium

A. Restore the entire binary log file without time filtering

B. Restore changes only after 10 AM on June 1, 2024

C. Create a backup of the binary log file

D. Restore all changes from the binary log starting at 9 AM until 10 AM on June 1, 2024

Solution

1. Step 1: Understand the command options

   The command uses --start-datetime and --stop-datetime to limit the replay of changes between 9 AM and 10 AM on June 1, 2024.

2. Step 2: Interpret the command effect

   It replays only the changes in that time window, restoring the database state for that period.

3. Final Answer:

   Restore all changes from the binary log starting at 9 AM until 10 AM on June 1, 2024 -> Option D

4. Quick Check:

   Start and stop datetime limit recovery range [OK]

Hint: Start and stop datetime limit replay range [OK]

Common Mistakes:

• Thinking it restores after stop time only
• Assuming it restores entire log ignoring time
• Confusing backup creation with recovery

4. You run the command:
mysqlbinlog --start-datetime='2024-06-01 08:00:00' --stop-datetime='2024-06-01 07:00:00' binlog.000003 | mysql -u root -p
What is the problem with this command?

medium

A. The stop datetime is earlier than the start datetime, causing an error

B. The binary log file name is incorrect

C. The command is missing the user password

D. The command will restore the entire binary log file

Solution

1. Step 1: Check the datetime order

   The --stop-datetime is set to '2024-06-01 07:00:00', which is before the --start-datetime '2024-06-01 08:00:00'. This is logically incorrect.

2. Step 2: Understand the effect of reversed times

   This causes mysqlbinlog to fail or produce no output because the stop time cannot be before the start time.

3. Final Answer:

   The stop datetime is earlier than the start datetime, causing an error -> Option A

4. Quick Check:

   Stop datetime must be after start datetime [OK]

Hint: Stop datetime must be after start datetime [OK]

Common Mistakes:

• Ignoring datetime order
• Assuming missing password causes this error
• Thinking binary log file name is wrong

5. You have a full backup taken at '2024-06-01 06:00:00' and binary logs from that time onward. You want to recover the database to '2024-06-01 09:30:00' after accidentally deleting some data at 9:45 AM. Which sequence of commands correctly performs point-in-time recovery?

hard

A. Restore full backup only without using binary logs

B. Run mysqlbinlog --start-datetime='2024-06-01 09:30:00' --stop-datetime='2024-06-01 09:45:00' binlog.* | mysql -u root -p only

C. Restore full backup, then run mysqlbinlog --start-datetime='2024-06-01 06:00:00' --stop-datetime='2024-06-01 09:30:00' binlog.* | mysql -u root -p

D. Run mysqlbinlog --stop-datetime='2024-06-01 09:30:00' binlog.* | mysql -u root -p without restoring backup

Solution

1. Step 1: Restore the full backup first

   Point-in-time recovery requires restoring the last full backup before applying binary logs.

2. Step 2: Replay binary logs up to the desired time

   Use mysqlbinlog with --start-datetime at backup time and --stop-datetime at recovery time to replay changes correctly.

3. Step 3: Verify the recovery time is before the accidental deletion

   Stopping at 9:30 AM avoids replaying the deletion at 9:45 AM.

4. Final Answer:

   Restore full backup, then run mysqlbinlog with start and stop datetime from backup to 9:30 AM -> Option C

5. Quick Check:

   Backup restore + replay logs to recovery time [OK]

Hint: Restore backup first, then replay logs up to recovery time [OK]

Common Mistakes:

• Skipping full backup restore
• Replaying logs after the unwanted change
• Trying to recover without backup