Binary log management in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to show the current binary log files in MySQL.

MySQL

SHOW [1];

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

ATABLES

BDATABASES

CBINARY LOGS

DSTATUS

Attempts:

3 left

💡 Hint

Common Mistakes

Using SHOW DATABASES instead of SHOW BINARY LOGS.

Using SHOW TABLES which lists tables, not logs.

The command SHOW BINARY LOGS; lists all binary log files currently available on the MySQL server.

2fill in blank

medium

Complete the code to enable binary logging in MySQL configuration.

MySQL

[mysqld]
[1]=ON

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

Alog_bin

Bbinlog_format

Cserver_id

Dskip_log_bin

Attempts:

3 left

💡 Hint

Common Mistakes

Using binlog_format which sets format but does not enable logging.

Using skip_log_bin which disables binary logging.

Setting log_bin=ON in the MySQL configuration file enables binary logging.

3fill in blank

hard

Fix the error in the command to purge binary logs older than a specific date.

MySQL

PURGE BINARY LOGS TO '[1]';

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

Abinlog.000010

Bmysql-bin.000010

Cmysql_bin_000010

Dmysql-bin-000010

Attempts:

3 left

💡 Hint

Common Mistakes

Using dashes or underscores instead of dots in the file name.

Using incomplete or incorrect file name formats.

The correct syntax requires the exact binary log file name with dot separators, like mysql-bin.000010.

4fill in blank

hard

Fill both blanks to set the binary log format and server ID in MySQL configuration.

MySQL

[mysqld]
binlog_format=[1]
server_id=[2]

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

AROW

BSTATEMENT

C12345

DON

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'ON' for binlog_format instead of a valid format.

Using non-numeric values for server_id.

binlog_format can be set to 'ROW' for row-based logging, and server_id must be a unique number like 12345.

5fill in blank

hard

Fill all three blanks to create a command that shows the status of binary logging and the current binary log file.

MySQL

SHOW [1] LIKE '[2]';
SHOW MASTER [3];

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

ASTATUS

Blog_bin

DLOGS

Attempts:

3 left

💡 Hint

Common Mistakes

Using SHOW LOGS which is not a valid MySQL command.

Using incorrect filter strings in LIKE clause.

The first command SHOW STATUS LIKE 'log_bin'; checks if binary logging is enabled. The second command SHOW MASTER STATUS; shows the current binary log file and position.

Practice

1. What is the primary purpose of the MySQL binary log?

easy

A. To store user login credentials securely

B. To record all changes to the database for recovery and replication

C. To manage database schema versions

D. To optimize query execution plans

Solution

1. Step 1: Understand what binary logs do

   Binary logs keep a record of all changes made to the database, such as inserts, updates, and deletes.

2. Step 2: Identify the main uses of binary logs

   These logs are used for data recovery after crashes and for replicating changes to other servers.

3. Final Answer:

   To record all changes to the database for recovery and replication -> Option B

4. Quick Check:

   Binary logs = record changes for recovery and replication [OK]

Hint: Binary logs track changes for recovery and replication [OK]

Common Mistakes:

• Confusing binary logs with user authentication logs
• Thinking binary logs store query execution plans
• Assuming binary logs manage schema versions

2. Which of the following commands correctly shows the list of binary log files in MySQL?

easy

A. SHOW BINARY LOG FILES;

B. SHOW BINLOG FILES;

C. SHOW LOGS BINARY;

D. SHOW BINARY LOGS;

Solution

1. Step 1: Recall the correct syntax to list binary logs

   The correct command to list binary log files is SHOW BINARY LOGS;.

2. Step 2: Verify other options for syntax correctness

   Options B, C, and D are not valid MySQL commands and will cause syntax errors.

3. Final Answer:

   SHOW BINARY LOGS; -> Option D

4. Quick Check:

   List binary logs = SHOW BINARY LOGS; [OK]

Hint: Use SHOW BINARY LOGS; to list binary log files [OK]

Common Mistakes:

• Using incorrect command syntax like SHOW BINLOG FILES;
• Mixing up command word order
• Adding extra words causing syntax errors

3. Given the following MySQL commands executed in order:
SHOW BINARY LOGS;
Output:
File: mysql-bin.000001
File: mysql-bin.000002
File: mysql-bin.000003

Then running:
PURGE BINARY LOGS TO 'mysql-bin.000003';
What binary log files remain after the purge?

medium

A. mysql-bin.000001, mysql-bin.000002

B. Only mysql-bin.000003

C. mysql-bin.000003 and later

D. All binary logs are deleted

Solution

1. Step 1: Understand PURGE BINARY LOGS TO command

   This command deletes all binary logs before the specified file, but not the file itself.

2. Step 2: Apply to given files

   Logs mysql-bin.000001 and mysql-bin.000002 are deleted; mysql-bin.000003 and any later files remain.

3. Final Answer:

   mysql-bin.000003 and later -> Option C

4. Quick Check:

   PURGE BINARY LOGS TO 'file' keeps that file and after [OK]

Hint: PURGE BINARY LOGS TO keeps the named file and after [OK]

Common Mistakes:

• Assuming the named file is deleted too
• Thinking all logs are deleted
• Confusing PURGE BINARY LOGS TO with PURGE BINARY LOGS BEFORE

4. You run the command PURGE BINARY LOGS BEFORE 'mysql-bin.000005'; but get an error: "ERROR 1236 (HY000): Could not purge binary logs". What is the most likely cause?

medium

A. The specified log file does not exist or is not in the binary log index

B. You need to restart the MySQL server before purging

C. The binary log feature is disabled in MySQL

D. You must use PURGE BINARY LOGS TO instead of BEFORE

Solution

1. Step 1: Understand error cause for PURGE BINARY LOGS BEFORE

   This error usually happens if the specified log file is missing or not recognized in the binary log index.

2. Step 2: Check other options

   Restarting server is not required; binary log must be enabled to run command; PURGE BINARY LOGS BEFORE is valid syntax.

3. Final Answer:

   The specified log file does not exist or is not in the binary log index -> Option A

4. Quick Check:

   Missing log file = purge error [OK]

Hint: Check log file exists before purging [OK]

Common Mistakes:

• Restarting server unnecessarily
• Confusing PURGE BINARY LOGS BEFORE with TO
• Ignoring binary log enablement status

5. You want to free disk space by deleting old binary logs but keep all logs generated after a specific date '2024-01-01 00:00:00'. Which approach correctly achieves this?

hard

A. Find the binary log file generated just after the date, then run PURGE BINARY LOGS TO 'that_file';

B. Run PURGE BINARY LOGS BEFORE 'mysql-bin.000001'; to delete all logs before the first file

C. Disable binary logging temporarily, delete all files manually, then re-enable logging

D. Use SHOW BINARY LOGS; and delete files older than the date manually from disk

Solution

1. Step 1: Identify the binary log file after the target date

   Use the binary log index or SHOW BINARY LOGS; to find the first file generated after '2024-01-01 00:00:00'.

2. Step 2: Use PURGE BINARY LOGS TO command

   Run PURGE BINARY LOGS TO 'that_file'; to safely delete all logs before that file, keeping newer logs.

3. Step 3: Avoid unsafe manual deletion

   Manual deletion or disabling logging risks data loss or replication errors.

4. Final Answer:

   Find the binary log file generated just after the date, then run PURGE BINARY LOGS TO 'that_file'; -> Option A

5. Quick Check:

   Use PURGE BINARY LOGS TO after date file [OK]

Hint: Purge logs safely by targeting file after date [OK]

Common Mistakes:

• Deleting files manually risking corruption
• Disabling binary logging to delete files
• Purging logs before the first file (no effect)