mysql-bin log files take a lot of space
- Logs |
- MySQL |
- Disk space
This is a wiki page. Be bold and improve it!
If you have any questions about the content on this page, don't hesitate to open a new ticket and we'll do our best to assist you.
Symptom and cause
Symptom: the directory /var/lib/mysql/ is taking an increasing amount of space because of the numerous “server-bin.n” or mysql-bin.00000n files stored there.
This is because /etc/mysql/my.cnf has:
Binary logging is used for data recovery and for replication with master/slave servers.
To clear those binary log files, you must first decide whether you want/need binary logging or not.
Disable binary logging
disable log-bin and related settings as below (comment out or delete):
although, from MySQL 8.0, binary logging is enabled by default.
Keep binary logging and purge old binary log files
If you want to keep binary logging, you need to adjust your settings and do some cleanup:
In /etc/mysql/my.cnf, set:
binlog_expire_logs_seconds = 432000 # 5 days = 3600 * 24 * 5
max_binlog_size = 100M
You may restart the mysql server, which will automatically purge older files as per your new settings:
# service mysql restart
or issue the following commands within mysql itself:
SQL > SHOW BINARY LOGS;
// Purge to a certain file number:
mysql> PURGE BINARY LOGS TO `mysql-bin.000123`;
// Or purge files older than 10 days:
mysql> PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 10 DAY) + INTERVAL 0 SECOND;
mysql> SET GLOBAL expire_logs_days = 10;
Official documentation references:
MySQL 8.0 Reference Manual / The Binary Log / 5.4.4 The Binary Log
126.96.36.199 Binary Logging Options and Variables