Categories
Uncategorized

How to purge MySQL 8 binary logs

You saw, that your mysql disk, using /var/lib/mysql is full. So, you need to know how to free some space and clean up the disk. Usually, you can safely remove the old binlogs.

First of all, you should login to MySQL server and check if there are some binlog files. If you see something like this, they are:

sh-4.2$ ls -la /var/lib/mysql | grep binlog
-rw-rw----. 1 1001230000 1001230000    2959368 Nov  5 04:56 binlog.000001
-rw-rw----. 1 1001230000 1001230000 1074256888 Nov  5 05:04 binlog.000002
-rw-rw----. 1 1001230000 1001230000  623916528 Nov  5 05:12 binlog.000003
-rw-rw----. 1 1001230000 1001230000   16797561 Nov  5 12:57 binlog.000004
-rw-rw----. 1 1001230000 1001230000  385788866 Nov  9 12:38 binlog.000005
-rw-rw----. 1 1001230000 1001230000  484322047 Nov  9 15:16 binlog.000006
-rw-rw----. 1 1001230000 1001230000  424167115 Nov 10 14:04 binlog.000007
-rw-rw----. 1 1001230000 1001230000   58027474 Nov 10 20:00 binlog.000008
-rw-rw----. 1 1001230000 1001230000   13217008 Nov 11 20:00 binlog.000009
-rw-rw----. 1 1001230000 1001230000        179 Nov 11 21:31 binlog.000010
-rw-rw----. 1 1001230000 1001230000   13285404 Nov 12 20:00 binlog.000011
-rw-rw----. 1 1001230000 1001230000   13241773 Nov 13 20:00 binlog.000012
-rw-rw----. 1 1001230000 1001230000      34779 Nov 16 20:00 binlog.000013
-rw-rw----. 1 1001230000 1001230000  359932796 Nov 17 20:00 binlog.000014
-rw-rw----. 1 1001230000 1001230000   22423233 Nov 18 20:00 binlog.000015
-rw-rw----. 1 1001230000 1001230000   86190929 Nov 19 20:00 binlog.000016
-rw-rw----. 1 1001230000 1001230000   39029607 Nov 20 20:00 binlog.000017
-rw-rw----. 1 1001230000 1001230000  101916333 Nov 23 20:00 binlog.000018
-rw-rw----. 1 1001230000 1001230000   32958639 Nov 24 20:00 binlog.000019
-rw-rw----. 1 1001230000 1001230000   40808525 Nov 25 20:00 binlog.000020
-rw-rw----. 1 1001230000 1001230000   40866887 Nov 26 20:00 binlog.000021
-rw-r-----. 1 1001230000 1001230000        156 Dec  1 08:37 binlog.000022
-rw-r-----. 1 1001230000 1001230000        352 Dec  1 08:37 binlog.index

By default, MySQL rotates binlogs and stores them for 30 days. You can reduce this parameter by setting up this parameter at mysqld.cnf:

binlog_expire_logs_seconds = 432000

The value is in seconds, so 432000 means 5 days. You should restart MySQL to apply the settings.

After that you can cleanup old logs. Do not delete files, but login to your MySQL server and execute this query to remove all binlogs:

PURGE BINARY LOGS BEFORE NOW();

All the logs will be purged. As you can see, you can insert your own date here to cleanup not all binlogs, but before a certain date.

Leave a Reply

Your email address will not be published. Required fields are marked *