Three solutions for MySQL to accurately control the number of Binlog logs

One trick to modify the configuration file: a permanent control technique

1. Locate the file

Configuration file locations for different systems:

• Linux：/etc/or/etc/mysql/
• Windows：C:\ProgramData\MySQL\MySQL Server \

2. Add core parameters

[mysqld]
max_binlog_size = 256M      # Maximum size of a single file (default 1G)max_binlog_files = 30       # The latest number of retained files (exclusive to MySQL 8.0+)expire_logs_days = 7        # Old file survival cycle# expire_logs_seconds=604800 # More accurate time control (in seconds)

Restart takes effect

sudo systemctl restart mysqld   # Linux
# Windows restarts MySQL service through the service manager

No restart hot update: Master emergency killing skills

Dynamic adjustment through MySQL command (temporary effect, invalid after restart):

-- Set log expiration time（3sky）
SET GLOBAL expire_logs_seconds = 259200;  

-- Resize a single log file（1GB）
SET GLOBAL max_binlog_size = 1073741824;  

-- View all currentlyBinlogdocument
SHOW BINARY LOGS;

Ultimate Cleaning Tips: Manual and Precise Roots

-- Delete all logs before the specified file（Be sure to back up before high-risk operations！）
PURGE BINARY LOGS TO 'mysql-bin.000358';  

-- Clean up by time（Format：'YYYY-MM-DD hh:mm:ss'）
PURGE BINARY LOGS BEFORE '2024-02-01 00:00:00';  

Three major gold configuration strategies (scenario adaptation)

Five devastating operational restricted areas

1. Delete the log in transmission: The log that has not been completed by the master-slave replication is deleted, which directly leads to cluster split.
2. Clean up before backup: Point-in-time recovery (PITR) cannot be performed after accidentally deletion, and data is permanently lost.
3. Violent rm deletes files: causesThe index files are inconsistent, and MySQL crashes.
4. set upexpire_logs_days=0: It is equivalent to permanent retention, and the disk must be "eat" empty.
5. Master-slave inconsistent configuration: The Binlog parameters of the master-slave node must be completely consistent!

First aid tool box

-- Real-time monitoringBinlogTotal space occupied
SELECT 
    SUM(ROUND((LENGTH(logged_data)/1024/1024),2)) AS "Total occupancy(GB)",
    COUNT(*) AS "Number of Files"
FROM mysql.general_log;  

-- Check the progress of master-slave synchronization（Avoid accidentally deleting active logs）
SHOW SLAVE STATUS\G

MySQL version 8.4 major update

The latest version of 2025 optimizes Binlog management:

• Intelligent dynamic expansion: Added binlog_auto_tuning parameters to automatically adjust file size and retention policy.
• Cloud Storage Direct Connection: Supports direct archive of Binlog to object storage such as AWS S3, Alibaba Cloud OSS.
• Compression algorithm upgrade: Use the Zstandard algorithm to save 60% disk space.

The above is the detailed content of three solutions for MySQL to accurately control the number of Binlog logs. For more information about MySQL to control the number of Binlogs, please pay attention to my other related articles!