Mastering MySQL Binary Logs: Prevent Disk Bloat and Optimize Replication

MySQL operations engineers often encounter disk‑space alerts where the data directory appears small but the underlying storage is exhausted; binary logs (binlogs) are usually the culprit. This article, based on MySQL 8.0.36 on Ubuntu 24.04 LTS, systematically covers binlog architecture, disk‑space impact, troubleshooting methods, and best‑practice configurations.

1. Binary Log Basics

1.1 Purpose of Binary Logs

Binary logs record every data‑modifying operation, including INSERT, UPDATE, DELETE, DDL statements (CREATE, ALTER, DROP), and server events such as replication heartbeats. Their main uses are:

Primary‑to‑replica replication – replicas read the master’s binlog to stay in sync.

Point‑in‑time recovery – the mysqlbinlog tool can replay logs to a specific moment.

Incremental backups – combine full backups with binlogs for efficient restores.

Auditing – track historical changes to the database.

1.2 File Structure

Binlog files follow the pattern mysql-bin.000001, mysql-bin.000002, etc. Each file contains multiple event types such as Format_description, Table_map, row events ( Write_rows, Update_rows, Delete_rows), and Xid for transaction commits.

1.3 Log Formats

MySQL 8.0 supports three formats:

ROW – records full row changes; safest but largest.

STATEMENT – records the original SQL; smaller but can produce nondeterministic results.

MIXED – defaults to STATEMENT and switches to ROW when needed.

SHOW VARIABLES LIKE 'binlog_format';
SET GLOBAL binlog_format = 'ROW';
-- To make it permanent, add "binlog_format = ROW" to my.cnf

2. Disk‑Space Issues

2.1 Problem Scenario

A production MySQL server shows a 500 GB usage on /var/lib/mysql while actual data is only 80 GB. Investigation reveals >400 GB of binary logs, a classic case of missing cleanup policies.

2.2 Growth Factors

High write workload – more transactions generate more logs.

ROW format – produces larger logs than STATEMENT.

Large tables – even small updates generate many row events.

SELECT SUM(FILE_SIZE)/1024/1024/1024 AS total_size_gb FROM information_schema.FILES WHERE FILE_TYPE='BINARY LOG';
SELECT LOG_NAME, FILE_SIZE/1024/1024 AS size_mb FROM information_schema.FILES WHERE FILE_TYPE='BINARY LOG' ORDER BY LOG_NAME;

2.3 Cleanup Mechanisms

MySQL offers two ways to purge binlogs:

Automatic: set expire_logs_days (default 0 = never).

Manual: run PURGE BINARY LOGS with a date or filename.

SHOW VARIABLES LIKE 'expire_logs_days';
SET GLOBAL expire_logs_days = 7;  -- keep 7 days of logs
-- Permanent: add "expire_logs_days = 7" to my.cnf

2.4 Manual Purge Example

PURGE BINARY LOGS BEFORE '2026-01-01 00:00:00';
PURGE BINARY LOGS TO 'mysql-bin.000100';
SHOW MASTER STATUS;

Important: never delete logs that replicas have not yet processed; otherwise replication breaks.

3. Troubleshooting Steps

3.1 Initial Diagnosis

# Disk usage
df -h
# MySQL data directory size
du -sh /var/lib/mysql/*
# Log directory size
du -sh /var/log/mysql/*

3.2 Binlog Usage Analysis

SELECT LOG_NAME, FILE_SIZE/1024/1024 AS size_mb FROM information_schema.FILES WHERE FILE_TYPE='BINARY LOG' ORDER BY LOG_NAME;
SELECT COUNT(*) AS total_files, SUM(FILE_SIZE)/1024/1024/1024 AS total_gb FROM information_schema.FILES WHERE FILE_TYPE='BINARY LOG';

3.3 Identify Hidden Space Consumers

If df shows high usage but du does not, check for open file handles, lingering temporary files, or oversized system logs.

# Open files held by mysqld
sudo lsof -p $(pgrep mysqld) | grep -E "REG|DIR" | awk '{print $NF}' | sort | uniq | xargs -I {} ls -lh {} 2>/dev/null
# Temporary directory
SHOW VARIABLES LIKE 'tmpdir';
SHOW STATUS LIKE 'Created_tmp%';

4. Replication‑Specific Considerations

4.1 Primary‑Replica Architecture

In a replication setup, the primary must keep binlogs enabled, while each replica reads them via an I/O thread into a relay log and replays them with an SQL thread.

SHOW SLAVE HOSTS;  -- view replica topology
SHOW SLAVE STATUS\G;  -- check replication progress

4.2 Should Replicas Enable Binlogs?

If a replica also acts as a primary for downstream replicas, enable binlogs; otherwise they can be disabled to save space.

SHOW VARIABLES LIKE 'log_slave_updates';
-- ON means the replica writes its own binlog after applying relay events.

4.3 Cleanup in Replication

Only purge logs that all replicas have already processed. Use SHOW SLAVE STATUS to compare Relay_Master_Log_File across replicas, then purge up to the oldest common file.

# Example using mysqlrpladmin (Percona Toolkit)
mysqlrpladmin --master=root:pass@master-host --discover-slaves-for=master prune
# Manual method
-- Record each replica's Relay_Master_Log_File
-- Identify the earliest file among them
PURGE BINARY LOGS TO 'mysql-bin.000050';

4.4 GTID Mode

GTID simplifies replication management. In GTID mode, MySQL automatically skips logs containing unexecuted transactions during purge.

SHOW VARIABLES LIKE 'gtid_mode';
SHOW VARIABLES LIKE 'enforce_gtid_consistency';
SELECT @@GLOBAL.gtid_executed, @@GLOBAL.gtid_purged;
PURGE BINARY LOGS TO 'mysql-bin.000100';  -- safe if GTID is consistent

5. Binlog Configuration Optimisation

5.1 Core Parameters (my.cnf)

[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_cache_size = 4M
max_binlog_cache_size = 512M
max_binlog_size = 1G
expire_logs_days = 7
sync_binlog = 1   # safest; set higher for performance
binlog_row_image = FULL  # or MINIMAL to reduce size

5.2 Performance vs. Safety

sync_binlog

controls when the binlog is flushed to disk: 1 – flush after every transaction (most durable, highest I/O cost). 0 – rely on OS flush (best performance, risk of losing multiple transactions on crash). N – flush every N transactions (trade‑off).

SHOW VARIABLES LIKE 'sync_binlog';
SET GLOBAL sync_binlog = 100;  -- flush every 100 transactions

5.3 Binlog Cache Tuning

Increase binlog_cache_size if Binlog_cache_disk_use is high, indicating the in‑memory cache is insufficient.

SHOW STATUS LIKE 'Binlog_cache%';
SET GLOBAL binlog_cache_size = 8*1024*1024;  -- 8 MB

5.4 Row‑Format Optimisation

Adjust binlog_row_image to control column logging: FULL – all columns. MINIMAL – only primary key and changed columns. NOBLOB – omit BLOB columns when not present.

SHOW VARIABLES LIKE 'binlog_row_image';
SET GLOBAL binlog_row_image = MINIMAL;

6. Real‑World Cases

6.1 Case 1 – No Auto‑Purge Leads to Disk Exhaustion

Symptoms: /var >90 % usage, MySQL data only 200 GB, binlogs 280 GB.

Root cause: expire_logs_days left at default 0.

Resolution:

# Verify replica status
SHOW SLAVE STATUS\G;
# Enable auto‑purge
SET GLOBAL expire_logs_days = 7;
# Manual purge of old logs
PURGE BINARY LOGS TO 'mysql-bin.00150';
# Persist in my.cnf
expire_logs_days = 7
# Clean up relay logs on replicas
SET GLOBAL relay_log_purge = ON;

6.2 Case 2 – Large Transaction Creates Oversized Binlog

A massive UPDATE without a WHERE clause generated a 50 GB binlog, exceeding max_binlog_size (1 GB).

Solution: split the operation into batches using a stored procedure.

DELIMITER //
CREATE PROCEDURE batch_update()
BEGIN
  DECLARE batch_size INT DEFAULT 1000;
  DECLARE offset_num INT DEFAULT 0;
  DECLARE done INT DEFAULT FALSE;
  WHILE NOT done DO
    UPDATE large_table SET status = 1 WHERE id BETWEEN offset_num AND offset_num + batch_size - 1;
    SET offset_num = offset_num + batch_size;
    IF ROW_COUNT() < batch_size THEN SET done = TRUE; END IF;
    COMMIT;
  END WHILE;
END//
DELIMITER ;
CALL batch_update();

6.3 Case 3 – Relay Log Bloat on a Replica

Replica disk alert, relay‑log directory at 200 GB.

Root causes include replication stalls, network latency, or huge transactions.

Fixes:

# Ensure purge is on
SET GLOBAL relay_log_purge = ON;
# Diagnose replication errors
SHOW SLAVE STATUS\G;  -- check Last_Error, Last_IO_Error
# Manually purge if needed
STOP SLAVE;
PURGE RELAY LOGS;
START SLAVE;

7. Monitoring Binary Logs

7.1 Key Metrics

SELECT COUNT(*) AS total_files, SUM(FILE_SIZE)/1024/1024 AS total_mb, MAX(FILE_SIZE)/1024/1024 AS max_file_mb FROM information_schema.FILES WHERE FILE_TYPE='BINARY LOG';
SHOW STATUS LIKE 'Binlog%';  -- cache usage, writes, etc.
SHOW STATUS LIKE 'Slave%';   -- replication‑related stats

7.2 Bash Monitoring Script

#!/bin/bash
ALERT_THRESHOLD_GB=100
MYSQL_USER="root"
MYSQL_PASS="YourPassword"
TOTAL_SIZE=$(mysql -u ${MYSQL_USER} -p${MYSQL_PASS} -N -e "SELECT SUM(FILE_SIZE)/1024/1024/1024 FROM information_schema.FILES WHERE FILE_TYPE='BINARY LOG';")
if [ "$(echo "$TOTAL_SIZE > $ALERT_THRESHOLD_GB" | bc)" -eq 1 ]; then
  echo "ALERT: Binary log size (${TOTAL_SIZE}GB) exceeds threshold (${ALERT_THRESHOLD_GB}GB)"
  exit 1
fi
OLDEST_LOG=$(mysql -u ${MYSQL_USER} -p${MYSQL_PASS} -N -e "SHOW BINARY LOGS;" | head -1 | awk '{print $1}')
NEWEST_LOG=$(mysql -u ${MYSQL_USER} -p${MYSQL_PASS} -N -e "SHOW BINARY LOGS;" | tail -1 | awk '{print $1}')
echo "Binary Log Report:"
echo "Total Size: ${TOTAL_SIZE}GB"
echo "Files: ${OLDEST_LOG} to ${NEWEST_LOG}"

7.3 Prometheus Exporter Configuration

# mysql_exporter scrape job
- job_name: 'mysql'
  static_configs:
    - targets: ['localhost:9104']
  relabel_configs:
    - source_labels: [__address__]
      target_label: instance
      regex: '(.+):\d+'
      replacement: '${1}'

8. Recovery Practices

8.1 Point‑in‑Time Recovery

# Recover to 2026‑01‑15 10:00:00
mysqlbinlog --stop-datetime="2026-01-15 09:59:59" /var/log/mysql/mysql-bin.000123 > /tmp/recovery.sql
mysql -u root -p < /tmp/recovery.sql

8.2 Position‑Based Recovery

# Identify position
mysqlbinlog --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000123 | grep -A5 "DROP TABLE" | head -30
# Replay up to that position
mysqlbinlog --stop-position=12345678 /var/log/mysql/mysql-bin.000123 > /tmp/recovery.sql
mysql -u root -p < /tmp/recovery.sql

8.3 Recovering from a Replica

If the primary’s binlogs are lost, use the replica’s relay logs.

# On replica, note Master_Log_File and Exec_Master_Log_Pos
SHOW SLAVE STATUS\G;
# Extract needed range from relay log
mysqlbinlog --start-position=123 --stop-position=12345678 /var/lib/mysql/mysql-relay-bin.000050 > /tmp/slave_recovery.sql
mysql -u root -p < /tmp/slave_recovery.sql

9. Best‑Practice Summary

9.1 Configuration Checklist

[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
max_binlog_size = 1G
expire_logs_days = 7
sync_binlog = 1000   # adjust per workload
binlog_cache_size = 8M
max_binlog_cache_size = 512M
log_slave_updates = ON
relay_log_purge = ON
relay_log_recovery = ON

9.2 Operational Routine

Daily check binlog directory size.

Set alerts at ~80 % disk usage.

Record binlog position before major data changes.

Back up binlogs to off‑site storage regularly.

9.3 Recovery Planning

Quarterly test point‑in‑time and position‑based restores.

Maintain up‑to‑date documentation of backup locations and latest binlog positions.

Log large transaction boundaries to simplify selective recovery.

Conclusion

Binary logs are indispensable for replication and point‑in‑time recovery, yet they can quickly fill disks if unmanaged. Understanding their structure, configuring automatic expiration, monitoring growth, and applying careful cleanup—especially in replication topologies—prevents outages. Combining proper configuration, routine checks, and tested recovery procedures ensures MySQL environments remain reliable and performant.