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.