mysql-bin log files take a lot of 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):
#expire_logs_days = 10
#max_binlog_size = 100M
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:
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:
// 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 5.7 Reference Manual / The Binary Log / 5.4.4 The Binary Log
MySQL 5.7 Reference Manual / Binary Logging Options and Variables / 18.104.22.168 Binary Logging Options and Variables