When examining MySQL binary logs (mysql-bin.xxxxxx files), we notice they consistently approach the 1GB limit set by max_binlog_size but aren't being purged automatically. The system keeps creating new log files until disk space is exhausted, which is a common operational challenge for database administrators.

MySQL doesn't automatically purge binary logs just because they reach max_binlog_size. These logs are preserved for:

• Replication purposes
• Point-in-time recovery
• Audit requirements

To control log retention, we need to understand these key parameters:

max_binlog_size = 1073741824  # 1GB
expire_logs_days = 7          # Critical for automatic purging
binlog_format = ROW           # Or STATEMENT/MIXED
sync_binlog = 1               # For durability

The most effective solution is setting expire_logs_days in my.cnf:

[mysqld]
expire_logs_days = 3
max_binlog_size = 1G

This keeps logs for 3 days before automatic purging. Adjust based on your recovery requirements.

For immediate space recovery, execute:

PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;
-- Or to a specific log file
PURGE BINARY LOGS TO 'mysql-bin.000040';

Create a monitoring script to check disk usage:

#!/bin/bash
THRESHOLD=90
CURRENT=$(df /var/lib/mysql | awk '{print $5}' | tail -1 | tr -d '%')

if [ "$CURRENT" -gt "$THRESHOLD" ]; then
    mysql -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 2 DAY);"
fi

If using replication, ensure slaves have processed logs before purging:

SHOW SLAVE STATUS\G
-- Check 'Relay_Master_Log_File' and 'Exec_Master_Log_Pos'
-- Never purge logs newer than what slaves have processed

For systems with limited storage:

SET GLOBAL binlog_expire_logs_seconds = 604800;  # 7 days in seconds
FLUSH LOGS;  # Force rotation

---

When examining the file listing, we can observe that MySQL is creating binary logs approaching the configured 1GB limit (1073741824 bytes), but not automatically purging older files:

-rw-rw----  1 mysql mysql 1073741982 2012-07-03 18:14 mysql-bin.000034
-rw-rw----  1 mysql mysql 1073741890 2012-07-04 14:39 mysql-bin.000035
...
-rw-rw----  1 mysql mysql  356167680 2012-07-07 23:47 mysql-bin.000040

The current my.cnf settings control log size but lack expiration parameters:

max_binlog_size = 1073741824
log-bin = mysql-bin
max_relay_log_size = 1G
relay_log_space_limit = 2G

What's needed is expire_logs_days or binlog_expire_logs_seconds (MySQL 8.0+) to enable automatic log rotation.

Option 1: Time-based expiration (MySQL 5.7+)

# Set logs to expire after 7 days
expire_logs_days = 7

Option 2: Precise time-based expiration (MySQL 8.0+)

# Set logs to expire after 3 days (259200 seconds)
binlog_expire_logs_seconds = 259200

Option 3: Manual purge command

-- Purge logs older than specified binary log file
PURGE BINARY LOGS TO 'mysql-bin.000040';

-- Purge logs older than specific datetime
PURGE BINARY LOGS BEFORE '2022-01-01 00:00:00';

After adding the expiration parameter, restart MySQL or set dynamically:

-- For MySQL 5.7
SET GLOBAL expire_logs_days = 7;

-- For MySQL 8.0+
SET GLOBAL binlog_expire_logs_seconds = 604800;

Monitor the effect with:

SHOW VARIABLES LIKE 'expire_logs%';
SHOW BINARY LOGS;

For replication environments, ensure the expiration period exceeds potential slave lag. A recommended formula:

expire_logs_days = max(slave_lag_in_days) + 2

Combine with monitoring to alert when log space exceeds thresholds:

#!/bin/bash
THRESHOLD=90
CURRENT=$(df /var/lib/mysql | awk '{print $5}' | tail -1 | sed 's/%//')
if [ "$CURRENT" -gt "$THRESHOLD" ]; then
    mysql -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 1 DAY)"
fi