Safely Purge MySQL Binary Logs in a Cluster

This article explains how to safely remove expired MySQL binary log (binlog) files from a database cluster without disrupting replication or causing data inconsistency. You will learn how to verify replication status, execute the correct SQL commands for safe manual purging, and configure automatic retention policies to prevent disk space issues in the future.

Why You Must Never Delete Binlogs Manually

Never delete binary log files directly from the operating system using commands like rm. Doing so bypasses MySQL, leaving the binary log index file (.index) out of sync. This can crash the MySQL server, corrupt the replication metadata, and break the entire cluster. Always use MySQL’s native utility commands or configuration settings to manage binlogs.

Step 1: Verify Replication Status

Before purging any logs, you must ensure that all replica nodes in your cluster have already processed the files you intend to delete. If you delete a log file that a replica still needs, replication will break.

Log in to your replica database(s) and run:

SHOW REPLICA STATUS\G
-- Use SHOW SLAVE STATUS\G for MySQL versions older than 8.0.22

Locate the Relay_Master_Log_File value. This indicates the latest binary log file on the source (primary) database that the replica has successfully read and copied. You can safely purge any binary logs older than this file.

Step 2: Purge Logs Safely Using SQL

Log in to the primary MySQL instance and use one of the following safe methods to purge the logs.

Method A: Purge by Log File Name (Recommended)

To delete all binary logs up to a specific file, use the PURGE BINARY LOGS TO command. This deletes all logs older than the specified file, keeping the specified file and all newer ones.

PURGE BINARY LOGS TO 'mysql-bin.000123';

Method B: Purge by Date and Time

To delete all binary logs created before a specific timestamp, use the PURGE BINARY LOGS BEFORE command:

PURGE BINARY LOGS BEFORE '2023-10-25 00:00:00';

Step 3: Automate Binlog Purging

To prevent your disk from filling up again, configure MySQL to automatically purge expired binary logs.

For MySQL 8.0 and newer:

MySQL 8.0 uses the binlog_expire_logs_seconds system variable. To set the expiration time to 7 days (604,800 seconds) dynamically and persist the change across restarts, run:

SET PERSIST binlog_expire_logs_seconds = 604800;

For MySQL 5.7 and older:

Older versions use expire_logs_days. To set the expiration time to 7 days dynamically, run:

SET GLOBAL expire_logs_days = 7;

To make this change permanent in MySQL 5.7, add the following line to your my.cnf or my.ini configuration file under the [mysqld] section:

expire_logs_days = 7