Understanding MySQL Binary Logs

The binary log (often called binlog) is a set of files that stores details about data modifications and potentially data-changing events. It captures operations like INSERT, UPDATE, and DELETE, along with timestamps and execution details. Queries that do not alter data, such as SELECT or SHOW, are omitted. These logs are essential for point-in-time data recovery, replication to replicas, and auditing database activity.

Binary Log Formats: Row, Statement, and Mixed

MySQL offers three distinct formats for recording events in the binary log. The choice affects replication behavior and log size.

Row-Based Logging

In this mode, the log records how individual table rows are affected. The replica applies changes directly to the corresponding rows.

• Pros: It provides high precision, logging exactly what changed. It avoids replication issues caused by non-deterministic functions or triggers.
• Cons: It can generate massive log files. For example, a single UPDATE affecting thousands of rows will log each row change individually.

Statement-Based Logging

This format logs the actual SQL statements executed on the source server.

• Pros: It is space-efficient because it logs the command, not the row data. This reduces disk I/O and storage usage.
• Cons: It requires context information to ensure statements replicate correctly. Functions like NOW() or UUID() can cause data drift between source and replicas if not handled carefully.

Mixed Logging

Mixed mode is a hybrid approach. By default, it uses Statement-based logging but switches to Row-based logging automatically when a statement is non-deterministic or unsafe for replication.

Configuring the Log Format

You can define the format in the configuration file or change it dynamically during runtime.

# Configuration file example
log-bin=db-logs
binlog_format=MIXED

Dynamic changes:

-- Set for the current session
SET SESSION binlog_format = 'ROW';

-- Set globally for all new connections
SET GLOBAL binlog_format = 'STATEMENT';

Cleaning Up Binary Log Files

Binary logs can consume significant disk space. You can manage them through automatic expiration, manual purging, or complete resets.

Automatic Expiration

Instead of manually deleting files, configure MySQL to remove logs older than a specific threshold.

For MySQL versions prior to 8.0:

-- Set logs to expire after 7 days
SET GLOBAL expire_logs_days = 7;

For MySQL 8.0 and latter, expire_logs_days is deprecated in favor of seconds:

-- Set logs to expire after 3 days (259200 seconds)
SET GLOBAL binlog_expire_logs_seconds = 259200;

Manual Purging

You can manually delete logs up to a specific file or date.

-- Delete logs older than 5 days
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 5 DAY);

-- Delete logs up to a specific file name
PURGE BINARY LOGS TO 'db-logs.000012';

Warning: Use RESET MASTER with extreme caution as it deletes all binary logs and resets the index. This is generally not recomended on active replication setups.

Analyzing Log Contents

There are two primary ways to inspect binary logs: using SQL commands or the command-line utility.

Using SQL Events

Quickly view events within a specific log file:

SHOW BINLOG EVENTS IN 'db-logs.000025';

Using the mysqlbinlog Utility

For detailed analysis, use the external tool. This is especially useful for Row-based logs which are encoded.

# Decode rows and output to a readable file
mysqlbinlog --base64-output=DECODE-ROWS -vv db-logs.000316 > /tmp/analysis.txt

# Filter by specific byte positions
mysqlbinlog --start-position=475 --stop-position=95076397 db-logs.000315 | tail -50

Enhancing Readability for Row-Based Logs

Row-based logs can be cryptic. Enable binlog_rows_query_log_events to inject the original SQL as a comment into the log, making analysis easier.

SET GLOBAL binlog_rows_query_log_events = 1;

To reduce log size in Row mode, adjust binlog_row_image:

• FULL: Logs all columns (default).
• MINIMAL: Logs only changed columns and the primary key.
• NOBLOB: Logs all columns except unchanged BLOB/TEXT.

Data Recovery Using Binary Logs

To recover data, pipe the output of mysqlbinlog directly into a MySQL client. Avoid copying and pasting SQL manually, as context matters.

mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -u root -p

If you encounter character set errors with mysqlbinlog, use the --no-defaults flag:

mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -vv db-logs.000546

Managing Relay Logs

Relay logs are used by replicas to store events read from the source's binary log before they are applied. Usually, these are purged automatically once the SQL thread has executed them.

If you need to force purging or ensure the feature is enabled:

SET GLOBAL relay_log_purge = 1;
FLUSH LOGS;