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

[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.

[nebulon]

@d19dotca as far as I understand the situation, it makes not too much sense to keep binlogs for db recovery for longer than the last backup (which contains the mysqldump for restore)

[girish]

@nebulon that's correct. but not sure how we can make the mysql.cnf configuration based on that? the cnf is installed at update time with a static number.

[nebulon]

I guess setting it to something more reasonable for us like a couple of days should be plenty

[d19dotca]

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

I guess setting it to something more reasonable for us like a couple of days should be plenty

Exactly, yes. I think it should basically be the minimal number possible / reasonable, especially when a well-used Cloudron like mine seems to generate 1-2 GB of them in just a 24 hour period. If we kept that as 10 days that'd be anywheres from 10 GB to 20 GB which is far too much disk space consumed for that, IMO. I thinking keeping them for just two days would be sufficient, heck even maybe just one day.

[p44]

@d19dotca Probably I had the same problem, yesterday one instance alerted that disk was full. I didn't have time yet to check inside.

[d19dotca]

@p44 Yeah probably. I'm honestly very surprised how many people seemingly haven't run into this issue yet. I guess most using Cloudron are still on Ubuntu 18.04 rather than 20.04, or those on 20.04 have plenty of disk space where it's not a concern at all (unfortunately I don't have that luxury, haha) or they just don't use their Cloudrons nearly as much as some of us do which means they have far less writes to the database thus less binlog entries. Guess it's a "fringe" scenario for now haha but definitely still a valid one that we need to fix. I think the changes coming from Girish & Nebulon will have a great impact on that and prevent issues like I and you were running into where the disk usage goes insane from binlog files. Keeping the binlogs to just 1 or 2 days should be plenty for actively used Cloudron servers.

[girish]

All our managed instances (our old business) are on Ubuntu 20 and they don't seem to accumulate as much data. It's around 100MB everyday and so it's < 1GB for 10 days. I guess the deal is we have the luxury of extra space. What I noticed is that it seems if you start afresh on Ubuntu 20 the behavior appears different from if you migrate from Ubuntu 18. I haven't really tinkered with the combinations of upgrades.

In any case, binlog is at 2 days for both the mysql addon and the main mysql server now.

[girish]

@d19dotca So reading a bit more, I think you might be right that it's of minimal use when not replicating and for replaying commands in that mode... So, I will disable it altogether. It got turned on in MySQL 8 by default but was off in MySQL 5.7.

[d19dotca]

@girish Music to my ears. haha. Thanks Girish!

[p44]

@d19dotca totally agree on this point of view.

I tryed to run:

mysql -uroot -ppassword
PURGE BINARY LOGS BEFORE '2021-02-21 23:00:00';

and I saved around 2,5 GB...

I think Cloudron have two missing two things:

1. Control of logs amounts and cleaning function;
2. Control of logs to understand potentially threat and give ability to eg. block IP or block login attempts.