Binary log management in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the binary log in MySQL?

The binary log records all changes to the database, such as INSERT, UPDATE, and DELETE statements. It helps with replication and data recovery.

beginner

How do you enable binary logging in MySQL?

You enable binary logging by adding log_bin=ON to the MySQL configuration file (my.cnf) and restarting the server.

beginner

What command lists all binary log files on the MySQL server?

Use SHOW BINARY LOGS; to see all binary log files and their sizes.

intermediate

How can you safely purge old binary logs?

Use PURGE BINARY LOGS TO 'log_name'; or PURGE BINARY LOGS BEFORE 'date'; to delete old logs and free space.

beginner

Why is managing binary logs important in MySQL?

Because binary logs can grow large and consume disk space, managing them prevents storage issues and ensures replication works smoothly.

Which MySQL configuration option enables binary logging?

A enable_binary

B log_bin

C bin_log_enable

D binary_log_mode

The correct option to enable binary logging is log_bin.

What does the command SHOW BINARY LOGS; do?

A Disables binary logging

B Deletes all binary logs

C Displays the current binary log file only

D Shows all binary log files and their sizes

It lists all binary log files on the server along with their sizes.

Which command is used to remove binary logs before a specific date?

A PURGE BINARY LOGS BEFORE 'date';

B DELETE BINARY LOGS BEFORE 'date';

C REMOVE BINARY LOGS TO 'date';

D DROP BINARY LOGS BEFORE 'date';

The correct syntax to purge old logs before a date is PURGE BINARY LOGS BEFORE 'date';.

Why should you manage binary logs regularly?

A To disable replication

B To speed up query execution

C To prevent disk space from filling up

D To backup the database

Binary logs can grow large and fill disk space, so managing them prevents storage problems.

Which of these is NOT a use of the binary log?

A Query optimization

B Point-in-time recovery

C Data replication

D Auditing data changes

Binary logs are not used for query optimization; they record data changes for replication and recovery.

Explain what the MySQL binary log is and why it is important.

Think about how MySQL keeps track of changes and why that matters.

Describe how to safely remove old binary logs in MySQL.

Consider commands that delete logs and why timing matters.