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