Why MySQL Binary Logs Fill Up Disk Space and How to Fix It

Background

Operations engineers often encounter disk‑space alerts on MySQL servers where the data directory appears small, yet the underlying storage is nearly full. Investigation typically reveals that binary logs (binlogs) are the primary consumer of space.

Binary Log Fundamentals

Purpose of Binary Logs

Binary logs record every change to database data, including DML statements (INSERT, UPDATE, DELETE), DDL statements (CREATE, ALTER, DROP when enabled), and server events such as heartbeats in replication. They are essential for:

Master‑slave replication – slaves read the master’s binlogs to stay synchronized.

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

Incremental backups – combining full backups with binlogs yields incremental snapshots.

Auditing – providing a historical record of data modifications.

File Structure

Binlog files follow the naming pattern mysql-bin.000001, mysql-bin.000002, etc. Each file contains multiple events, such as: Format_description – records MySQL version information. Table_map – maps tables involved in the event. Write_rows / Update_rows / Delete_rows – actual row changes. Xid – transaction commit.

Log Formats

MySQL 8.0 supports three binlog formats:

ROW : records each row change; safest but produces the largest logs.

STATEMENT : records the original SQL; smaller but can yield nondeterministic results.

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

Disk‑Space Problems

Typical Scenario

A production MySQL instance reports >500 GB disk usage under /var/lib/mysql while the actual data size is only ~80 GB. Queries show that binary logs occupy >400 GB, indicating a missing cleanup policy.

Growth Factors

Log growth depends on workload intensity, chosen log format (ROW generates more data than STATEMENT), and the size of affected tables.

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

Cleanup Mechanisms

MySQL offers two ways to purge binlogs:

Automatic cleanup via the expire_logs_days system variable.

Manual cleanup using PURGE BINARY LOGS statements.

SHOW VARIABLES LIKE 'expire_logs_days';
SET GLOBAL expire_logs_days = 7;

Investigation Steps

Initial Diagnosis

# Check overall disk usage
df -h
# Inspect MySQL data directory
du -sh /var/lib/mysql/*
# List binlog directory
ls -lh /var/log/mysql/mysql-bin.* | tail -20

Binlog Size Analysis

SELECT LOG_NAME, FILE_SIZE/1024/1024 AS size_mb
FROM information_schema.FILES
WHERE FILE_TYPE='BINARY LOG'
ORDER BY FILE_SIZE DESC
LIMIT 10;

Identify Hidden Consumers

If df shows high usage but du reports small files, investigate open file handles, temporary files, and system logs.

# List open MySQL files
sudo lsof -p $(pgrep mysqld) | grep -E "REG|DIR" | awk '{print $NF}' | sort | uniq | xargs -I {} ls -lh {} 2>/dev/null
# Show temporary directory
SHOW VARIABLES LIKE 'tmpdir';
# Check temporary table usage
SHOW STATUS LIKE 'Created_tmp%';

Replication‑Specific Handling

Master‑Slave Architecture

In replication, the master must keep binlogs until all slaves have read them. Slaves read the master’s binlogs via an I/O thread, store them as relay logs, and apply them with an SQL thread.

SHOW SLAVE HOSTS;
SHOW SLAVE STATUS\G;

Should a Slave Enable Binlog?

Enable binlog on a slave only if it also acts as a master for downstream slaves or if incremental backups from the slave are required.

SHOW VARIABLES LIKE 'log_slave_updates';

Safe Cleanup in Replication

Delete only logs that every slave has already processed. Identify the oldest Relay_Master_Log_File across slaves and purge up to that point.

# Automatic purge with mysqlrpladmin (if installed)
mysqlrpladmin --master=root:pass@master-host --discover-slaves-for=master prune
# Manual safe purge
PURGE BINARY LOGS TO 'mysql-bin.000100';

GTID Mode

GTID simplifies log management; purge operations automatically respect executed GTIDs.

SHOW VARIABLES LIKE 'gtid_mode';
SHOW GLOBAL VARIABLES LIKE 'gtid_executed';
PURGE BINARY LOGS TO 'mysql-bin.000100';

Configuration Optimizations

# /etc/mysql/mysql.conf.d/mysqld.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 trade‑off
binlog_row_image = FULL

Performance vs. Safety

sync_binlog

controls when the binlog is flushed to disk: 1 – flush after every transaction (most reliable, highest overhead). 0 – rely on OS flushing (fastest, risk of losing multiple transactions).

Any other number – flush after N transactions (balanced).

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

Cache Tuning

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

Row‑Image Optimization

SHOW VARIABLES LIKE 'binlog_row_image';
SET GLOBAL binlog_row_image = MINIMAL;  # store only changed columns and primary key

Monitoring

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%';
SHOW STATUS LIKE 'Slave%';

Monitoring Script (Bash)

#!/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}"
echo "Oldest file: ${OLDEST_LOG}"

Prometheus Exporter Configuration

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

Recovery Practices

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/full_recovery.sql
mysql -u root -p < /tmp/full_recovery.sql

Position‑Based Recovery

# Find position, then recover up to it
mysqlbinlog --stop-position=12345678 \
  /var/log/mysql/mysql-bin.000123 > /tmp/recovery.sql
mysql -u root -p < /tmp/recovery.sql

Recover from a Slave

# On the slave, note Master_Log_File and Exec_Master_Log_Pos
SHOW SLAVE STATUS\G
# Use relay logs for recovery
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

Best‑Practice Summary

Configuration Standards

# /etc/mysql/mysql.conf.d/mysqld.cnf
[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

Operational Guidelines

Check binlog directory size daily.

Set alert thresholds at ~80 % of disk capacity.

Record current binlog position before major data changes.

Back up binlogs to off‑site storage regularly.

Recovery Planning

Test the full recovery workflow quarterly.

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

Log the start/end positions of large transactions for selective restores.

Conclusion

Binary logs are indispensable for MySQL replication and point‑in‑time recovery, yet they can silently consume massive disk space if not managed properly. Understanding their structure, choosing an appropriate format, configuring automatic expiration, monitoring growth, and applying safe cleanup—especially in replication topologies—prevents outages. Coupled with regular backups and tested recovery procedures, these practices ensure data integrity while balancing performance and storage efficiency.