Limit disk used by MySQL binary log files
You can use following config to keep 7 days bin log
|
1 2 3 4 5 |
[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:
|
1 |
systemctl restart mysql |
You can also manually delete old binary log files to free up disk space:
|
1 |
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
|
1 |
SHOW BINARY LOGS; |
On checking MySQL configuration, I had the line
|
1 |
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.
|
1 2 |
[mysqld] binlog_expire_logs_seconds=86400 |
Restart the MySQL server with
|
1 |
systemctl restart mysql |
On RHEL based servers
|
1 |
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
|
1 2 |
[mysqld] skip-log-bin |
Restart the MySQL server
Back to MySQL