Managing MySQL Binary Logs for Improved Database Efficiency

MySQL Binary Logs are logs that keep track of all changes made to a database, including INSERT, UPDATE, DELETE statements and database structure modifications. They are binary files that store the events in a compact, efficient and consistent format. The binary logs serve several purposes, including replication, backup, and disaster recovery.

Purging MySQL Binary Logs

Automatically using the expire_logs_days  or binlog_expire_logs_seconds system variables.

mysql> SET GLOBAL expire_logs_days = 3;
Query OK, 0 rows affected (0.00 sec)

This method allows you to set the number of days after which binary logs should be automatically purged. The default value is 0, which means binary logs will never be purged automatically.

mysql> SET GLOBAL binlog_expire_logs_seconds = 259200;
Query OK, 0 rows affected (0.00 sec)

mysql> SET PERSIST binlog_expire_logs_seconds = 259200;
Query OK, 0 rows affected (0.01 sec)

And this allows you to set expiry by seconds ( 60*60*24*3 = 259200 ). The default is 2592000 which means 30days.       

Manually using the PURGE BINARY LOGS command: 

This method allows you to manually delete binary logs older than a specific binary log file. For example, to purge binary logs older than the file mysql-bin.0009, you can run the following command:

mysql> PURGE BINARY LOGS TO 'mysql-bin.0009';

And this method will purge until some date:

mysql> PURGE BINARY LOGS BEFORE '2023-02-06 00:01:00';

To show all binary logs you can run following command:

mysql> SHOW BINARY LOGS;

check current value of binlog_expire_logs_seconds:

mysql> show global variables like 'binlog_expire_logs_seconds';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
+----------------------------+---------+
2592000 seconds = 30days

IMPORTANT Note: Do not manually delete files from the file system. It will break the MySQL system and will complain at some time in the future. Save yourself the unnecessary downtime by using  PURGE BINARY LOGS  command from within MySQL.