My disk is full of binlog files

Question

I just realized that my server is growing by 2 GB per day, which is not normal.

I looked at my folder and the folder / var / lib / mysql reached 26 GB

Normally it should be about 4 GB

A month ago I migrated my MySQL 5.7 database to MySQL 8.0

I think my disk is full of binlog files.

I would like to know why these files suddenly get bigger ?

How can I fix this without damaging my database ?

total 26511376
-rw-r----- 1 mysql mysql         56 Aug 19  2018  auto.cnf
-rw-r----- 1 mysql mysql        178 Mar 10 06:07  binlog.000001
-rw-r----- 1 mysql mysql   73883481 Mar 10 06:25  binlog.000002
-rw-r----- 1 mysql mysql   58601919 Mar 10 13:13  binlog.000003
-rw-r----- 1 mysql mysql  178322334 Mar 11 06:25  binlog.000004
-rw-r----- 1 mysql mysql  379019579 Mar 12 06:25  binlog.000005
-rw-r----- 1 mysql mysql 1075214425 Mar 13 02:10  binlog.000006
-rw-r----- 1 mysql mysql  710899072 Mar 13 06:25  binlog.000007
-rw-r----- 1 mysql mysql 1073746776 Mar 13 20:15  binlog.000008
-rw-r----- 1 mysql mysql  616483095 Mar 14 06:25  binlog.000009
-rw-r----- 1 mysql mysql 1073836859 Mar 15 00:38  binlog.000010
-rw-r----- 1 mysql mysql 1074301774 Mar 15 03:52  binlog.000011
-rw-r----- 1 mysql mysql 1073936970 Mar 15 04:10  binlog.000012
-rw-r----- 1 mysql mysql 1073793229 Mar 15 04:43  binlog.000013
-rw-r----- 1 mysql mysql  324536092 Mar 15 06:25  binlog.000014
-rw-r----- 1 mysql mysql 1074204920 Mar 15 11:00  binlog.000015
-rw-r----- 1 mysql mysql 1074309915 Mar 16 01:10  binlog.000016
-rw-r----- 1 mysql mysql  323166533 Mar 16 06:25  binlog.000017
-rw-r----- 1 mysql mysql  290236337 Mar 16 20:46  binlog.000018
-rw-r----- 1 mysql mysql  236901439 Mar 17 06:25  binlog.000019
-rw-r----- 1 mysql mysql  480168672 Mar 18 06:25  binlog.000020
-rw-r----- 1 mysql mysql  709223503 Mar 19 06:25  binlog.000021
-rw-r----- 1 mysql mysql 1074813984 Mar 19 17:17  binlog.000022
-rw-r----- 1 mysql mysql 1073759979 Mar 20 04:20  binlog.000023
-rw-r----- 1 mysql mysql  272250245 Mar 20 06:25  binlog.000024
-rw-r----- 1 mysql mysql 1073741888 Mar 20 10:48  binlog.000025
-rw-r----- 1 mysql mysql 1075360653 Mar 20 16:33  binlog.000026
-rw-r----- 1 mysql mysql 1073743148 Mar 21 05:25  binlog.000027
-rw-r----- 1 mysql mysql  540261003 Mar 21 06:25  binlog.000028
-rw-r----- 1 mysql mysql  824878670 Mar 22 06:25  binlog.000029
-rw-r----- 1 mysql mysql  648554142 Mar 23 06:25  binlog.000030
-rw-r----- 1 mysql mysql 1015179916 Mar 24 06:25  binlog.000031
-rw-r----- 1 mysql mysql 1073772614 Mar 24 15:00  binlog.000032
-rw-r----- 1 mysql mysql 1073750944 Mar 25 00:09  binlog.000033
-rw-r----- 1 mysql mysql 1074307838 Mar 25 01:18  binlog.000034
-rw-r----- 1 mysql mysql   49166468 Mar 25 06:25  binlog.000035
-rw-r----- 1 mysql mysql 1074699799 Mar 25 09:13  binlog.000036
-rw-r----- 1 mysql mysql  896120152 Mar 25 16:15  binlog.000037
-rw-r----- 1 mysql mysql      11512 Mar 25 16:21  binlog.000038
-rw-r----- 1 mysql mysql      83159 Mar 25 16:40  binlog.000039
-rw-r----- 1 mysql mysql        624 Mar 25 16:21  binlog.index
-rw------- 1 mysql mysql       1679 Mar 10 05:58  ca-key.pem
-rw-r--r-- 1 mysql mysql       1107 Mar 10 05:58  ca.pem
-rw-r--r-- 1 mysql mysql       1107 Mar 10 05:58  client-cert.pem
-rw------- 1 mysql mysql       1675 Mar 10 05:58  client-key.pem
-rw-r--r-- 1 mysql mysql          0 Jan 24 07:58  debian-5.7.flag
-rw-r----- 1 mysql mysql      18030 Mar 25 16:15  ib_buffer_pool
-rw-r----- 1 mysql mysql   79691776 Mar 25 16:40  ibdata1
-rw-r----- 1 mysql mysql   50331648 Mar 25 16:40  ib_logfile0
-rw-r----- 1 mysql mysql   50331648 Mar 25 15:25  ib_logfile1
-rw-r----- 1 mysql mysql   12582912 Mar 25 16:21  ibtmp1
drwxr-x--- 2 mysql mysql       4096 Mar 25 16:21 '#innodb_temp'
drwxr-x--- 2 mysql mysql       4096 Mar 10 06:05  mysql
-rw-r----- 1 mysql mysql   37748736 Mar 25 16:40  mysql.ibd
-rw-r--r-- 1 mysql mysql          6 Mar 10 06:05  mysql_upgrade_info
drwxr-x--- 2 mysql mysql       4096 Mar 10 06:04  performance_schema
drwxr-x--- 2 mysql mysql       4096 Mar 10 06:04  phpmyadmin
-rw------- 1 mysql mysql       1675 Mar 10 05:58  private_key.pem
-rw-r--r-- 1 mysql mysql        451 Mar 10 05:58  public_key.pem
-rw-r--r-- 1 mysql mysql       1107 Mar 10 05:58  server-cert.pem
-rw------- 1 mysql mysql       1679 Mar 10 05:58  server-key.pem
drwxr-x--- 2 mysql mysql      12288 Mar 10 06:04  sys
-rw-r----- 1 mysql mysql   13631488 Mar 25 16:40  undo_001
-rw-r----- 1 mysql mysql   13631488 Mar 25 16:40  undo_002
drwxr-x--- 2 mysql mysql     110592 Mar 25 15:25  www_example_com

Answer 1

Try using the PURGE command to delete the binary logs. For reference : documentation

Also update the following variable binlog_expire_logs_seconds to ensure that the binary logs are automatically deleted.

I would like to know why these files suddenly get bigger ?

One possible reason is that till MySQL 5.7 the default value for expire_logs_days is 10 days. But from MySQL 8.0.11 the default value for binlog_expire_logs_seconds is 30 days. So the log files are not being rotated that regularly. Please correct me if I am wrong.

Answer 2

With MySQL8, they have turned on binary logging by default and the default purge (expiry/deletion) of binary logs is set to 30days.

Once you are in your SSH and in mysql, you can use the below commands

To show binary logs

mysql> SHOW BINARY LOGS;

To Purge binary logs manually until some point

mysql> PURGE BINARY LOGS TO 'binlog.000142';

Change automatic default purge expiry from 30days (deafault) to 3days

mysql> SET GLOBAL binlog_expire_logs_seconds = (60*60*24*3);
Query OK, 0 rows affected (0.00 sec)

mysql> SET PERSIST binlog_expire_logs_seconds = (60*60*24*3);
Query OK, 0 rows affected (0.01 sec)

The above value is in seconds, i.e. 3 days in seconds = (60 seconds x 60 minutes x 24 hours x 3 days)