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
Best Answer
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.
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.