MySQL Disk Space Explodes: How Binary Logs Become the Hidden Culprit

Background and Problem

Operations engineers often encounter MySQL disk‑space alerts while the actual data size is modest. Investigation shows that binary logs (binlogs) are the primary space consumers.

Binary logs are essential for replication and point‑in‑time recovery, but without proper management they can fill disks quickly. The analysis is based on MySQL 8.0.36 on Ubuntu 24.04 LTS, with all commands verified in a real environment.

1. Binary Log Fundamentals

1.1 Purpose of Binary Logs

Binary logs record every data‑modifying operation, including:

INSERT, UPDATE, DELETE statements

CREATE, ALTER, DROP statements (configurable)

Replication heartbeat events

Key uses:

Master‑to‑slave replication

Point‑in‑time data recovery via mysqlbinlog Incremental backups combined with full backups

Auditing database change history

1.2 File Structure

Binary log files are named mysql-bin.000001, mysql-bin.000002, etc.

# List binary log directory
ls -lh /var/log/mysql/
# Show binary logs
mysql -u root -p -e "SHOW BINARY LOGS;"
# Show current binary log
mysql -u root -p -e "SHOW MASTER STATUS;"

Each file contains multiple events, such as: Format_description: records MySQL version Table_map: maps tables involved in events Write_rows/Update_rows/Delete_rows: actual row changes Xid: transaction commit

1.3 Binary Log Formats

MySQL 8.0 supports three formats:

ROW : records full row changes (largest size)

STATEMENT : records the SQL statement (smaller but may be nondeterministic)

MIXED : defaults to STATEMENT, switches to ROW when needed

-- View current format
SHOW VARIABLES LIKE 'binlog_format';
-- Set temporary format
SET GLOBAL binlog_format = 'ROW';
-- Permanent change in my.cnf
# binlog_format = ROW

2. Binary Log Disk‑Space Issues

2.1 Scenario

A production MySQL instance reports 500 GB usage in /var/lib/mysql, yet actual data occupies only 80 GB. Binary logs account for >400 GB, containing six months of change history.

2.2 Growth Factors

High write workload accelerates log growth.

ROW format generates more data than STATEMENT.

Large tables with small updates still produce sizable ROW logs.

# Check total binary log size (GB)
SELECT SUM(FILE_SIZE)/1024/1024/1024 AS total_size_gb FROM information_schema.FILES WHERE FILE_TYPE='BINARY LOG';
# List each log file size (MB)
SELECT LOG_NAME AS file_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 binary logs:

Automatic : set expire_logs_days (default 0 = never).

Manual : execute PURGE BINARY LOGS commands.

# View automatic cleanup setting
SHOW VARIABLES LIKE 'expire_logs_days';
# Set to keep last 7 days
SET GLOBAL expire_logs_days = 7;
# Permanent change in my.cnf
# expire_logs_days = 7

Manual Purge Examples

# Delete logs before a specific date
PURGE BINARY LOGS BEFORE '2026-01-01 00:00:00';
# Delete up to a specific log file
PURGE BINARY LOGS TO 'mysql-bin.000100';
# Verify current log file
SHOW MASTER STATUS;

Important: never purge logs that slaves have not yet processed; otherwise replication breaks. Verify with SHOW SLAVE STATUS on each replica.

# Check slave read position
SHOW SLAVE STATUS\G
# Look at Master_Log_File and Relay_Master_Log_File fields

3. Disk‑Space Troubleshooting Steps

3.1 Initial Diagnosis

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

3.2 Binary Log Usage Analysis

# List all binary logs with sizes
mysql -u root -p -e "SELECT LOG_NAME, FILE_SIZE/1024/1024 AS size_mb FROM information_schema.FILES WHERE FILE_TYPE='BINARY LOG' ORDER BY LOG_NAME;"
# Total size in GB
mysql -u root -p -e "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 Other Space Consumers

If df shows high usage but du on MySQL files is low, consider:

Open file handles not released ( lsof).

MySQL temporary files ( tmpdir).

System logs.

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

4. Special Handling in Replication Environments

4.1 Replication Architecture Overview

In master‑slave setups:

Master must have binary logging enabled.

Slave reads the master’s binlogs via an I/O thread into a relay log.

SQL thread applies the relay log events.

# Master view slave connections
SHOW SLAVE HOSTS;
# Slave view replication status
SHOW SLAVE STATUS\G

4.2 Should a Slave Enable Binary Logging?

Enable on a slave only if it also acts as a master for downstream replicas or if incremental backups are required; otherwise it can be disabled.

# Check slave binary‑log setting
SHOW VARIABLES LIKE 'log_slave_updates';
# Enable if needed
SET GLOBAL log_slave_updates = ON;

4.3 Log Cleanup in Replication

Only delete logs that all slaves have already processed.

# List all slaves
SHOW SLAVE HOSTS;
# Check each slave’s read position
SHOW SLAVE STATUS\G
# Identify the oldest log file still needed by any slave, then purge older files
PURGE BINARY LOGS TO 'mysql-bin.000050';

4.4 GTID Mode Management

GTID simplifies replication and influences log cleanup.

# View GTID settings
SHOW VARIABLES LIKE 'gtid_mode';
SHOW VARIABLES LIKE 'enforce_gtid_consistency';
# Show executed GTID set
SELECT @@GLOBAL.gtid_executed;
SELECT @@GLOBAL.gtid_purged;
# In GTID mode, <code>PURGE BINARY LOGS</code> skips logs containing unexecuted transactions; attempting to purge such a log raises an error.

5. Binary Log Configuration Optimization

5.1 Basic Parameters (my.cnf)

# /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 to 0 for performance, or N for trade‑off
binlog_row_image = FULL   # set to MINIMAL to reduce size when no BLOB columns

5.2 Performance vs. Safety Trade‑offs

sync_binlog

controls when the binary log is flushed to disk: 1: flush after every transaction (most safe, highest I/O). 0: rely on OS flush (best performance, risk of losing multiple transactions on crash). N: flush every N transactions (balanced).

# View current setting
SHOW VARIABLES LIKE 'sync_binlog';
# Example: flush every 100 transactions
SET GLOBAL sync_binlog = 100;

5.3 Binary Log Cache Tuning

When Binlog_cache_disk_use grows, increase binlog_cache_size to keep more transactions in memory.

# View cache usage
SHOW STATUS LIKE 'Binlog_cache%';
# Increase cache to 8 MB
SET GLOBAL binlog_cache_size = 8*1024*1024;

5.4 Row‑Format Optimizations

binlog_row_image

determines how much column data is stored in ROW format: FULL: all columns. MINIMAL: only primary key and changed columns. NOBLOB: omit BLOB columns when none exist.

# View current value
SHOW VARIABLES LIKE 'binlog_row_image';
# Reduce size for tables without BLOBs
SET GLOBAL binlog_row_image = MINIMAL;

6. Real‑World Cases

6.1 Case 1 – No Automatic Cleanup

Symptoms : /var >90 % used; du -sh /var/lib/mysql shows only 200 GB, but df -h reports 350 GB.

Root Cause : expire_logs_days unset (default 0), binary logs never purged.

Resolution Steps :

# Verify slave status
SHOW SLAVE STATUS\G
# Enable automatic cleanup (keep 7 days)
SET GLOBAL expire_logs_days = 7;
# Manual purge of old logs
PURGE BINARY LOGS TO 'mysql-bin.00150';
# Persist setting in my.cnf
# expire_logs_days = 7
# Ensure relay logs are also purged
SET GLOBAL relay_log_purge = ON;

6.2 Case 2 – Large Transaction Creates Oversized Log File

Symptoms : A single UPDATE on a large table spikes a binlog by 50 GB; max_binlog_size is 1 GB but the file grows beyond 10 GB.

Analysis : In ROW format, a massive transaction must be written to a single binlog file; max_binlog_size only triggers rotation, not a hard limit.

Solution : Split the operation into batches.

-- Original massive update
-- UPDATE large_table SET status = 1;
-- Batch 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 Accumulates on a Slave

Symptoms : Slave reports disk‑space alarm while master is fine; relay‑log directory uses 200 GB.

Root Cause : Although relay_log_purge defaults to ON, replication interruptions, network stalls, or very large transactions prevent automatic cleanup.

Fix :

# Ensure relay log purge is enabled
SHOW VARIABLES LIKE 'relay_log_purge';
SET GLOBAL relay_log_purge = ON;
# Diagnose replication errors
SHOW SLAVE STATUS\G   # check Last_Error, Last_IO_Error
# Manually purge relay logs if needed
STOP SLAVE;
PURGE RELAY LOGS;
START SLAVE;

7. Binary Log Monitoring

7.1 Metrics

# Total binary log size and file count
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';
# Binlog cache usage
SHOW STATUS LIKE 'Binlog%';
# Replication‑related stats
SHOW STATUS LIKE 'Slave%';

7.2 Monitoring Script (Bash)

#!/bin/bash
ALERT_THRESHOLD_GB=100
LOG_DIR="/var/log/mysql"
MYSQL_USER="root"
MYSQL_PASS="YourPassword"
# Get total binary log size (GB)
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 and newest log files
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}"

7.3 Prometheus Exporter Configuration

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

8. Binary Log Recovery Practices

8.1 Point‑in‑Time Recovery

# Find the log file covering the target time
mysqlbinlog --no-defaults --stop-datetime="2026-01-15 09:59:59" /var/log/mysql/mysql-bin.000123 > /tmp/full_recovery.sql
# Apply the recovered SQL
mysql -u root -p < /tmp/full_recovery.sql

8.2 Position‑Based Recovery

# Locate the exact position
mysqlbinlog --no-defaults --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000123 | grep -A 5 "DROP TABLE" | head -30
# Extract up to the desired position
mysqlbinlog --no-defaults --stop-position=12345678 /var/log/mysql/mysql-bin.000123 > /tmp/recovery.sql
mysql -u root -p < /tmp/recovery.sql

8.3 Recovering from a Slave

If the master’s binlogs are unavailable, use the slave’s relay logs.

# On the slave, note Master_Log_File and Exec_Master_Log_Pos
SHOW SLAVE STATUS\G
# Extract needed range from relay log
mysqlbinlog --no-defaults --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 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 for performance‑safety balance
binlog_cache_size = 8M
max_binlog_cache_size = 512M
log_slave_updates = ON
relay_log_purge = ON
relay_log_recovery = ON

9.2 Operational Procedures

Check binary‑log directory size daily.

Set alert thresholds (e.g., 80 % of disk capacity).

Record current binlog position before major data changes.

Back up binary logs to off‑site storage regularly.

9.3 Recovery Planning

Test recovery quarterly.

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

Log positions of large transactions for selective restores.