MySQL binary log files (mysql-bin.xxxxxx) are crucial components of MySQL's replication and recovery system. They record all data modification statements (INSERT, UPDATE, DELETE) and schema changes. While essential for:

• Point-in-time recovery
• Replication between servers
• Audit trails

These files can grow large over time, especially on busy databases.

First, examine your binary log status:

SHOW BINARY LOGS;
SHOW VARIABLES LIKE 'expire_logs_days';
SHOW VARIABLES LIKE 'max_binlog_size';

This reveals the current log files and retention settings.

Method 1: Using PURGE BINARY LOGS

The safest way to remove old binary logs:

-- Remove all logs before a specific file
PURGE BINARY LOGS TO 'mysql-bin.000010';

-- Remove logs older than specific date
PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';

Method 2: Configuring Automatic Expiration

Set automatic expiration in my.cnf/my.ini:

[mysqld]
expire_logs_days = 7
max_binlog_size = 100M

Then restart MySQL or set dynamically:

SET GLOBAL expire_logs_days = 7;

Method 3: Manual File Deletion (Not Recommended)

If you must delete manually:

1. Stop MySQL service
2. Delete the files
3. Update the index file
4. Restart MySQL

• Ensure no slaves are using logs you plan to purge
• Maintain at least one complete backup cycle worth of logs
• Monitor disk space after cleanup

Create a cron job for regular maintenance:

0 3 * * * mysql -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY)"

If you encounter errors after cleanup:

-- Recreate index if corrupted
mysqlbinlog --force-if-open mysql-bin.000001 > /dev/null

---

MySQL binary logs (mysql-bin.xxxxxx files) contain all statements that update data or potentially could have updated it. These files serve two primary purposes:

• For replication: The binary log is used to send changes from master to slave servers
• For point-in-time recovery: After restoring a backup, you can replay binary logs to reconstruct data changes

In your case, you're seeing multiple large binary log files occupying significant disk space:

/var/lib/mysql/mysql-bin.000003: 1.1G
/var/lib/mysql/mysql-bin.000004: 225M

This typically happens when:

• binlog_expire_logs_seconds isn't configured (or set too high)
• Replication is lagging, preventing log rotation
• You have high write volume but infrequent log purges

Method 1: Configure Automatic Expiration

Add to your my.cnf/my.ini:

[mysqld]
expire_logs_days = 3  # Older method (days)
# OR the newer seconds-based approach:
binlog_expire_logs_seconds = 259200  # 3 days in seconds

Method 2: Manual Purge Command

Connect to MySQL and run:

PURGE BINARY LOGS TO 'mysql-bin.000007';  # Keeps 000007 and newer
# OR
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;

Before removing binary logs:

• Ensure all replicas have processed the logs you plan to delete
• Take a backup if you might need point-in-time recovery
• Verify no applications are reading older logs

Create a simple monitoring script:

#!/bin/bash
BINLOG_DIR=/var/lib/mysql
THRESHOLD=80 # Percentage

usage=$(df --output=pcent $BINLOG_DIR | tail -1 | tr -d '% ')
if [ $usage -gt $THRESHOLD ]; then
    mysql -e "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 2 DAY;"
    logger "MySQL binary logs purged due to disk space threshold"
fi

[mysqld]
binlog_format = ROW  # More compact than STATEMENT
binlog_row_image = MINIMAL  # Only log changed columns
max_binlog_size = 100M  # Rotate logs at this size