How to Reduce number of binlog in Mysql

To reduce the number of MySQL binary log (binlog) files and manage their size effectively, here are several strategies you can apply:

---

🔧 1. Manually Purge Old Binlogs

Inside the MySQL shell:

PURGE BINARY LOGS BEFORE '2025-08-01 00:00:00';

Or purge up to a specific file:

PURGE BINARY LOGS TO 'binlog.000142';

⚠️ Never delete binlog files manually from the filesystem—always use the PURGE command to avoid corruption.

---

🕒 2. Set Automatic Expiry

You can configure MySQL to automatically delete binlogs older than a certain number of seconds:

SET GLOBAL binlog_expire_logs_seconds = 259200;  -- 3 days
SET PERSIST binlog_expire_logs_seconds = 259200;

This replaces the older expire_logs_days setting in MySQL 8+.

---

📏 3. Limit Binlog File Size

In your MySQL config file (/etc/mysql/my.cnf or /etc/my.cnf), add:

[mysqld]
max_binlog_size = 100M

This causes MySQL to rotate to a new binlog file once the current one hits 100MB.

---

🧼 4. Disable Binary Logging (If Not Needed)

If you’re not using replication or point-in-time recovery, you can disable binlogs entirely:

[mysqld]
skip-log-bin

⚠️ This disables replication and binlog-based recovery—only do this if you’re sure you don’t need those features.

---

🔁 5. Monitor and Automate with Cron

You can create a cron job that runs a purge command periodically. Example:

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

This purges logs older than 7 days every night at 3 AM.

---