MySQL binlogs growing unbounded — 145 GB, no expiry set?

[james]

Hello @jayonrails
Since you did run the commands I have asked for against /var/lib/mysql and not /home/yellowtent/platformdata/mysql/ I now understand that this is about the Cloudron MySQL server on the root system itself and not the MySQL service.

This changes things and I will have to look into it again.

[jayonrails]

Perfect. Since I am running out of space now, is there a safe way to do a backup (which I would share with you to investigate) and get rid of the bullshit data so I don't have to move to another server tonight?

[james]

Hello @jayonrails

Before we do a clean-up, please run the following command and share the output with me.

mysql -uroot -ppassword -e "SHOW BINARY LOGS;"

[jayonrails]

I cannot attach a textfile nor add the whole lines of output here because this is too big.

https://paste.cloudron.io/p/bat-cobra-emu

[james]

Hello @jayonrails
Thanks for that.

For a quick clean-up you can run the following command:

mysql -uroot -ppassword -e "RESET BINARY LOGS AND GTIDS;"

After that please check again with the following command if they got removed:

mysql -uroot -ppassword -e "SHOW BINARY LOGS;"

After that we need to understand why your MySQL server even has bin logging enabled.
But let's attempt the quick clean-up first.

[jayonrails]

Seems fine for now. Would you like to aggree on a meeting so we could analyze this together?

root@my:~# mysql -uroot -ppassword -e "RESET BINARY LOGS AND GTIDS;"
mysql: [Warning] Using a password on the command line interface can be insecure.
root@my:~# mysql -uroot -ppassword -e "SHOW BINARY LOGS;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   1895520 | No        |
+---------------+-----------+-----------+

[james]

Hello @jayonrails

I will have to check some things first, please give me 10-15 minutes.

[james]

Hello @jayonrails

Since 26.02.2021 all Cloudron installation ship the MySQL system server with:

skip-log-bin

in the /etc/mysql/my.cnf file.

Is your Cloudron installation older than 26.02.2021?

Please check your /etc/mysql/my.cnf file if skip-log-bin is set or if for example:

expire_logs_days = 10
max_binlog_size = 100M

or

binlog_expire_logs_seconds = 864000
max_binlog_size = 100M

You can also run the following command to get the live configuration:

mysql -uroot -ppassword -e "SELECT VERSION(), @@log_bin, @@binlog_expire_logs_seconds, @@expire_logs_days, @@max_binlog_size, @@binlog_expire_logs_auto_purge;"

[jayonrails]

hey @james,

thanks for your investigation. Let met get straight to your questions:

@james sagte:

Is your Cloudron installation older than 26.02.2021?

No, never has been.

@james sagte:

/etc/mysql/my.cnf

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

@james sagte:

You can also run the following command to get the live configuration:

root@my:~# mysql -uroot -ppassword -e "SELECT VERSION(), @@log_bin, @@binlog_expire_logs_seconds, @@expire_logs_days, @@max_binlog_size, @@binlog_expire_logs_auto_purge;"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1193 (HY000) at line 1: Unknown system variable 'expire_logs_days'

[james]

Hello @jayonrails
Your MySQL config looks empty and that is wrong.
Is also assume there is nothing in the folders listed in the config.

Since that command also failed for you please try again without the expire_logs_days:

mysql -uroot -ppassword -e "SELECT VERSION(), @@log_bin, @@binlog_expire_logs_seconds, @@max_binlog_size, @@binlog_expire_logs_auto_purge;"

[CRBear]

Hello,

I seem to have the same or a very similar issue as reported here:

https://forum.cloudron.io/topic/15988/mysql-binlogs-growing-unbounded-145-gb-no-expiry-set/18

My system:

Cloudron: 10.0.5
Ubuntu:   Ubuntu 26.04.1 LTS
Kernel:   Linux 7.0.0-31-generic

The root filesystem is currently at 93%:

/dev/sda1   150G   134G   11G   93% /

The unexpected usage is mainly:

/var/lib/mysql   76G

There are hundreds of binlog.xxxxxx files, mostly about 101 MB each.

MySQL version:

8.4.11-0ubuntu0.26.04.1

Current MySQL settings:

log_bin                       = 1
binlog_expire_logs_seconds    = 2592000
max_binlog_size               = 104857600
binlog_expire_logs_auto_purge = 1

SHOW REPLICA STATUS\G returns no replica.

/etc/mysql/my.cnf only contains:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

There is no skip-log-bin entry.

I have not deleted any binlogs or changed the MySQL configuration yet.

Could you please advise what I should do next?

In particular, is it safe/recommended in this situation to use:

RESET BINARY LOGS AND GTIDS;

and should skip-log-bin then be restored manually, or is there another Cloudron-supported fix?

The server currently has only about 11 GB free, so I would prefer to resolve this before the root filesystem fills up.

Thanks in advance for your help!

[nebulon]

So just to confirm here, this is about the Ubuntu host system mysql, not the mysql addon service running in docker?

If so how old is that Cloudron? The skip-log-bin was added in Cloudron around 2021 to /etc/mysql/mysql.cnf and a Cloudron update should theoretically ensure that this file gets updated accordingly.

[james]

This indeed does look like the same issue as https://forum.cloudron.io/topic/15988/mysql-binlogs-growing-unbounded-145-gb-no-expiry-set
So maybe the upgrade from Ubuntu 24 to Ubuntu 26 resets the /etc/mysql.cnf.
The guide https://docs.cloudron.io/guides/upgrade-ubuntu-26/ explictly states to:

For all packages (mime, nginx, timesyncd, logrotate, journald, etc.), select N or O to keep your currently-installed version (default).

I will try to reproduce this.

[jayonrails]

Good morning @james,

root@my:~# mysql -uroot -ppassword -e "SELECT VERSION(), @@log_bin, @@binlog_expire_logs_seconds, @@max_binlog_size, @@binlog_expire_logs_auto_purge;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------------+-----------+------------------------------+-------------------+---------------------------------+
| VERSION()               | @@log_bin | @@binlog_expire_logs_seconds | @@max_binlog_size | @@binlog_expire_logs_auto_purge |
+-------------------------+-----------+------------------------------+-------------------+---------------------------------+
| 8.4.11-0ubuntu0.26.04.1 |         1 |                      2592000 |         104857600 |                               1 |
+-------------------------+-----------+------------------------------+-------------------+---------------------------------+

@james sagte:

Is also assume there is nothing in the folders listed in the config.

Yes.

As there have been two notes to this thread, I would like to add:

When upgrading from Ubuntu 24.04 to Ubuntu 26.04. I kept all the config files and didn't let it create new ones.

[james]

Hello @jayonrails
I am currently trying to reproduce if the Ubuntu 24 to Ubuntu 26 upgrade resets the my.cnf.

[CRBear]

Yes, this is the Ubuntu host system MySQL under /var/lib/mysql, not the MySQL addon service running in Docker.

The Cloudron dashboard shows:

Cloudron activation time: 2 years ago
Cloudron version: 10.0.5
Ubuntu: 26.04.1 LTS

So the activation is approximately from 2024 and therefore well after skip-log-bin was added in 2021.

Regarding the Ubuntu 24 → 26 upgrade: I used the default N/O option to keep the currently installed configuration files when prompted.

I also checked /var/log/apt/term.log. For the MySQL 8.4 installation itself I cannot see any configuration-file prompt. Instead, the relevant part is:

Setting up mysql-server (8.4.11-0ubuntu0.26.04.1)…
update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode

Cloudron's own template still contains:

/home/yellowtent/box/setup/start/mysql.cnf

including:

# disable bin logs. they are only useful in replication mode
skip-log-bin

but skip-log-bin is no longer present anywhere under /etc/mysql.

I have not changed or deleted anything yet. I will wait for your guidance before making any changes, so please let me know what you would like me to do next.

I can also provide any further diagnostics that may help with reproducing this.

Thank you very much.

[james]

Hello @jayonrails and @crbear
I have merged the two topics into this one.

---

I have reproduced the issue, and it comes from upgrading Ubuntu 24 to Ubuntu 26.
A documentation update is deployed as of writing this.

The issue is that Ubuntu upgrade does uninstall the MySQL server on the system and both of you did correctly follow the guide and run:

systemctl stop box
apt install mysql-server
systemctl start box

This installed the MySQL server with the apt package default configuration and not the Cloudron configurations.
To fix this please run the following command from your ssh root/sudo session:

bash /home/yellowtent/box/setup/start.sh

This will ensure all services on the root system including MySQL are configured correctly.

---

Thank you again for bringing this to our attention.

[james]

Hello @jayonrails and @crbear
After running the command please report if this resolved the issue for you.

note: binlogs might not be purged automatically

If that is the case for you please run:

mysql -uroot -ppassword -e "RESET BINARY LOGS AND GTIDS;"

if that also does not clean up the binlog files in /var/lib/mysql/ stop the box.service and mysql.service and delete the files manually.

Example:

systemctl stop box.service mysql.service
# this will ask you for every file for confirmation
rm -rfi /var/lib/mysql/binlog.*
rm: remove regular file '/var/lib/mysql/binlog.000003'? y
rm: remove regular file '/var/lib/mysql/binlog.000004'? y
rm: remove regular file '/var/lib/mysql/binlog.index'? y
systemctl status mysql.service box.service

[CRBear]

Thank you very much - the issue is fully resolved now.

I ran bash /home/yellowtent/box/setup/start.sh, confirmed that skip-log-bin is present again and @@log_bin = 0.

RESET BINARY LOGS AND GTIDS; did not remove the existing files, so I followed your fallback procedure, stopped box.service and mysql.service, removed /var/lib/mysql/binlog.*, and started both services again.

Current status:

/var/lib/mysql: 337M
/: 58G used / 86G available / 41%
@@log_bin = 0

Everything is running normally again.

Thank you again for the incredibly fast and excellent support, and especially for reproducing the issue and updating the documentation so quickly. Much appreciated!

[jayonrails]

@james I cannot maintain this until the weekend, so I would leave FreeScout stopped and report back in a few days.

root@my:~# 
root@my:~# mysql -uroot -ppassword -e "RESET BINARY LOGS AND GTIDS;"
mysql: [Warning] Using a password on the command line interface can be insecure.
root@my:~# mysql -uroot -ppassword -e "SHOW BINARY LOGS;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   3783544 | No        |
+---------------+-----------+-----------+

This seems fine for now, let see if it blows up again...