How to Purge the BinLog for MySQL and MariaDB

bossadmin September 3, 2023

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:

usr@server-1:/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:

usr@server-1:/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