Point-in-time recovery in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start the MySQL binary log recovery from a specific point in time.

MySQL

mysqlbinlog --start-datetime='[1]' mysql-bin.000001 | mysql -u root -p

Drag options to blanks, or click blank then click option'

A2023/01/01 12:00:00

B2023-01-01

C12:00:00

D2023-01-01 12:00:00

Attempts:

3 left

💡 Hint

Common Mistakes

Using only the date without time.

Using slashes instead of dashes in the date.

Using only the time without the date.

The --start-datetime option requires a full date and time in the format YYYY-MM-DD HH:MM:SS to specify the exact point to start recovery.

2fill in blank

medium

Complete the command to stop the binary log recovery at a specific position.

MySQL

mysqlbinlog --stop-position=[1] mysql-bin.000002 | mysql -u root -p

Drag options to blanks, or click blank then click option'

A1000

Bstart

Cend

Dnow

Attempts:

3 left

💡 Hint

Common Mistakes

Using keywords like 'start' or 'end' instead of a number.

Using non-numeric values.

The --stop-position option requires a numeric position in the binary log to stop applying events.

3fill in blank

hard

Fix the error in the command to recover data up to a specific date and time.

MySQL

mysqlbinlog --stop-datetime='[1]' mysql-bin.000003 | mysql -u root -p

Drag options to blanks, or click blank then click option'

A15:00:00 2023-02-28

B2023/02/28 15:00:00

C2023-02-28 15:00:00

D2023-02-30 15:00:00

Attempts:

3 left

💡 Hint

Common Mistakes

Using invalid dates like February 30th.

Swapping date and time order.

Using slashes instead of dashes.

The date must be valid. February 30th does not exist, so use February 28th. The format must be YYYY-MM-DD HH:MM:SS.

4fill in blank

hard

Fill both blanks to create a command that recovers data between two points in time.

MySQL

mysqlbinlog --start-datetime='[1]' --stop-datetime='[2]' mysql-bin.000004 | mysql -u root -p

Drag options to blanks, or click blank then click option'

A2023-03-01 08:00:00

B2023-03-01 18:00:00

C2023-03-02 08:00:00

D2023-02-28 23:59:59

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping start and stop times.

Using stop time earlier than start time.

The --start-datetime should be earlier than --stop-datetime. Here, recovery starts at 8 AM and stops at 6 PM on March 1st, 2023.

5fill in blank

hard

Fill both blanks to create a command that recovers data from a specific position to a specific datetime.

MySQL

mysqlbinlog --start-position=[1] --stop-datetime='[2]' mysql-bin.000005 | mysql -u root -p

Drag options to blanks, or click blank then click option'

A1500

B2023-04-10 14:30:00

C2000

D2023-04-10 12:00:00

Attempts:

3 left

💡 Hint

Common Mistakes

Using a date where a number is expected.

Using an invalid date format.

The --start-position is a numeric log position to begin recovery, and --stop-datetime is the date and time to stop recovery.

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