Jul 262016

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
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.