How to Configure MySQL Binary Logging

How to Configure MySQL Binary Logging

MySQL binary logging records all data-modifying statements and events in a binary format. Binary logs are essential for point-in-time recovery, replication, and auditing database changes on your Breeze instances. Understanding how to configure and manage binary logs is a fundamental database administration skill.

Enabling Binary Logging

Binary logging is enabled by default in MySQL 8.0+. Verify and configure it in /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
server_id = 1
log_bin = /var/log/mysql/binlog
binlog_format = ROW
binlog_row_image = FULL
max_binlog_size = 100M
binlog_expire_logs_seconds = 604800  # 7 days
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON

Restart MySQL to apply changes:

sudo systemctl restart mysql

Binary Log Formats

• ROW — logs the actual row changes. Most precise and reliable for replication. Recommended for production.
• STATEMENT — logs the SQL statements that modify data. Smaller log files but can cause replication issues with non-deterministic functions.
• MIXED — uses statement-based logging by default, switches to row-based for statements that cannot be safely replicated as statements.

The ROW format is recommended for all production Breeze instances because it ensures exact replication of data changes regardless of the SQL used.

Viewing Binary Log Contents

List available binary log files:

mysql -u root -p -e "SHOW BINARY LOGS;"

Decode binary log events using mysqlbinlog:

# View all events in a binary log
mysqlbinlog /var/log/mysql/binlog.000001

# View events in a time range
mysqlbinlog --start-datetime="2026-03-01 00:00:00" \
            --stop-datetime="2026-03-01 12:00:00" \
            /var/log/mysql/binlog.000001

# View only specific database events
mysqlbinlog --database=myapp /var/log/mysql/binlog.000001

# Decode ROW format events to readable SQL
mysqlbinlog --verbose /var/log/mysql/binlog.000001

Point-in-Time Recovery

Point-in-time recovery lets you restore your database to any moment using a full backup plus binary logs:

# Step 1: Restore the full backup
mysql -u root -p myapp < /backup/myapp_full.sql

# Step 2: Identify the position or timestamp to recover to
mysqlbinlog --verbose /var/log/mysql/binlog.000005 | less

# Step 3: Apply binary logs up to the desired point
mysqlbinlog --stop-datetime="2026-03-01 14:30:00" \
  /var/log/mysql/binlog.000003 \
  /var/log/mysql/binlog.000004 \
  /var/log/mysql/binlog.000005 | mysql -u root -p

To skip a problematic transaction (such as an accidental DROP TABLE), use GTID-based filtering:

mysqlbinlog --exclude-gtids="server-uuid:42" \
  /var/log/mysql/binlog.000005 | mysql -u root -p

Managing Binary Log Disk Usage

Binary logs can consume significant disk space on your Breeze instance. Manage them proactively:

# Check current binary log disk usage
mysql -u root -p -e "SHOW BINARY LOGS;"

# Purge logs older than a specific date
PURGE BINARY LOGS BEFORE '2026-02-01 00:00:00';

# Purge all logs before a specific file
PURGE BINARY LOGS TO 'binlog.000010';

# Set automatic expiration (in seconds)
SET GLOBAL binlog_expire_logs_seconds = 604800;

Binary Log Monitoring

Monitor binary log activity with these status variables:

SHOW GLOBAL STATUS LIKE 'Binlog_%';
SHOW GLOBAL STATUS LIKE 'Com_commit';
SHOW GLOBAL STATUS LIKE 'Com_rollback';
SHOW MASTER STATUS;

If Binlog_cache_disk_use is high relative to Binlog_cache_use, increase binlog_cache_size to keep transaction data in memory. Properly configured binary logging provides a robust safety net for your data on every Breeze instance.