How to Safely Clean Up MySQL Binlog When Disk Space Is Critical

Problem Background

MySQL binary logs (binlog) record all data‑changing operations and are essential for replication, incremental backup, and audit. Because binlog only grows, unchecked accumulation can fill the disk, causing MySQL to crash. Improper manual deletion often breaks replication or leads to data loss.

Binlog Basics

What Binlog Records

Data‑changing statements: INSERT, UPDATE, DELETE, REPLACE

DDL statements: CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE

Database operations: CREATE DATABASE, DROP DATABASE

Replication statements (in STATEMENT format): BEGIN, COMMIT, ROLLBACK

Binlog does not record SELECT, SHOW, DESCRIBE, or non‑changing BEGIN.

Storage Mechanism

Binlog files are stored in the data directory with names like mysql-bin.000001. You can view them with:

SHOW VARIABLES LIKE 'datadir';
SHOW VARIABLES LIKE 'log_bin';
SHOW BINARY LOGS;

Example output shows file sizes and encryption status.

File Switching

MySQL creates a new binlog when any of the following occurs:

Executing FLUSH LOGS File size reaches max_binlog_size (default 1 GB, configurable)

MySQL restarts

Even if a file is 900 MB, a single large transaction can trigger a switch, so max_binlog_size is an approximate limit.

Binlog and Transactions

In ROW format, each row’s before‑image and after‑image are logged, which can make the binlog much larger than the original SQL statements. Example: a transaction updating 10,000 rows generates 10,000 row‑level events.

Binlog Formats

Controlled by binlog_format:

STATEMENT : logs the SQL statement (small size, but functions like NOW() may diverge on replicas).

ROW : logs each row change (large size, strong consistency).

MIXED : defaults to STATEMENT, switches to ROW when needed (most common in production).

Expiration Policy

Two variables control automatic cleanup: expire_logs_days: number of days to keep binlog (0 disables cleanup). binlog_expire_logs_seconds (MySQL 8.0+): seconds to keep binlog; overrides expire_logs_days when both are set.

Automatic cleanup only runs when there are at least two binlog files and the configured time has passed.

Preparation Before Cleanup

Check Disk Usage

SELECT COUNT(*) AS binlog_count, SUM(File_size)/1024/1024/1024 AS total_size_gb FROM information_schema.GLOBAL_VARIABLES CROSS JOIN (SHOW BINARY LOGS) AS logs;

Identify why usage is high (large batch jobs, replication lag, big transactions, ROW format).

Analyze Growth Causes

SELECT Log_name, File_size/1024/1024 AS size_mb FROM information_schema.GLOBAL_STATUS CROSS JOIN (SHOW BINARY LOGS) AS logs WHERE Variable_name='Binlog_files';

Typical scenarios:

Massive data changes (batch imports).

Replica lag causing the primary to retain old logs.

Very large single transactions.

ROW format producing many row events.

Check Replication Status

SHOW SLAVE HOSTS;
SHOW SLAVE STATUS\G;

Key fields: Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master, Master_Log_File, Read_Master_Log_Pos.

Confirm No Critical Binlog Dependency

When was the last full backup?

Has the backup been verified?

Any ongoing incremental restores?

Do you need binlog for point‑in‑time audit?

Optional Binlog Backup

BACKUP_DIR="/data/backup/binlog_$(date +%Y%m%d_%H%M%S)"
mkdir -p "$BACKUP_DIR"
ls -lh mysql-bin.* | tail -n 10 | awk '{print $9}' | xargs -I {} cp {} "$BACKUP_DIR/"

Safe Cleanup Methods

Method 1: PURGE MASTER LOGS

Use MySQL’s built‑in command instead of deleting files.

-- Delete all logs before a specific file
PURGE MASTER LOGS TO 'mysql-bin.000100';

-- Delete logs before a specific datetime
PURGE MASTER LOGS BEFORE '2026-05-20 00:00:00';

Steps:

On the replica, run SHOW SLAVE STATUS\G and note Master_Log_File (e.g., mysql-bin.000095).

On the primary, verify that file exists with SHOW BINARY LOGS;.

PURGE up to the file *just before* the oldest file the replica has synced (e.g., PURGE MASTER LOGS TO 'mysql-bin.000094';).

Verify with SHOW BINARY LOGS; and SHOW MASTER STATUS;.

Check replica status again to ensure Slave_IO_Running and Slave_SQL_Running are still Yes.

Method 2: Automatic Expiration

Set a retention period so MySQL cleans old logs automatically.

-- MySQL 5.7
SET GLOBAL expire_logs_days = 7;

-- MySQL 8.0 (recommended)
SET GLOBAL binlog_expire_logs_seconds = 604800;  -- 7 days

Persist the setting in the config file ( /etc/my.cnf or /etc/my.cnf.d/mysql-server.cnf) and reload with FLUSH LOGS;.

Method 3: MySQL 8.0 Advanced Management

Enable binlog encryption ( binlog_encryption = ON) for security.

Set binlog_replica_mode = 'semi_sync' (MySQL 8.0.20+) to ensure safe replication.

Use binlog_expire_logs_auto_cleanup = ON (default) for automatic removal.

Method 4: Off‑load Binlog to an Archive Server

Configure a dedicated server to receive a copy of the primary’s binlog for long‑term audit while the primary keeps a short retention window.

# Example my.cnf for the archive server
[mysqld]
server-id = 200
relay-log = /data/binlog_archive/relay-log
read-only = ON
replicate-do-db = app_db

Set relay_log_purge = ON on the archive to clean its own relay logs.

Common Mistakes & Risks

1. Purging Before Replica Catches Up

Deleting logs that the replica has not yet read breaks replication (error "Could not find first log file"). Correct practice: always verify the replica’s Master_Log_File and purge only older files.

2. Deleting Files Directly (rm)

Manual rm can remove the active binlog and cause irreversible data loss. Always use PURGE MASTER LOGS or the expiration variables.

3. Setting expire_logs_days = 0

Zero disables automatic cleanup, leading to unbounded growth. Change to a reasonable value (e.g., 7 days) immediately after installation.

4. Running PURGE During Peak Load

Large PURGE operations consume I/O and may temporarily degrade performance. Schedule during low‑traffic windows (e.g., 02:00‑04:00).

5. Forgetting to Update Replica Position

After PURGE, record Master_Log_File and Read_Master_Log_Pos. If replication stops, use CHANGE MASTER TO with the last known position.

6. Not Verifying Data Consistency

After cleanup, compare row counts or checksums between primary and replica for critical tables.

Verification After Cleanup

Disk Space

# Before/after size comparison
du -sh /var/lib/mysql/;

Binlog List

SHOW BINARY LOGS;

Replication Health

SHOW SLAVE STATUS\G

Ensure Slave_IO_Running=Yes, Slave_SQL_Running=Yes, and Seconds_Behind_Master is low or decreasing.

Data Consistency

SELECT 'primary' AS source, COUNT(*) AS cnt, MAX(id) AS max_id FROM app_db.orders
UNION ALL
SELECT 'replica' AS source, COUNT(*) AS cnt, MAX(id) AS max_id FROM app_db.orders;

Error Log

tail -n 100 /var/log/mysql/error.log | grep -iE "ERROR|WARNING|PURGE|binlog";

Production Checklist

[ ] Notify stakeholders of maintenance window
[ ] Verify replica status (IO/SQL running, no lag)
[ ] Record replica's Master_Log_File and Read_Master_Log_Pos
[ ] Ensure latest full backup exists and is verified
[ ] Confirm no pending incremental restores
[ ] Check current binlog disk usage and file count
[ ] Determine the oldest binlog that must be kept
[ ] Schedule PURGE during off‑peak hours
[ ] Keep a backup of purged logs (optional)
[ ] Prepare verification scripts for post‑cleanup checks

Monitoring & Alerts

Set up Prometheus/Grafana alerts for binlog disk usage, file count, and replica lag. Example alert rules are provided in the original article (omitted here for brevity).

Daily Inspection Script (Example)

#!/bin/bash
MYSQL_USER="root"
MYSQL_PASS="your_password"
ALERT_EMAIL="[email protected]"

# Disk usage check
DISK_USAGE=$(df -h /var/lib/mysql/ | tail -1 | awk '{print $5}' | tr -d '%')
if [ "$DISK_USAGE" -gt 80 ]; then
  echo "Binlog disk usage >80% (${DISK_USAGE}%)" | mail -s "[MySQL] Binlog Disk Alert" $ALERT_EMAIL
fi

# Binlog count check
BINLOG_COUNT=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW BINARY LOGS;" | wc -l)
if [ "$BINLOG_COUNT" -gt 100 ]; then
  echo "Binlog count >100 ($BINLOG_COUNT)" | mail -s "[MySQL] Binlog Count Alert" $ALERT_EMAIL
fi

# Replica lag check
SLAVE_LAG=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')
if [ -n "$SLAVE_LAG" ] && [ "$SLAVE_LAG" -gt 300 ]; then
  echo "Replica lag >300s ($SLAVE_LAG)" | mail -s "[MySQL] Replica Lag Alert" $ALERT_EMAIL
fi

# Summary report
BINLOG_SIZE=$(du -sh /var/lib/mysql/mysql-bin.* 2>/dev/null | awk '{sum+=$1} END {print sum}')
echo "Binlog Daily Report - $(date)"
echo "Disk usage: ${DISK_USAGE}%"
echo "Binlog files: $BINLOG_COUNT"
echo "Total binlog size: $BINLOG_SIZE"
echo "Replica lag: ${SLAVE_LAG}s"

Schedule the script via cron to run daily and send alerts.

Summary

Cleaning MySQL binlog safely revolves around four principles: Safety First . Never delete files manually, always verify replica sync points, perform cleanup during low‑traffic periods, and validate both replication health and data consistency after the operation. Configure expire_logs_days or binlog_expire_logs_seconds for automatic cleanup, enable monitoring, and maintain a regular inspection routine to prevent disk‑full emergencies.