Binary log management in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Check Current Binary Log File

What is the output of the following query that shows the current binary log file in MySQL?

MySQL

SHOW MASTER STATUS;

A A table with columns File, Position, Binlog_Do_DB, Binlog_Ignore_DB showing the current binary log file name and position

B An error saying 'Unknown command SHOW MASTER STATUS;'

C A list of all binary log files on the server

D Empty result set with no rows

💡 Hint

SHOW MASTER STATUS shows the current binary log file and position used for replication.

SHOW MASTER STATUS returns a single row with the current binary log file name and position, which is essential for replication and backup.

🧠 Conceptual

intermediate

Purpose of Binary Logs

Which of the following best describes the main purpose of binary logs in MySQL?

A To log all SELECT queries executed on the server

B To store all changes to the database for replication and recovery

C To keep a backup of the entire database every hour

D To store user login credentials securely

💡 Hint

Think about what binary logs record and how they are used in replication.

Binary logs record all changes (like INSERT, UPDATE, DELETE) to the database, which helps in replication and point-in-time recovery.

📝 Syntax

advanced

Enable Binary Logging in MySQL

Which of the following configuration lines correctly enables binary logging in the MySQL server configuration file (my.cnf)?

A

[mysqld]
binlog_format = ROW
log_bin = mysql-bin

B

[client]
log_bin = mysql-bin

C

[mysqld]
log_bin = off

D

[mysqld]
binlog_format = OFF

💡 Hint

Binary logging must be enabled under the [mysqld] section with log_bin set to a filename prefix.

To enable binary logging, set log_bin to a filename prefix under [mysqld]. binlog_format controls the format (ROW, STATEMENT, MIXED).

❓ optimization

advanced

Reducing Binary Log Size

Which method is the best way to reduce the size of binary logs without losing replication data?

A Delete binary log files manually while the server is running

B Disable binary logging completely

C Set expire_logs_days to a lower number to automatically purge old logs

D Set binlog_format to STATEMENT and run only SELECT queries

💡 Hint

Think about automatic cleanup of old logs versus manual or disabling logging.

Setting expire_logs_days causes MySQL to automatically remove old binary logs after the specified days, reducing disk usage safely.

🔧 Debug

expert

Binary Log Corruption Detection

You suspect a binary log file is corrupted. Which command will help you verify the integrity of binary log files?

A mysqlbinlog --start-position=1 mysql-bin.000001

B mysqlbinlog --check mysql-bin.000001

C mysqlbinlog --read-from-remote-server mysql-bin.000001

D mysqlbinlog --verify mysql-bin.000001

💡 Hint

Look for a mysqlbinlog option that checks or verifies the log file.

The --verify option of mysqlbinlog checks the binary log file for corruption and reports errors if found.

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)