Point-in-time recovery in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is Point-in-time recovery (PITR) in MySQL?

Point-in-time recovery is a method to restore a database to a specific moment in time, usually just before an unwanted event like data loss or corruption happened.

beginner

Which MySQL files are essential for performing point-in-time recovery?

You need a full backup of the database and the binary log files that record all changes made after the backup.

intermediate

How do binary logs help in point-in-time recovery?

Binary logs keep a record of all changes to the database, so you can replay these changes up to the exact time you want to recover.

intermediate

What is the general process to perform point-in-time recovery in MySQL?

1. Restore the full backup.
2. Apply binary logs using mysqlbinlog tool up to the desired time.
3. Stop applying logs at the recovery point.

intermediate

Why is it important to stop applying binary logs at the correct time during PITR?

Stopping at the correct time prevents restoring unwanted changes that happened after the problem, ensuring the database state is exactly as needed.

What does point-in-time recovery allow you to do?

A Delete old backups automatically

B Create a new database schema

C Optimize database queries

D Restore the database to a specific moment in time

Point-in-time recovery restores the database to a chosen moment, usually before an error or data loss.

Which MySQL feature records all changes to the database for PITR?

A Error logs

B Binary logs

C Slow query logs

D General logs

Binary logs record all data changes and are essential for point-in-time recovery.

What is the first step in point-in-time recovery?

A Restore the full backup

B Apply binary logs

C Stop the MySQL server

D Delete corrupted data

You start by restoring the full backup before applying any changes from binary logs.

Which tool is commonly used to apply binary logs during PITR?

A mysqldump

B mysqladmin

C mysqlbinlog

D mysqlcheck

The mysqlbinlog tool reads and applies binary log files for recovery.

Why must you stop applying binary logs at the correct time?

A To avoid restoring unwanted changes after the recovery point

B To speed up the recovery process

C To free up disk space

D To restart the MySQL server

Stopping at the right time ensures the database state matches exactly the desired recovery moment.

Explain the concept of point-in-time recovery and why it is useful.

Think about how you can undo changes up to a certain moment.

Describe the steps to perform point-in-time recovery in MySQL.

Consider the order of restoring and replaying changes.

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