Skip to content

How to Purge the BinLog for MySQL and MariaDB

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
Code language: Bash (bash)

I looked to see how much space this was all taking:

[email protected]:/var/log/mysql# du -h 5.4G .
Code language: Bash (bash)

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';
Code language: SQL (Structured Query Language) (sql)

Or before a certain date:

Code language: SQL (Structured Query Language) (sql)

Or before an exact time and date:

PURGE BINARY LOGS BEFORE '2020-03-26 08:45:13';
Code language: SQL (Structured Query Language) (sql)

Make the Binary Logs go away forever

It’s also possible to remove the bin logs for good.

See also  How to Delete from a Table where rows match in Another Table - MySQL

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
Code language: Bash (bash)

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
Code language: Bash (bash)

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
Code language: Bash (bash)

Notify of
Inline Feedbacks
View all comments
Would love your thoughts, please comment.x