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

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

[d19dotca]

@p44 Agreed. It was definitely a bug. It's all fixed now though for 6.2 when it comes out. https://git.cloudron.io/cloudron/box/-/blob/master/setup/start/mysql.cnf#L19

[p44]

@d19dotca Ok, thank's to your attention!