7

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
0

2 Answers 2

4

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.

5
  • Thank you for these explanations. Could this command damage the database? Is there a particular reason for keeping 30 days of logs?
    – ML61
    Mar 25, 2019 at 17:15
  • Where is the file to configure the expiration on ubuntu 18.04 ?
    – ML61
    Mar 25, 2019 at 17:28
  • my.cnf is the configuration file. To find its location visit this link : stackoverflow.com/questions/2482234/… Mar 25, 2019 at 18:24
  • If the server has no replication associated with it, then it is not at all dangerous. Mar 25, 2019 at 18:25
  • I opened another question because I do not know how to configure the expiration dba.stackexchange.com/questions/233059/…
    – ML61
    Mar 25, 2019 at 18:39
9

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.