Point-in-time recovery in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Point-in-Time Recovery Purpose

What is the main purpose of point-in-time recovery (PITR) in MySQL databases?

A To restore the database to a specific moment before a failure or error occurred

B To create a full backup of the database at regular intervals

C To optimize query performance by indexing data

D To replicate data from one server to another in real-time

💡 Hint

Think about what you want to achieve when you want to undo recent changes after a problem.

Point-in-time recovery allows restoring the database to a precise moment, such as just before accidental data deletion or corruption.

❓ query_result

intermediate

Result of Applying Binary Logs for PITR

Given a MySQL database with binary logging enabled, what will be the result of applying binary logs from a backup taken at 10:00 AM to 11:30 AM for point-in-time recovery?

A The database will be restored to the state at 10:00 AM without any changes

B The database state will be restored exactly as it was at 11:30 AM

C The database will be restored to the latest backup ignoring binary logs

D The database will be corrupted due to conflicting data

💡 Hint

Binary logs record all changes after the backup time.

Applying binary logs from the backup time up to the target time replays all changes, restoring the database to that exact moment.

📝 Syntax

advanced

Correct Command to Start MySQL Recovery to a Specific Time

Which of the following commands correctly starts MySQL point-in-time recovery to the timestamp '2024-06-01 15:45:00' using mysqlbinlog?

MySQL

mysqlbinlog --start-datetime='2024-06-01 15:00:00' --stop-datetime='2024-06-01 15:45:00' /var/log/mysql/mysql-bin.000001 | mysql -u root -p

A mysqlbinlog --from='2024-06-01 15:00:00' --to='2024-06-01 15:45:00' /var/log/mysql/mysql-bin.000001 | mysql -u root -p

B mysqlbinlog --start-time='2024-06-01 15:00:00' --end-time='2024-06-01 15:45:00' /var/log/mysql/mysql-bin.000001 | mysql -u root -p

C mysqlbinlog --start='2024-06-01 15:00:00' --stop='2024-06-01 15:45:00' /var/log/mysql/mysql-bin.000001 | mysql -u root -p

D mysqlbinlog --start-datetime='2024-06-01 15:00:00' --stop-datetime='2024-06-01 15:45:00' /var/log/mysql/mysql-bin.000001 | mysql -u root -p

💡 Hint

Check the official mysqlbinlog options for specifying datetime ranges.

The correct options for mysqlbinlog to specify datetime ranges are --start-datetime and --stop-datetime.

❓ optimization

advanced

Optimizing Point-in-Time Recovery Performance

Which approach best optimizes the speed of point-in-time recovery when applying binary logs after restoring a full backup?

A Apply only the binary logs from the backup time up to the target recovery time

B Apply all binary logs from the beginning of time to the target recovery time

C Apply binary logs in random order to speed up processing

D Skip applying binary logs and restore only from full backups

💡 Hint

Applying unnecessary logs wastes time.

Applying only the relevant binary logs reduces recovery time by avoiding replaying unnecessary transactions.

🔧 Debug

expert

Diagnosing PITR Failure Due to Missing Binary Logs

You attempted point-in-time recovery but received an error indicating missing binary log files. What is the most likely cause?

A The recovery command syntax is incorrect

B The MySQL server version is incompatible with the backup

C Binary logs required for recovery were deleted or purged before recovery

D The full backup was corrupted and cannot be restored

💡 Hint

Think about what happens if logs needed to replay changes are not available.

If binary logs needed for point-in-time recovery are missing, the recovery cannot proceed because changes cannot be replayed.

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