Understanding and Managing MySQL Binary Logs: A Comprehensive Guide

When working with MySQL, especially in production environments, understanding binary logs (binlogs) is essential. Binary logs record all changes to the database, such as INSERT, UPDATE, and DELETE statements, and are primarily used for replication and point-in-time recovery. This guide will walk you through everything you need to know about binary logs: how to check them, read them, manage them, and avoid common pitfalls.

What Are Binary Logs in MySQL?

Binary logs are transaction logs that contain a record of database operations. Unlike the general log or slow query log, the binary log only records events that modify the database.

They are used for:

• Replication: To send changes from a master to a replica.
• Backup and recovery: To perform point-in-time recovery (PITR) after restoring from a backup.

How to Check if Binary Logging is Enabled

To verify if binary logging is active on your MySQL server, run:

SHOW VARIABLES LIKE 'log_bin';

A value of ON confirms that binary logging is enabled.

You can also inspect the path and filename prefix of the logs:

SHOW VARIABLES LIKE 'log_bin_basename';

For logging format (e.g., ROW, STATEMENT, or MIXED):

SHOW VARIABLES LIKE 'binlog_format';

Listing Binary Log Files

To list all existing binary log files:

SHOW BINARY LOGS;

This shows the log file names and their sizes.

To see the current active binary log file:

SHOW MASTER STATUS;

Reading Binary Log Contents

You cannot read binary logs directly in SQL. Instead, use the mysqlbinlog command-line tool.

Example:

mysqlbinlog /var/lib/mysql/binlog.000001

This will dump the contents to standard output.

Filtering by date range:

mysqlbinlog --start-datetime="2025-06-25 00:00:00" \
            --stop-datetime="2025-06-25 23:59:59" \
            /var/lib/mysql/binlog.000001

Filtering by position:

mysqlbinlog --start-position=107 --stop-position=512 binlog.000001

To apply logs to a MySQL server:

mysqlbinlog binlog.000001 | mysql -u root -p

Common Binary Log File Path

• Default location on Linux: /var/lib/mysql/
• Filename usually looks like: binlog.000001, binlog.000002, etc.

Check your MySQL config file (/etc/my.cnf or /etc/mysql/my.cnf) for:

[mysqld]
log-bin=mysql-bin

Managing Binary Logs

Binary logs can consume a lot of disk space if not managed properly. Here are some key practices:

1. Set expiration policy

SET GLOBAL binlog_expire_logs_seconds = 604800; -- 7 days

Or via configuration:

expire_logs_days = 7

2. Manually purge binary logs

PURGE BINARY LOGS TO 'binlog.000010';

Or by date:

PURGE BINARY LOGS BEFORE '2025-06-01 00:00:00';

3. Avoid deleting logs used in replication

Always check slave status first:

SHOW SLAVE STATUS\G

Look at the Relay_Master_Log_File before purging.

Other Considerations

• Permissions: You need the REPLICATION CLIENT privilege to run SHOW BINARY LOGS.
• Log Rotation: MySQL creates a new log file automatically when the current file exceeds max_binlog_size.
• Binary log encryption is available from MySQL 5.7.11+ if enabled.
• Row-based logging is recommended for more accurate replication (especially with non-deterministic functions).

Use Cases for Binary Logs

1. Disaster Recovery: After restoring a full backup, replay binary logs to recover the latest state.
2. Replication Troubleshooting: Inspect binlog to understand what changes were sent to replicas.
3. Audit Trail: With some scripting, you can parse binlogs to detect what changes were made by whom (if you include user info in queries).

Finally

Managing binary logs effectively ensures better replication stability, faster recovery, and avoids disk space issues. By regularly inspecting and pruning your logs, you maintain a healthy MySQL instance. Remember to monitor your binlog size and frequency of changes, and always test your PITR process periodically.

If you're not using replication or PITR, you might consider disabling binary logs in non-critical environments to reduce I/O and disk usage. But for most production systems, they are indispensable.

Support Us