Note that I have replication ON
I had a problem where my websites were becoming excessively slow/unresponsive. Eventually saw that it was due to the root partition filing up.
df -h du -sh / | sort -h
and the culprit was MariaDB and its logging.
ls -la /var/lib/mysql ... 1073741824 Jul 25 16:01 mariadb-bin.000026 1073741824 Jul 25 18:43 mariadb-bin.000027 1073741824 Jul 25 20:12 mariadb-bin.000028 877809702 Jul 25 21:56 mariadb-bin.000029
First thing, clean up some disk space by purging the older, and larger, files. Don’t just delete files. If there is still a process writing to them, deleting can potentially cause all sorts of stability problems.
mysql -u root -p PURGE BINARY LOGS TO 'mariadb-bin.000030';
Phew! My sites are back to normal. However, I need to find out what is causing the log files to be so large.
SHOW BINLOG EVENTS IN 'mariadb-bin.000030';
In my case, I saw that there was a particular database that was causing the issue. The logging was out of control. Since I don’t really care about those log messages for that database, I will just stop logging.
Add the binlog-ignore-db line to your /etc/my.cnf
vim /etc/my.cnf [mysqld] binlog-ignore-db=TroubleMakingDatabase
service mariadb restart
Alternatively, you could set the number of days to keep logs for. I didn’t actually do this since a single day could fill my root partition so was not really much of a solution. Here it is anyway.
mysql -u root -p SET GLOBAL expire_logs_days = 3;
I also understand that I could make sure that logging was done to another, much larger partition. That would not have been a solution for me either since the particular log messages were not the sort of thing I care about.