MySQL Binary Log Management: Sizing, Retention, and Troubleshooting

Q: Can I disable binary logging to save disk space and CPU?

Yes: SET GLOBAL sql_log_bin = OFF disables binlogging for the current session; removing log_bin from my.cnf and restarting disables it server-wide. Without binary logs you lose replication capability and PITR. Only appropriate for standalone development instances.

The MySQL binary log (binlog) is a sequential record of every data-modifying event on the server. It drives replication (replicas read and replay these events) and enables point-in-time recovery (PITR). Unmanaged binary logs fill disks; incorrectly purged logs break replicas or destroy recovery capability.

Impact

Binary logs grow continuously at a rate proportional to write throughput. On a busy server without a retention policy, they fill the data directory within days, causing write failures (error 1114) and bringing the server down. Purging logs that a replica has not yet read breaks replication and requires a full resync of the replica — potentially hours of downtime.

Common Causes of Binary Log Problems

No purge policy set (expire_logs_days = 0 in older versions, binlog_expire_logs_seconds = 0) — logs accumulate forever
expire_logs_days / binlog_expire_logs_seconds set too short, removing logs before a lagging replica has read them
Manual PURGE BINARY LOGS deleting logs that a connected replica's I/O thread has not yet consumed
Binary log format set to ROW on a write-heavy workload generating very large row image events (e.g., UPDATE on a wide table)
sync_binlog=1 causing high I/O latency on SATA/HDD storage that amplifies write latency
Disk partition containing binlog_basename path is different from datadir and has less space
GTID gaps left by manual operations that cause replicas to fail with "Got fatal error 1236"
Binary logs not enabled at all, making PITR and replication impossible

Checking Binary Log Configuration

SHOW VARIABLES LIKE 'log_bin%';
SHOW VARIABLES LIKE 'binlog%';
SHOW VARIABLES LIKE 'expire_logs_days';
SHOW VARIABLES LIKE 'sync_binlog';
SHOW MASTER STATUS;         -- MySQL 5.7
SHOW BINARY LOG STATUS;     -- MySQL 8.4+
SHOW BINARY LOGS;           -- List all current log files and sizes

Configuring a Retention Policy

Set automatic expiry in my.cnf (recommended):

[mysqld]
# MySQL 8.0+ — seconds (604800 = 7 days)
binlog_expire_logs_seconds = 604800

# MySQL 5.7 — days (integer only)
expire_logs_days = 7

Or set at runtime (MySQL 8.0+):

SET GLOBAL binlog_expire_logs_seconds = 604800;
SET PERSIST binlog_expire_logs_seconds = 604800;

Expiry fires when a new binary log file is created (on FLUSH BINARY LOGS or when max_binlog_size is reached). If no new log is created, old logs are not purged automatically.

Choose a retention window that is longer than your maximum acceptable replica lag plus a safety margin. If a replica can lag up to 1 hour, set retention to at least 24 hours. If you want 7-day PITR capability, set retention to 7 days.

Purging Binary Logs Safely

Check what each replica has consumed before purging:

-- On the primary: list connected replicas and their positions
SHOW REPLICA HOSTS;         -- MySQL 8.0+
SHOW SLAVE HOSTS;           -- MySQL 5.7

-- On each replica: which log file is the I/O thread reading?
SHOW REPLICA STATUS\G
-- Look at: Master_Log_File, Read_Master_Log_Pos

Purge logs only up to the oldest file any replica has consumed:

-- Purge all logs created before a specific date
PURGE BINARY LOGS BEFORE '2025-05-01 00:00:00';

-- Purge all logs before a specific file (does not delete this file)
PURGE BINARY LOGS TO 'mysql-bin.000150';

Never purge a log file that appears in any replica's Master_Log_File field.

Use PURGE BINARY LOGS inside a maintenance script that first checks replica positions:

#!/bin/bash
# Get the oldest log file any replica is reading
OLDEST=$(mysql -N -e "
  SELECT MIN(Master_Log_File)
  FROM (
    SELECT SUBSTRING_INDEX(Master_Log_File, '.', -1) AS num, Master_Log_File
    FROM information_schema.processlist p
    JOIN information_schema.replica_master_info rmi ON 1=1
    LIMIT 1
  ) t
" 2>/dev/null || echo "")
# Safer: manual verification recommended before automation

Tuning Binary Log Format

Choose the right binary log format:
```
SHOW VARIABLES LIKE 'binlog_format';
```
- ROW (default and recommended): logs before/after images of every changed row. Safe and deterministic but verbose for wide-row updates. Required for full GTID and Group Replication support.
- STATEMENT: logs the SQL statement. Compact but non-deterministic (functions like NOW(), UUID() may produce different results on replicas).
- MIXED: uses STATEMENT for simple queries and falls back to ROW for non-deterministic ones. The best of both in theory; in practice, ROW is recommended for correctness.

Reduce ROW format verbosity with binlog_row_image:

SHOW VARIABLES LIKE 'binlog_row_image';
-- full (default): before + after image of all columns
-- minimal: only changed columns in after image + primary key in before image
-- noblob: like full but skips unchanged BLOB/TEXT columns

SET GLOBAL binlog_row_image = 'minimal';
SET PERSIST binlog_row_image = 'minimal';

minimal can reduce binlog size by 40–70% on wide tables where only a few columns change per UPDATE, with no impact on replication correctness.

Control per-transaction binlog cache size:

SHOW VARIABLES LIKE 'binlog_cache_size';
SHOW GLOBAL STATUS LIKE 'Binlog_cache_disk_use';
SHOW GLOBAL STATUS LIKE 'Binlog_cache_use';

If Binlog_cache_disk_use / Binlog_cache_use is high, transactions are exceeding binlog_cache_size and spilling to temp files. Raise the cache:

SET GLOBAL binlog_cache_size = 1048576;   -- 1 MB
SET PERSIST binlog_cache_size = 1048576;

Using Binary Logs for Point-in-Time Recovery

Restore from backup and replay binary logs to a specific point in time:

# 1. Restore the last full backup
mysql < backup.sql

# 2. Replay binary logs from backup time up to the point before the error
mysqlbinlog \
  --start-datetime="2025-05-10 02:00:00" \
  --stop-datetime="2025-05-10 14:30:00" \
  /var/log/mysql/mysql-bin.000210 \
  /var/log/mysql/mysql-bin.000211 \
| mysql -u root -p

# Use --stop-position instead of --stop-datetime for precision:
mysqlbinlog --stop-position=12345678 mysql-bin.000211 | mysql -u root -p

With GTID (gtid_mode=ON), identify and exclude a bad transaction:

# Find the GTID of the bad transaction
mysqlbinlog --verbose mysql-bin.000211 | grep -A5 "DROP TABLE orders"

# Skip that specific GTID during replay using --exclude-gtids
mysqlbinlog --exclude-gtids='3E11FA47-71CA-11E1-9E33-C80AA9429562:23' \
  mysql-bin.000211 | mysql -u root -p

Monitoring Binary Log Disk Usage

-- Current binary log files and sizes
SELECT
    LOG_NAME,
    FILE_SIZE / 1024 / 1024 AS size_mb
FROM performance_schema.binary_log_transaction_compression_stats
LIMIT 1;  -- available in MySQL 8.0.20+

-- Simpler: SHOW BINARY LOGS
SHOW BINARY LOGS;

-- Total binlog disk usage
SELECT
    SUM(FILE_SIZE) / 1024 / 1024 AS total_mb
FROM information_schema.FILES
WHERE FILE_TYPE = 'UNDO LOG';  -- not accurate for binlogs; use shell:
-- du -sh /var/log/mysql/mysql-bin.*

Additional Information

sync_binlog=1 (fsync on every transaction commit) is the safest setting for durability but adds ~1 ms latency per commit on spinning disk. On SSD or NVMe storage the overhead is negligible. sync_binlog=0 (OS buffers writes) risks losing up to the last OS buffer flush on crash.
When GTID mode is enabled, PURGE BINARY LOGS is GTID-aware: it updates mysql.gtid_executed so that purged GTIDs are not mistakenly re-applied.
max_binlog_size (default 1 GB) controls when MySQL rotates to a new log file. A large transaction can exceed this — InnoDB does not split transactions across files.
Enabling binary logging on a read replica (with log_slave_updates=ON in 5.7, log_replica_updates=ON in 8.0) creates a relay of the binlog that is useful for replica chains and for making the replica itself a PITR source.

Frequently Asked Questions

Q: I purged binary logs and now my replica is broken. How do I fix it? A: The replica I/O thread will fail with error 1236: "Requested position not found in the binary log." You must re-initialize the replica from a fresh snapshot of the primary. With GTID: restore snapshot, then CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION=1. Without GTID: restore snapshot noting the binlog file/position from mysqldump --master-data, then CHANGE REPLICATION SOURCE TO.

Q: How do I know what binlog_expire_logs_seconds to set? A: It must be longer than the maximum expected replica lag plus your recovery time objective. If your longest acceptable lag is 1 hour and you want 7-day PITR: use at least 7 days (604800 seconds). Disk space determines the practical upper bound.

Q: Binary logs are not being purged even though the expiry is set. Why? A: Automatic purge fires only when a new log file is created (on binlog rotation). If writes are infrequent and max_binlog_size is not reached, run FLUSH BINARY LOGS manually to trigger a rotation and purge.

Q: Can I disable binary logging to save disk space and CPU? A: Yes: SET GLOBAL sql_log_bin = OFF disables binlogging for the current session; removing log_bin from my.cnf and restarting disables it server-wide. Without binary logs you lose replication capability and PITR. Only appropriate for standalone development instances.

Q: Is binlog_row_image = minimal safe? A: For replication, yes — replicas apply the minimal image correctly. For PITR with mysqlbinlog, you lose the ability to see unchanged column values in the before-image. For Debezium / CDC tools that need full row snapshots, use full or noblob.