0

I have a server with Ubuntu 20.04 LTS and MySQL 8

My system drive is normally around 11gb out of the 20gb available.

For a week my database has used everything.

Why is MySQL taking up all the space on my server ?

Why the database went from 1.4gb to 14gb

ubuntu@ov-376Gf2 ~ $ sudo du -h -d 1 /var/lib/mysql
1.5M    /var/lib/mysql/performance_schema
84K /var/lib/mysql/sys
622M    /var/lib/mysql/www_s1biose_com
164K    /var/lib/mysql/#innodb_temp
36K /var/lib/mysql/mysql
14G /var/lib/mysql

and

ubuntu@ov-376Gf2 ~ $ sudo ls -larth /var/lib/mysql
total 14G
drwxr-x---  2 mysql mysql 4.0K Sep  7 17:39  performance_schema
-rw-r-----  1 mysql mysql   56 Sep  7 17:39  auto.cnf
-rw-------  1 mysql mysql 1.7K Sep  7 17:39  ca-key.pem
-rw-r--r--  1 mysql mysql 1.1K Sep  7 17:39  ca.pem
-rw-------  1 mysql mysql 1.7K Sep  7 17:39  server-key.pem
-rw-r--r--  1 mysql mysql 1.1K Sep  7 17:39  server-cert.pem
-rw-------  1 mysql mysql 1.7K Sep  7 17:39  client-key.pem
-rw-r--r--  1 mysql mysql 1.1K Sep  7 17:39  client-cert.pem
-rw-r--r--  1 mysql mysql  452 Sep  7 17:39  public_key.pem
-rw-------  1 mysql mysql 1.7K Sep  7 17:39  private_key.pem
drwxr-x---  2 mysql mysql 4.0K Sep  7 17:39  mysql
drwxr-x---  2 mysql mysql 4.0K Sep  7 17:39  sys
-rw-r--r--  1 root  root     0 Sep  7 17:39  debian-5.7.flag
-rw-r-----  1 mysql mysql  179 Sep  7 17:39  binlog.000001
-rw-r-----  1 mysql mysql  403 Sep  7 17:39  binlog.000002
drwxr-xr-x 53 root  root  4.0K Sep  7 17:42  ..
-rw-r-----  1 mysql mysql 1.2K Sep  7 17:48  binlog.000003
-rw-r-----  1 mysql mysql 3.3K Sep  7 17:48  ib_buffer_pool
drwxr-x---  2 mysql mysql 4.0K Sep  7 17:49 '#innodb_temp'
-rw-r-----  1 mysql mysql  12M Sep  7 17:49  ibtmp1
-rw-r-----  1 mysql mysql 102M Sep  7 21:44  binlog.000004
-rw-r-----  1 mysql mysql 101M Sep  7 21:44  binlog.000005
-rw-r-----  1 mysql mysql 101M Sep  7 21:47  binlog.000006
-rw-r-----  1 mysql mysql 101M Sep  7 22:11  binlog.000007
-rw-r-----  1 mysql mysql 104M Sep  8 00:34  binlog.000008
-rw-r-----  1 mysql mysql 101M Sep  8 02:49  binlog.000009
-rw-r-----  1 mysql mysql 101M Sep  8 09:45  binlog.000010
-rw-r-----  1 mysql mysql 101M Sep  8 16:25  binlog.000011
-rw-r-----  1 mysql mysql 101M Sep  8 23:00  binlog.000012
-rw-r-----  1 mysql mysql  23M Sep  9 00:00  binlog.000013
-rw-r-----  1 mysql mysql 101M Sep  9 01:45  binlog.000014
-rw-r-----  1 mysql mysql 101M Sep  9 02:13  binlog.000015
-rw-r-----  1 mysql mysql 101M Sep  9 02:39  binlog.000016
-rw-r-----  1 mysql mysql 101M Sep  9 02:47  binlog.000017
-rw-r-----  1 mysql mysql 102M Sep  9 03:10  binlog.000018
-rw-r-----  1 mysql mysql 107M Sep  9 03:18  binlog.000019
-rw-r-----  1 mysql mysql 102M Sep  9 03:30  binlog.000020
-rw-r-----  1 mysql mysql 102M Sep  9 03:42  binlog.000021
-rw-r-----  1 mysql mysql 101M Sep  9 03:52  binlog.000022
-rw-r-----  1 mysql mysql 101M Sep  9 03:52  binlog.000023
-rw-r-----  1 mysql mysql 101M Sep  9 05:51  binlog.000024
-rw-r-----  1 mysql mysql 101M Sep  9 10:43  binlog.000025
-rw-r-----  1 mysql mysql 101M Sep  9 16:00  binlog.000026
-rw-r-----  1 mysql mysql 101M Sep  9 17:33  binlog.000027
-rw-r-----  1 mysql mysql 101M Sep  9 20:15  binlog.000028
-rw-r-----  1 mysql mysql 101M Sep  9 23:20  binlog.000029
-rw-r-----  1 mysql mysql  34M Sep 10 00:00  binlog.000030
-rw-r-----  1 mysql mysql 101M Sep 10 06:25  binlog.000031
-rw-r-----  1 mysql mysql 101M Sep 10 14:30  binlog.000032
-rw-r-----  1 mysql mysql 101M Sep 10 18:20  binlog.000033
-rw-r-----  1 mysql mysql 101M Sep 10 20:59  binlog.000034
-rw-r-----  1 mysql mysql 101M Sep 10 23:06  binlog.000035
-rw-r-----  1 mysql mysql 101M Sep 10 23:37  binlog.000036
-rw-r-----  1 mysql mysql  76M Sep 11 00:00  binlog.000037
-rw-r-----  1 mysql mysql 105M Sep 11 00:04  binlog.000038
-rw-r-----  1 mysql mysql 108M Sep 11 00:16  binlog.000039
-rw-r-----  1 mysql mysql 106M Sep 11 00:19  binlog.000040
-rw-r-----  1 mysql mysql 101M Sep 11 02:33  binlog.000041
-rw-r-----  1 mysql mysql 104M Sep 11 03:36  binlog.000042
-rw-r-----  1 mysql mysql 101M Sep 11 03:42  binlog.000043
-rw-r-----  1 mysql mysql 101M Sep 11 05:14  binlog.000044
-rw-r-----  1 mysql mysql 101M Sep 11 12:11  binlog.000045
-rw-r-----  1 mysql mysql 101M Sep 11 19:36  binlog.000046
-rw-r-----  1 mysql mysql 101M Sep 11 20:18  binlog.000047
-rw-r-----  1 mysql mysql  86M Sep 12 00:00  binlog.000048
-rw-r-----  1 mysql mysql 101M Sep 12 01:49  binlog.000049
-rw-r-----  1 mysql mysql 101M Sep 12 02:15  binlog.000050
-rw-r-----  1 mysql mysql 101M Sep 12 07:00  binlog.000051
-rw-r-----  1 mysql mysql 101M Sep 12 12:15  binlog.000052
-rw-r-----  1 mysql mysql 102M Sep 12 12:17  binlog.000053
-rw-r-----  1 mysql mysql 104M Sep 12 12:38  binlog.000054
-rw-r-----  1 mysql mysql 101M Sep 12 12:44  binlog.000055
-rw-r-----  1 mysql mysql 101M Sep 12 13:28  binlog.000056
-rw-r-----  1 mysql mysql 101M Sep 12 15:08  binlog.000057
-rw-r-----  1 mysql mysql 108M Sep 12 15:27  binlog.000058
-rw-r-----  1 mysql mysql 101M Sep 12 17:50  binlog.000059
-rw-r-----  1 mysql mysql  91M Sep 13 00:00  binlog.000060
-rw-r-----  1 mysql mysql 107M Sep 13 04:18  binlog.000061
-rw-r-----  1 mysql mysql 101M Sep 13 04:19  binlog.000062
-rw-r-----  1 mysql mysql 101M Sep 13 04:45  binlog.000063
-rw-r-----  1 mysql mysql 101M Sep 13 11:30  binlog.000064
-rw-r-----  1 mysql mysql 101M Sep 13 15:08  binlog.000065
-rw-r-----  1 mysql mysql 101M Sep 13 17:45  binlog.000066
-rw-r-----  1 mysql mysql  87M Sep 14 00:00  binlog.000067
-rw-r-----  1 mysql mysql 101M Sep 14 02:24  binlog.000068
-rw-r-----  1 mysql mysql 101M Sep 14 02:26  binlog.000069
-rw-r-----  1 mysql mysql 101M Sep 14 02:39  binlog.000070
-rw-r-----  1 mysql mysql 101M Sep 14 02:55  binlog.000071
-rw-r-----  1 mysql mysql 101M Sep 14 03:09  binlog.000072
-rw-r-----  1 mysql mysql 105M Sep 14 03:14  binlog.000073
-rw-r-----  1 mysql mysql 101M Sep 14 03:20  binlog.000074
-rw-r-----  1 mysql mysql 101M Sep 14 03:42  binlog.000075
-rw-r-----  1 mysql mysql 102M Sep 14 03:44  binlog.000076
-rw-r-----  1 mysql mysql 102M Sep 14 03:48  binlog.000077
-rw-r-----  1 mysql mysql 101M Sep 14 03:49  binlog.000078
-rw-r-----  1 mysql mysql 101M Sep 14 04:28  binlog.000079
-rw-r-----  1 mysql mysql 101M Sep 14 04:32  binlog.000080
-rw-r-----  1 mysql mysql 101M Sep 14 04:40  binlog.000081
-rw-r-----  1 mysql mysql 101M Sep 14 04:44  binlog.000082
-rw-r-----  1 mysql mysql 105M Sep 14 04:45  binlog.000083
-rw-r-----  1 mysql mysql 101M Sep 14 04:48  binlog.000084
-rw-r-----  1 mysql mysql 101M Sep 14 04:54  binlog.000085
-rw-r-----  1 mysql mysql 101M Sep 14 06:00  binlog.000086
-rw-r-----  1 mysql mysql 101M Sep 14 11:31  binlog.000087
-rw-r-----  1 mysql mysql 105M Sep 14 13:56  binlog.000088
-rw-r-----  1 mysql mysql 104M Sep 14 13:58  binlog.000089
-rw-r-----  1 mysql mysql 105M Sep 14 13:59  binlog.000090
-rw-r-----  1 mysql mysql 103M Sep 14 14:03  binlog.000091
-rw-r-----  1 mysql mysql 101M Sep 14 14:11  binlog.000092
-rw-r-----  1 mysql mysql 104M Sep 14 14:28  binlog.000093
-rw-r-----  1 mysql mysql 101M Sep 14 15:07  binlog.000094
-rw-r-----  1 mysql mysql 101M Sep 14 16:13  binlog.000095
-rw-r-----  1 mysql mysql 101M Sep 14 17:39  binlog.000096
-rw-r-----  1 mysql mysql 103M Sep 14 17:54  binlog.000097
-rw-r-----  1 mysql mysql 101M Sep 14 17:55  binlog.000098
-rw-r-----  1 mysql mysql 101M Sep 14 18:30  binlog.000099
-rw-r-----  1 mysql mysql 101M Sep 14 23:16  binlog.000100
-rw-r-----  1 mysql mysql  14M Sep 15 00:00  binlog.000101
-rw-r-----  1 mysql mysql 101M Sep 15 03:56  binlog.000102
-rw-r-----  1 mysql mysql 101M Sep 15 06:00  binlog.000103
-rw-r-----  1 mysql mysql 101M Sep 15 13:15  binlog.000104
-rw-r-----  1 mysql mysql 101M Sep 15 20:00  binlog.000105
-rw-r-----  1 mysql mysql  86M Sep 16 00:00  binlog.000106
-rw-r-----  1 mysql mysql 101M Sep 16 02:33  binlog.000107
-rw-r-----  1 mysql mysql 101M Sep 16 03:23  binlog.000108
-rw-r-----  1 mysql mysql 107M Sep 16 06:54  binlog.000109
-rw-r-----  1 mysql mysql 101M Sep 16 08:51  binlog.000110
-rw-r-----  1 mysql mysql 101M Sep 16 12:24  binlog.000111
-rw-r-----  1 mysql mysql 101M Sep 16 16:27  binlog.000112
-rw-r-----  1 mysql mysql 101M Sep 16 17:04  binlog.000113
-rw-r-----  1 mysql mysql 101M Sep 16 18:53  binlog.000114
-rw-r-----  1 mysql mysql 101M Sep 16 23:18  binlog.000115
-rw-r-----  1 mysql mysql  28M Sep 17 00:00  binlog.000116
-rw-r-----  1 mysql mysql 101M Sep 17 06:15  binlog.000117
-rw-r-----  1 mysql mysql 101M Sep 17 12:30  binlog.000118
-rw-r-----  1 mysql mysql 101M Sep 17 18:15  binlog.000119
-rw-r-----  1 mysql mysql  78M Sep 18 00:00  binlog.000120
-rw-r-----  1 mysql mysql 101M Sep 18 06:00  binlog.000121
-rw-r-----  1 mysql mysql 101M Sep 18 13:45  binlog.000122
-rw-r-----  1 mysql mysql 101M Sep 18 14:23  binlog.000123
-rw-r-----  1 mysql mysql 101M Sep 18 19:57  binlog.000124
-rw-r-----  1 mysql mysql  51M Sep 19 00:00  binlog.000125
-rw-r-----  1 mysql mysql 101M Sep 19 08:21  binlog.000126
-rw-r-----  1 mysql mysql 101M Sep 19 17:00  binlog.000127
-rw-r-----  1 mysql mysql  70M Sep 20 00:00  binlog.000128
-rw-r-----  1 mysql mysql 101M Sep 20 10:15  binlog.000129
-rw-r-----  1 mysql mysql 101M Sep 20 22:00  binlog.000130
-rw-r-----  1 mysql mysql 8.2M Sep 20 23:53 '#ib_16384_1.dblwr'
-rw-r-----  1 mysql mysql  86M Sep 21 00:00  binlog.000131
-rw-r-----  1 mysql mysql 105M Sep 21 01:58  binlog.000132
-rw-r-----  1 mysql mysql 101M Sep 21 02:04  binlog.000133
-rw-r-----  1 mysql mysql 101M Sep 21 03:58  binlog.000134
-rw-r-----  1 mysql mysql 101M Sep 21 06:15  binlog.000135
-rw-r-----  1 mysql mysql 101M Sep 21 13:53  binlog.000136
drwxr-x---  2 mysql mysql  52K Sep 21 14:56  www_s1biose_com
-rw-r-----  1 mysql mysql 104M Sep 21 15:41  binlog.000137
-rw-r-----  1 mysql mysql 101M Sep 21 20:59  binlog.000138
-rw-r-----  1 mysql mysql  49M Sep 22 00:00  binlog.000139
-rw-r-----  1 mysql mysql 101M Sep 22 07:00  binlog.000140
-rw-r-----  1 mysql mysql 101M Sep 22 11:15  binlog.000141
-rw-r-----  1 mysql mysql 2.3K Sep 22 11:15  binlog.index
drwx------  7 mysql mysql 4.0K Sep 22 11:15  .
-rw-r-----  1 mysql mysql  36M Sep 22 15:13  mysql.ibd
-rw-r-----  1 mysql mysql  79M Sep 22 15:13  binlog.000142
-rw-r-----  1 mysql mysql  13M Sep 22 15:13  undo_002
-rw-r-----  1 mysql mysql  15M Sep 22 15:13  undo_001
-rw-r-----  1 mysql mysql  48M Sep 22 15:13  ib_logfile1
-rw-r-----  1 mysql mysql  12M Sep 22 15:13  ibdata1
-rw-r-----  1 mysql mysql 192K Sep 22 15:13 '#ib_16384_0.dblwr'
-rw-r-----  1 mysql mysql  48M Sep 22 15:13  ib_logfile0
20f2c98f50
  • 39
  • 2
  • 11
  • why do you try to do an ls -larth /var/lib/mysql and check which files are growing? – c4f4t0r Sep 22 '20 at 13:02
  • @c4f4t0r Thanks, I updated my question but I don't know what to do – 20f2c98f50 Sep 22 '20 at 13:15
  • Is this database used? In my enterprise, some databases weight over 1 terabyte, so gaining 10Go on a frequently used database doesn't surprise me. – LeRouteur Sep 22 '20 at 13:16
  • @LeRouteur Yes for a small drupal 8 site before updating to MySQL 8 I did not have this problem. What are these binlog files ? – 20f2c98f50 Sep 22 '20 at 13:22
  • Do you have lots of write queries? Or do they modify lots of rows? There may be some optimizations in those areas. – Rick James Sep 22 '20 at 21:44

1 Answers1

3

Those are binary transaction logs. You can purge them, but you should actually read into their purpose and make an informed decision about how many you should keep, based on the criticality of your data. You can control a lot of their behavior in the configuration options.

Satanicpuppy
  • 5,946
  • 1
  • 17
  • 18
  • I only have a database on a single server. I am not replicating. Are these files useful ? – 20f2c98f50 Sep 22 '20 at 13:29
  • @20f2c98f50 Probably not, but transaction logs are useful for recovering data between backups, so you should sync your purges with your backup strategy. – Satanicpuppy Sep 22 '20 at 13:34
  • For backups, I just do a command line to export the database – 20f2c98f50 Sep 22 '20 at 13:40
  • @20f2c98f50 The point is that the transaction logs could provide more granular restore points. – Håkan Lindqvist Sep 22 '20 at 13:45
  • 3
    "For backups, I just do a command line to export the database" - that is NOT a backup. Backups should always go to offline separate systems without direct copy functionality. Ask anyone who lost data because every reachable data was encrypted by ransomware. – TomTom Sep 22 '20 at 13:47
  • Exactly. With a binary tx log you can restore down to the last transaction - hence they should alyways at least be on a separate drive. They contain the changes since the last backup. – TomTom Sep 22 '20 at 13:48
  • @TomTom I save my database exports with this https://www.infomaniak.com/en/swiss-backup How can I reduce the duration of the logs? From 30 days by default to 7 days ? – 20f2c98f50 Sep 22 '20 at 14:28
  • @Satanicpuppy I save my database exports with this infomaniak.com/en/swiss-backup How can I reduce the duration of the logs? From 30 days by default to 7 days ? – 20f2c98f50 Sep 22 '20 at 16:50
  • Specifically, see `expire_logs_days` and `binlog_expire_logs_seconds`. – Rick James Sep 22 '20 at 21:43
  • @Rick James Ok thank you, I have updated 1 day. Will the current files be purged automatically? – 20f2c98f50 Sep 22 '20 at 22:29
  • @20f2c98f50 - I think so, but perhaps only when it is ready to build a new binlog -- which seems to happen every few hours. – Rick James Sep 22 '20 at 23:53