Able to clean up binlog.###### files in /var/lib/mysql directory?

[d19dotca]

I noticed my disk space running out quicker than usual, and when digging into the disk space I see 12 GB used in /var/lib/mysql. To be fair, I'm not sure what it's usage was before, so maybe it's always been around there but I don't recall that being the case.

I see about 129 files almost all around 101 MB in size each (some less than 20 MB though) which is basically what's consuming the full amount of the 12 GB in /var/lib/mysql directory.

Many of the files are days old too. The 13th is when I had migrated my server by restoring to a new one in a different datacentre, so it seems like no binlogs have been deleted since then yet, leaving me to wonder if it's being maintained properly. This is on Ubuntu 20.04 if it matters.

Here's a snippet of the binlog files in an ls -alh listing of /var/lib/mysql/:

-rw-r-----  1 mysql mysql  179 Feb 13 05:58  binlog.000001
-rw-r-----  1 mysql mysql  179 Feb 13 06:02  binlog.000002
-rw-r-----  1 mysql mysql 116K Feb 13 06:03  binlog.000003
-rw-r-----  1 mysql mysql  554 Feb 13 08:12  binlog.000004
-rw-r-----  1 mysql mysql  12M Feb 13 08:30  binlog.000005
-rw-r-----  1 mysql mysql  15M Feb 13 08:58  binlog.000006
-rw-r-----  1 mysql mysql 101M Feb 13 10:33  binlog.000007
-rw-r-----  1 mysql mysql 101M Feb 13 12:05  binlog.000008
-rw-r-----  1 mysql mysql 101M Feb 13 13:37  binlog.000009
-rw-r-----  1 mysql mysql 101M Feb 13 15:09  binlog.000010
-rw-r-----  1 mysql mysql 101M Feb 13 16:40  binlog.000011
-rw-r-----  1 mysql mysql 101M Feb 13 18:11  binlog.000012
-rw-r-----  1 mysql mysql 101M Feb 13 19:43  binlog.000013
-rw-r-----  1 mysql mysql 101M Feb 13 21:14  binlog.000014
-rw-r-----  1 mysql mysql 101M Feb 13 22:45  binlog.000015
-rw-r-----  1 mysql mysql 101M Feb 14 00:17  binlog.000016
-rw-r-----  1 mysql mysql 101M Feb 14 01:49  binlog.000017
-rw-r-----  1 mysql mysql 101M Feb 14 03:20  binlog.000018
-rw-r-----  1 mysql mysql 101M Feb 14 04:52  binlog.000019
-rw-r-----  1 mysql mysql 101M Feb 14 06:24  binlog.000020
-rw-r-----  1 mysql mysql 101M Feb 14 07:56  binlog.000021
-rw-r-----  1 mysql mysql 101M Feb 14 09:28  binlog.000022
-rw-r-----  1 mysql mysql 101M Feb 14 10:59  binlog.000023
-rw-r-----  1 mysql mysql 101M Feb 14 12:31  binlog.000024
-rw-r-----  1 mysql mysql 101M Feb 14 14:03  binlog.000025
-rw-r-----  1 mysql mysql 101M Feb 14 15:35  binlog.000026
-rw-r-----  1 mysql mysql 101M Feb 14 17:06  binlog.000027
-rw-r-----  1 mysql mysql 101M Feb 14 18:38  binlog.000028
-rw-r-----  1 mysql mysql 101M Feb 14 20:09  binlog.000029
-rw-r-----  1 mysql mysql 101M Feb 14 21:40  binlog.000030
-rw-r-----  1 mysql mysql 101M Feb 14 23:14  binlog.000031
-rw-r-----  1 mysql mysql  49M Feb 15 00:00  binlog.000032
-rw-r-----  1 mysql mysql 101M Feb 15 01:35  binlog.000033
-rw-r-----  1 mysql mysql 101M Feb 15 03:11  binlog.000034
-rw-r-----  1 mysql mysql 101M Feb 15 04:46  binlog.000035
-rw-r-----  1 mysql mysql  90M Feb 15 06:11  binlog.000036
-rw-r-----  1 mysql mysql 101M Feb 15 07:54  binlog.000037
-rw-r-----  1 mysql mysql 101M Feb 15 09:30  binlog.000038
-rw-r-----  1 mysql mysql 101M Feb 15 11:05  binlog.000039
-rw-r-----  1 mysql mysql 101M Feb 15 12:41  binlog.000040
-rw-r-----  1 mysql mysql 101M Feb 15 14:16  binlog.000041
-rw-r-----  1 mysql mysql 101M Feb 15 15:51  binlog.000042
-rw-r-----  1 mysql mysql 101M Feb 15 17:26  binlog.000043
-rw-r-----  1 mysql mysql 101M Feb 15 19:01  binlog.000044
-rw-r-----  1 mysql mysql 101M Feb 15 20:37  binlog.000045
-rw-r-----  1 mysql mysql 101M Feb 15 22:12  binlog.000046
-rw-r-----  1 mysql mysql 101M Feb 15 23:46  binlog.000047
-rw-r-----  1 mysql mysql  15M Feb 16 00:00  binlog.000048
-rw-r-----  1 mysql mysql 101M Feb 16 01:34  binlog.000049
-rw-r-----  1 mysql mysql 101M Feb 16 03:09  binlog.000050
-rw-r-----  1 mysql mysql 101M Feb 16 04:44  binlog.000051
-rw-r-----  1 mysql mysql 101M Feb 16 06:19  binlog.000052
-rw-r-----  1 mysql mysql 101M Feb 16 07:54  binlog.000053
-rw-r-----  1 mysql mysql 101M Feb 16 09:29  binlog.000054
-rw-r-----  1 mysql mysql 101M Feb 16 11:04  binlog.000055
-rw-r-----  1 mysql mysql 101M Feb 16 12:39  binlog.000056
-rw-r-----  1 mysql mysql 101M Feb 16 14:14  binlog.000057
-rw-r-----  1 mysql mysql 101M Feb 16 15:49  binlog.000058
-rw-r-----  1 mysql mysql 101M Feb 16 17:24  binlog.000059
-rw-r-----  1 mysql mysql 101M Feb 16 18:59  binlog.000060
-rw-r-----  1 mysql mysql 101M Feb 16 20:34  binlog.000061
-rw-r-----  1 mysql mysql 101M Feb 16 22:08  binlog.000062
-rw-r-----  1 mysql mysql 101M Feb 16 23:43  binlog.000063
-rw-r-----  1 mysql mysql  18M Feb 17 00:00  binlog.000064
-rw-r-----  1 mysql mysql 101M Feb 17 01:35  binlog.000065
-rw-r-----  1 mysql mysql 101M Feb 17 03:12  binlog.000066
-rw-r-----  1 mysql mysql 101M Feb 17 04:47  binlog.000067
-rw-r-----  1 mysql mysql 101M Feb 17 06:14  binlog.000068
-rw-r-----  1 mysql mysql 101M Feb 17 07:39  binlog.000069
-rw-r-----  1 mysql mysql 101M Feb 17 09:05  binlog.000070
-rw-r-----  1 mysql mysql 101M Feb 17 10:30  binlog.000071
-rw-r-----  1 mysql mysql 101M Feb 17 11:55  binlog.000072
-rw-r-----  1 mysql mysql 101M Feb 17 13:20  binlog.000073
-rw-r-----  1 mysql mysql 101M Feb 17 14:45  binlog.000074
-rw-r-----  1 mysql mysql 101M Feb 17 16:10  binlog.000075
-rw-r-----  1 mysql mysql 101M Feb 17 17:35  binlog.000076
-rw-r-----  1 mysql mysql 101M Feb 17 19:00  binlog.000077
-rw-r-----  1 mysql mysql 101M Feb 17 20:25  binlog.000078
-rw-r-----  1 mysql mysql 101M Feb 17 21:50  binlog.000079
-rw-r-----  1 mysql mysql 101M Feb 17 23:15  binlog.000080
-rw-r-----  1 mysql mysql  53M Feb 18 00:00  binlog.000081
-rw-r-----  1 mysql mysql 101M Feb 18 01:25  binlog.000082
-rw-r-----  1 mysql mysql 101M Feb 18 02:51  binlog.000083
-rw-r-----  1 mysql mysql 101M Feb 18 04:16  binlog.000084
-rw-r-----  1 mysql mysql 101M Feb 18 05:41  binlog.000085
-rw-r-----  1 mysql mysql 101M Feb 18 07:07  binlog.000086
-rw-r-----  1 mysql mysql 101M Feb 18 08:32  binlog.000087
-rw-r-----  1 mysql mysql 101M Feb 18 09:58  binlog.000088
-rw-r-----  1 mysql mysql 101M Feb 18 11:23  binlog.000089
-rw-r-----  1 mysql mysql 101M Feb 18 12:49  binlog.000090
-rw-r-----  1 mysql mysql 101M Feb 18 14:15  binlog.000091
-rw-r-----  1 mysql mysql 101M Feb 18 15:40  binlog.000092
-rw-r-----  1 mysql mysql 101M Feb 18 17:05  binlog.000093
-rw-r-----  1 mysql mysql 101M Feb 18 18:30  binlog.000094
-rw-r-----  1 mysql mysql 101M Feb 18 19:55  binlog.000095
-rw-r-----  1 mysql mysql 101M Feb 18 21:20  binlog.000096
-rw-r-----  1 mysql mysql 101M Feb 18 22:45  binlog.000097
-rw-r-----  1 mysql mysql  88M Feb 19 00:00  binlog.000098
-rw-r-----  1 mysql mysql 101M Feb 19 01:25  binlog.000099
-rw-r-----  1 mysql mysql 101M Feb 19 02:51  binlog.000100
-rw-r-----  1 mysql mysql 101M Feb 19 04:16  binlog.000101
-rw-r-----  1 mysql mysql 101M Feb 19 05:42  binlog.000102
-rw-r-----  1 mysql mysql 101M Feb 19 07:12  binlog.000103
-rw-r-----  1 mysql mysql 101M Feb 19 08:48  binlog.000104
-rw-r-----  1 mysql mysql 101M Feb 19 10:25  binlog.000105
-rw-r-----  1 mysql mysql 101M Feb 19 12:02  binlog.000106
-rw-r-----  1 mysql mysql 101M Feb 19 13:39  binlog.000107
-rw-r-----  1 mysql mysql 101M Feb 19 15:16  binlog.000108
-rw-r-----  1 mysql mysql 101M Feb 19 16:52  binlog.000109
-rw-r-----  1 mysql mysql 101M Feb 19 18:29  binlog.000110
-rw-r-----  1 mysql mysql 101M Feb 19 20:05  binlog.000111
-rw-r-----  1 mysql mysql 101M Feb 19 21:41  binlog.000112
-rw-r-----  1 mysql mysql 101M Feb 19 23:17  binlog.000113
-rw-r-----  1 mysql mysql  45M Feb 20 00:00  binlog.000114
-rw-r-----  1 mysql mysql 101M Feb 20 01:35  binlog.000115
-rw-r-----  1 mysql mysql 101M Feb 20 03:08  binlog.000116
-rw-r-----  1 mysql mysql 101M Feb 20 04:42  binlog.000117
-rw-r-----  1 mysql mysql 101M Feb 20 06:14  binlog.000118
-rw-r-----  1 mysql mysql 101M Feb 20 07:46  binlog.000119
-rw-r-----  1 mysql mysql 101M Feb 20 09:19  binlog.000120
-rw-r-----  1 mysql mysql 101M Feb 20 10:52  binlog.000121
-rw-r-----  1 mysql mysql 101M Feb 20 12:25  binlog.000122
-rw-r-----  1 mysql mysql 101M Feb 20 13:58  binlog.000123
-rw-r-----  1 mysql mysql 101M Feb 20 15:30  binlog.000124
-rw-r-----  1 mysql mysql 101M Feb 20 17:03  binlog.000125
-rw-r-----  1 mysql mysql 101M Feb 20 18:35  binlog.000126
-rw-r-----  1 mysql mysql 101M Feb 20 20:08  binlog.000127
-rw-r-----  1 mysql mysql 101M Feb 20 21:40  binlog.000128
-rw-r-----  1 mysql mysql  85M Feb 20 22:58  binlog.000129

Safe to delete most if not all of those files? Or is there a way to set the binlogs to self-clean sooner in a way that doesn't harm Cloudron?

[girish]

@d19dotca Interesting. I haven't seen this before (and 12G seems massive). Can you try https://dev.mysql.com/doc/refman/8.0/en/purge-binary-logs.html ? You can get mysql access using mysql -uroot -ppassword (as-is)

[girish]

One difference between Ubuntu 18 and 20 is that the mysql versions. The former uses 5.7 and the latter is 8.0

[robi]

@girish is there a periodic purge setting in Cloudron?

[d19dotca]

@girish I know in one of the products I support for my day job that we have a script executed as a cron job for maintaining the binlog files. I suspect it may be needed to have something similar to clean up MySQL bin logs in Ubuntu 20.04, managed by Cloudron upon install I suppose? Just a thought anyways.

Typically it's okay to just delete those files (and at least in my experience they're normally only created when replication is enabled between two MySQL servers) but I wasn't certain if this would screw up Cloudron at all, and it's my production server so didn't want to remove it yet just in case, but sounds like I can then.

[d19dotca]

Thinking out loud here but a couple things I'm wondering about (and will try to test what I can):

1. Is this issue unique to only my instance, or happening to instances of Cloudron?
2. If happening to other instances, is the commonality being on Ubuntu 20.04 or MySQL 8.x?
3. If others have this issue, then I'd suspect we need to enable something during Cloudron setup to handle this out of the box, or ideally just disable bin logs (if possible).

[d19dotca]

I ran the following commands to temporarily reclaim the disk space (which was 14 GB today, so about 2 GB higher in just 24 hours).

1. mysql -uroot -ppassword
2. PURGE BINARY LOGS BEFORE '2021-02-21 23:00:00'; (replace the date with today's date of course when running it in the future for anyone stumbling across this post)

Running the above gave me over 13 GB back, with the /var/lib/mysql directory taking up just over 600 MB now instead fo the 14 GB.

Of course, I expect this will be an issue going forward still and will need to find a more permanent solution.

Screenshots for reference on disk space usage before & after:

[robi]

There must be a new default setting in the newer mysql that enables binlog files.

I don't have any on the previous version.

[girish]

@d19dotca do you still see it growing after you cleaned up? We haven't had any other reports like this.

[d19dotca]

@girish Yes, the issue is still present. Here's the current output:

-rw-r-----  1 mysql mysql 101M Feb 21 23:23  binlog.000145
-rw-r-----  1 mysql mysql  38M Feb 22 00:00  binlog.000146
-rw-r-----  1 mysql mysql 101M Feb 22 01:36  binlog.000147
-rw-r-----  1 mysql mysql 101M Feb 22 03:13  binlog.000148
-rw-r-----  1 mysql mysql 101M Feb 22 04:50  binlog.000149
-rw-r-----  1 mysql mysql 101M Feb 22 06:32  binlog.000150
-rw-r-----  1 mysql mysql 101M Feb 22 08:18  binlog.000151
-rw-r-----  1 mysql mysql 101M Feb 22 10:05  binlog.000152
-rw-r-----  1 mysql mysql 101M Feb 22 11:52  binlog.000153
-rw-r-----  1 mysql mysql 101M Feb 22 13:38  binlog.000154
-rw-r-----  1 mysql mysql 101M Feb 22 15:25  binlog.000155
-rw-r-----  1 mysql mysql 101M Feb 22 17:12  binlog.000156
-rw-r-----  1 mysql mysql 101M Feb 22 18:58  binlog.000157
-rw-r-----  1 mysql mysql 101M Feb 22 20:42  binlog.000158
-rw-r-----  1 mysql mysql 101M Feb 22 22:23  binlog.000159
-rw-r-----  1 mysql mysql  98M Feb 23 00:00  binlog.000160
-rw-r-----  1 mysql mysql 101M Feb 23 01:38  binlog.000161
-rw-r-----  1 mysql mysql 101M Feb 23 03:14  binlog.000162
-rw-r-----  1 mysql mysql 101M Feb 23 04:52  binlog.000163
-rw-r-----  1 mysql mysql 101M Feb 23 06:32  binlog.000164
-rw-r-----  1 mysql mysql 101M Feb 23 08:13  binlog.000165
-rw-r-----  1 mysql mysql 101M Feb 23 09:53  binlog.000166
-rw-r-----  1 mysql mysql 101M Feb 23 11:33  binlog.000167
-rw-r-----  1 mysql mysql 101M Feb 23 13:13  binlog.000168
-rw-r-----  1 mysql mysql 101M Feb 23 14:53  binlog.000169
-rw-r-----  1 mysql mysql 101M Feb 23 16:33  binlog.000170
-rw-r-----  1 mysql mysql 101M Feb 23 18:12  binlog.000171
-rw-r-----  1 mysql mysql 101M Feb 23 19:51  binlog.000172
-rw-r-----  1 mysql mysql 101M Feb 23 21:31  binlog.000173
-rw-r-----  1 mysql mysql 8.0M Feb 23 21:38  binlog.000174

Is it possible to grab a copy of your MySQL my.cnf file so I can compare? Maybe it's something weird about the default Ubuntu 20.04 image at OVH.

[d19dotca]

Found this... I may test this out: https://dba.stackexchange.com/questions/226269/disable-binary-logs-in-mysql-8-0

Curious, do you have skip-log-bin in your my.cnf file? Because I do not and suspect this is the root cause.

Here's the contents of my my.cnf file:

ubuntu@cloudron:~$ cat /etc/mysql/my.cnf
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

# http://bugs.mysql.com/bug.php?id=68514
[mysqld]
performance_schema=OFF
max_connections=50
# on ec2, without this we get a sporadic connection drop when doing the initial migration
max_allowed_packet=64M

# https://mathiasbynens.be/notes/mysql-utf8mb4
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# set timezone to UTC
default_time_zone='+00:00'

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

All of that is the default setup, I haven't manually changed any of it.

[girish]

@d19dotca mysql conf should be the same on all the servers - https://git.cloudron.io/cloudron/box/-/blob/master/setup/start/mysql.cnf

[girish]

@d19dotca Ah, this seems to be a some MySQL 8/Ubuntu 20 thing. It generates these binlogs for me on my test cloudron.

-rw-r----- 1 mysql mysql  179 Feb 15 20:42  binlog.000001
-rw-r----- 1 mysql mysql  179 Feb 15 20:45  binlog.000002
-rw-r----- 1 mysql mysql 116K Feb 15 20:45  binlog.000003
-rw-r----- 1 mysql mysql  13M Feb 17 00:00  binlog.000004
-rw-r----- 1 mysql mysql  43M Feb 18 00:00  binlog.000005
-rw-r----- 1 mysql mysql  43M Feb 19 00:00  binlog.000006
-rw-r----- 1 mysql mysql  13M Feb 20 00:00  binlog.000007
-rw-r----- 1 mysql mysql  28M Feb 21 00:00  binlog.000008
-rw-r----- 1 mysql mysql 2.8M Feb 22 00:00  binlog.000009
-rw-r----- 1 mysql mysql  13M Feb 23 00:00  binlog.000010
-rw-r----- 1 mysql mysql  14M Feb 23 21:41  binlog.000011
-rw-r----- 1 mysql mysql  176 Feb 23 00:00  binlog.index

[d19dotca]

@girish Right. Yeah I figured it was a weird issue in MySQL or Ubuntu 20.04 since both of those changed after I moved servers earlier last week. I suspect the skip-log-bin option should be added to the my.cnf file, my gut tells me that should do the trick.

This seems to be explained too in the actual documentation. One answer highlighted this in that post earlier... https://dba.stackexchange.com/a/228959

In fact, when I compare the MySQL documentation between 5.7 and 8.0, it seems that binlogs became default enabled in 8.0, was not a default in 5.7.

[girish]

@d19dotca We can't skip the binlog because it's used for db recovery. But in the mysql addon we set max_binlog_size = 100M and also expire_logs_days = 10. So, I will put this in the main mysql config as well.

See https://dev.mysql.com/doc/refman/8.0/en/binary-log.html for importance of binlog.

edit: it's binlog_expire_log_seconds now - https://dev.mysql.com/worklog/task/?id=10924

[d19dotca]

@girish Sounds good. I'm surprised that binlogs is needed for database recovery in a non-replicated scenario. In my day-job if we need to recover a database we just use the mysqldump created and then use the mysql command to import, nothing needed on the binlog side of things. Maybe you guys do it differently though than I'm used to.

If I edit the my.cnf file, will a future Cloudron update overwrite it? Trying to see if I should add it in now or just wait for the update so I don't mess up the update part at all.

[girish]

@d19dotca Same. We also always use only mysqldump for recovery and major mysql upgrades (like the upcoming one from 5.7 to 8.0). Generally, I don't trust those direct data directory upgrade procedures But the recovery stuff is sometimes useful when the disk goes corrupt and we want to recover quickly.

From what I can make out, this issue has surfaced because expire_logs_days which was 10 is now deprecated and replaced with this log_seconds variable which has a default of 30 days...

If I edit the my.cnf file, will a future Cloudron update overwrite it? Trying to see if I should add it in now or just wait for the update so I don't mess up the update part at all.

Yes, the my.cnf will be overwritten. You can just add it it's fine, the update will merely overwrite it with the same config... Currently, I am testing with 100M max_binlog_size and 432000 (5 days) as expire_log_days.

[girish]

https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery-binlog.html has the recovery procedure (disclaimer: i have never had to use this).

[d19dotca]

Okay this seems to have worked. I took what you did in the git but made it 6 hours (21600 seconds) instead as at least a test. Restarted the mysql service and now many of the binlogs are gone.

Side question: I tried that earlier and restarted mysql but from the Cloudron Services page, and it didn't seem to work. However when I restarted mysql on the command line, it worked. Is there a difference in that process?

[d19dotca]

@girish said in Able to clean up binlog.###### files in /var/lib/mysql directory?:

432000 (5 days) as expire_log_days

Out of curiosity, is there a need to have 10 days of binlogs? Just curious, because I was seeing issues before the 10 days, so I'm worried that setting won't work for me (and presumably at least a few others with well-used Cloudron's on servers with limited local disk space). I guess I can overwrite it though in my instance using something in the .d directories that are included, or do those get overwritten too? If it can be a lot less (like 3 days or something) that'd be best I'd think, but maybe more time is needed for most environments and mine is just a fringe scenario given my limited disk space.