If you see lots of disk space quickly disappearing on your MySQL or MariaDB server, then you may want to look into the BinLog directory, it is located at /var/log/mysql/.
In my case I saw this:
[email protected]:/var/log/mysql# ll
total 5571044
drwxr-s--- 2 x x 4096 Mar 25 18:18 ./
drwxrwxr-x 12 x x 4096 Mar 25 05:25 ../
-rw-rw---- 1 x x 104857748 Mar 23 09:29 mariadb-bin.000301
-rw-rw---- 1 x x 104859422 Mar 23 10:07 mariadb-bin.000302
-rw-rw---- 1 x x 104857708 Mar 23 11:10 mariadb-bin.000303
-rw-rw---- 1 x x 104860750 Mar 23 12:29 mariadb-bin.000304
...truncated...
-rw-rw---- 1 x x 104857809 Mar 25 16:46 mariadb-bin.000352
-rw-rw---- 1 x x 104858231 Mar 25 17:17 mariadb-bin.000353
-rw-rw---- 1 x x 104857709 Mar 25 18:18 mariadb-bin.000354
-rw-rw---- 1 x x 24833125 Mar 25 18:32 mariadb-bin.000355
-rw-rw---- 1 x x 1870 Mar 25 18:18 mariadb-bin.index
I looked to see how much space this was all taking:
[email protected]:/var/log/mysql# du -h
5.4G .
Almost 5.5Gb in this instance. I have seen servers go up into the many hundreds of Gigabytes before anyone notices.
I am not replicating logs to a cluster, or any slave nodes, so this is only taking up space, with no value add.
I can safely delete this, but how do I go about doing that?
How to safely purge Binary Logs
You can clear a specific log:
PURGE BINARY LOGS TO 'mariadb-bin.000301';
Or before a certain date:
PURGE BINARY LOGS BEFORE '2020-03-26';
Or before an exact time and date:
PURGE BINARY LOGS BEFORE '2020-03-26 08:45:13';
Make the Binary Logs go away forever
It’s also possible to remove the bin logs for good.
Open the /etc/mysql/my.cnf file on the server and look for the section:
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
sync_binlog = 1
expire_logs_days = 10
max_binlog_size = 100M
Now we can either comment these lines out and restart the MySQL/MariaDB server or adjust the values to expire quicker.
The commented out version looks like this:
#log_bin = /var/log/mysql/mariadb-bin
#log_bin_index = /var/log/mysql/mariadb-bin.index
#sync_binlog = 1
#expire_logs_days = 10
#max_binlog_size = 100M
Completely disable Binary Logs to disk
Within your /etc/mysql/my.cnf file, you can add a line under the [mysqld] section:
[mysqld]
skip-log-bin