Limit disk used by MySQL binary log files

You can use following config to keep 7 days bin log

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M

After modifying the configuration file, you will need to restart the MySQL server to apply the changes:

systemctl restart mysql

You can also manually delete old binary log files to free up disk space:

PURGE BINARY LOGS BEFORE '2024-02-01 00:00:00';

Replace ‘2024-02-01 00:00:00’ with the date before which you want to delete the binary log files.

On a server, the disk was almost full. On checking most of the disk space is used in “/var/lib/mysql” folder, which had many MySQL binary logs.

In the MySQL command prompt, run the command to see all MySQL binary logs

SHOW BINARY LOGS;

On checking MySQL configuration, I had the line

max_binlog_size   = 100M

This limited the binlog file size to 100 MB, when the file size reached 100 MB, the file get rotated.

How long logs are kept is determined by the value of binlog_expire_logs_seconds. The default value for this variable was 2592000, which is approx 30 days.

To make the MySQL bin log expire after 48 hours, add the following to the MySQL configuration file under the [mysqld] directive.

[mysqld]
binlog_expire_logs_seconds=86400

Restart the MySQL server with

systemctl restart mysql

On RHEL based servers

systemctl restart mysqld

After restarting I checked the disk space used by folder “/var/lib/mysql”, it changed to 13 GB, which is much lower than the initial MySQL disk usage.

Disable MySQL binlog

In my.cnf, add

[mysqld]
skip-log-bin

Restart the MySQL server

Back to MySQL

Need help with Linux Server or WordPress? We can help!

Leave a Reply

Your email address will not be published. Required fields are marked *