SYSTEM INFORMATION | |
---|---|
OS type and version | Ubuntu 20.04.5 LTS |
Virtualmin version | 7.2-1 |
Hello
I have a problem with Virtualmin and mySQL.
We have a server bug due to lack of storage space.
We cleaned up our files/backup and overnight the free space was drastically reduced.
By doing research it seems that mysql takes a huge space in a short time.
root@vps:/var/log# cd /var/lib/mysql
root@vps:/var/lib/mysql# ls -lsah
total 34G
4.0K drwx------ 9 mysql mysql 4.0K Oct 30 16:06 .
4.0K drwxr-xr-x 63 root root 4.0K Oct 30 15:38 ..
4.0K -rw-r----- 1 mysql mysql 56 Aug 31 15:40 auto.cnf
4.0K -rw-r----- 1 mysql mysql 201 Sep 25 00:00 binlog.000031
4.0K -rw-r----- 1 mysql mysql 201 Sep 26 00:00 binlog.000032
4.0K -rw-r----- 1 mysql mysql 201 Sep 27 00:00 binlog.000033
4.0K -rw-r----- 1 mysql mysql 201 Sep 28 00:00 binlog.000034
4.0K -rw-r----- 1 mysql mysql 201 Sep 29 00:00 binlog.000035
4.0K -rw-r----- 1 mysql mysql 201 Sep 30 00:00 binlog.000036
4.0K -rw-r----- 1 mysql mysql 201 Oct 1 00:00 binlog.000037
4.0K -rw-r----- 1 mysql mysql 201 Oct 2 00:00 binlog.000038
4.0K -rw-r----- 1 mysql mysql 201 Oct 3 00:00 binlog.000039
4.0K -rw-r----- 1 mysql mysql 201 Oct 4 00:00 binlog.000040
4.0K -rw-r----- 1 mysql mysql 201 Oct 5 00:00 binlog.000041
4.0K -rw-r----- 1 mysql mysql 201 Oct 6 00:00 binlog.000042
4.0K -rw-r----- 1 mysql mysql 201 Oct 7 00:00 binlog.000043
4.0K -rw-r----- 1 mysql mysql 201 Oct 8 00:00 binlog.000044
4.0K -rw-r----- 1 mysql mysql 201 Oct 9 00:00 binlog.000045
4.0K -rw-r----- 1 mysql mysql 201 Oct 10 00:00 binlog.000046
4.0K -rw-r----- 1 mysql mysql 201 Oct 11 00:00 binlog.000047
4.0K -rw-r----- 1 mysql mysql 201 Oct 12 00:00 binlog.000048
4.0K -rw-r----- 1 mysql mysql 201 Oct 13 00:00 binlog.000049
4.0K -rw-r----- 1 mysql mysql 201 Oct 14 00:00 binlog.000050
4.0K -rw-r----- 1 mysql mysql 201 Oct 15 00:00 binlog.000051
4.0K -rw-r----- 1 mysql mysql 201 Oct 16 00:00 binlog.000052
4.0K -rw-r----- 1 mysql mysql 201 Oct 17 00:00 binlog.000053
4.0K -rw-r----- 1 mysql mysql 201 Oct 18 00:00 binlog.000054
4.0K -rw-r----- 1 mysql mysql 201 Oct 19 00:00 binlog.000055
4.0K -rw-r----- 1 mysql mysql 201 Oct 20 00:00 binlog.000056
4.0K -rw-r----- 1 mysql mysql 201 Oct 21 00:00 binlog.000057
4.0K -rw-r----- 1 mysql mysql 201 Oct 22 00:00 binlog.000058
4.0K -rw-r----- 1 mysql mysql 201 Oct 23 00:00 binlog.000059
4.0K -rw-r----- 1 mysql mysql 201 Oct 24 00:00 binlog.000060
4.0K -rw-r----- 1 mysql mysql 180 Oct 24 22:43 binlog.000061
4.0K -rw-r----- 1 mysql mysql 496 Oct 24 00:00 binlog.index
4.0K -rw------- 1 mysql mysql 1.7K Aug 31 15:40 ca-key.pem
4.0K -rw-r--r-- 1 mysql mysql 1.1K Aug 31 15:40 ca.pem
4.0K -rw-r--r-- 1 mysql mysql 1.1K Aug 31 15:40 client-cert.pem
4.0K -rw------- 1 mysql mysql 1.7K Aug 31 15:40 client-key.pem
0 -rw-r--r-- 1 mysql mysql 0 Oct 30 15:39 debian-5.7.flag
192K -rw-r----- 1 mysql mysql 192K Oct 30 16:06 '#ib_16384_0.dblwr'
8.2M -rw-r----- 1 mysql mysql 8.2M Oct 30 16:06 '#ib_16384_1.dblwr'
4.0K -rw-r----- 1 mysql mysql 4.0K Oct 24 22:43 ib_buffer_pool
12M -rw-r----- 1 mysql mysql 12M Oct 30 16:06 ibdata1
4.0K drwxr-x--- 2 mysql mysql 4.0K Oct 30 16:06 '#innodb_redo'
4.0K drwxr-x--- 2 mysql mysql 4.0K Oct 30 16:06 '#innodb_temp'
4.0K drwxr-x--- 2 mysql mysql 4.0K Oct 30 16:05 mysql
16G -rw-r----- 1 mysql mysql 16G Oct 30 16:06 mysql.ibd
4.0K -rw-r----- 1 mysql mysql 6 Oct 30 16:06 mysql_upgrade_info
4.0K drwxr-x--- 2 mysql mysql 4.0K Oct 24 22:43 performance_schema
4.0K drwsr-s--- 2 mysql mysql 4.0K Aug 31 15:48 planete_warez
4.0K -rw------- 1 mysql mysql 1.7K Aug 31 15:40 private_key.pem
4.0K -rw-r--r-- 1 mysql mysql 452 Aug 31 15:40 public_key.pem
4.0K -rw-r--r-- 1 mysql mysql 1.1K Aug 31 15:40 server-cert.pem
4.0K -rw------- 1 mysql mysql 1.7K Aug 31 15:40 server-key.pem
4.0K drwxr-x--- 2 mysql mysql 4.0K Aug 31 15:40 sys
4.2G -rw-r----- 1 mysql mysql 4.2G Oct 30 16:06 undo_001
15G -rw-r----- 1 mysql mysql 15G Oct 30 16:06 undo_002
4.0K drwsr-s--- 2 mysql mysql 4.0K Aug 31 18:39 wiki
It seems that the following tables cause problems
- undo_001
- undo_002
- and mysql.ibd file
16G -rw-r----- 1 mysql mysql 16G Oct 30 16:06 mysql.ibd
4.2G -rw-r----- 1 mysql mysql 4.2G Oct 30 16:06 undo_001
15G -rw-r----- 1 mysql mysql 15G Oct 30 16:06 undo_002
I noticed this error on the log too:
Oct 30 16:04:18 vps.XXXX systemd[1]: Failed to start MySQL Community Server.
Oct 30 16:01:52 vps.XXXX systemd[1]: Failed to start MySQL Community Server.
Oct 30 15:59:24 vps.XXXX systemd[1]: Failed to start MySQL Community Server.
Oct 30 15:56:58 vps.XXXX systemd[1]: Failed to start MySQL Community Server.
Oct 30 15:54:29 vps.XXXX systemd[1]: Failed to start MySQL Community Server.
Oct 30 15:51:12 vps.XXXXsystemd[1]: Failed to start MySQL Community Server.
Oct 30 15:49:32 vps.XXXX systemd[1]: Failed to start MySQL Community Server.
Oct 30 15:47:04 vps.XXXX systemd[1]: Failed to start MySQL Community Server.
Oct 30 15:44:36 vps.XXXX systemd[1]: Failed to start MySQL Community Server.
Oct 30 15:37:09 vvps.XXXX systemd[1]: Failed to start MySQL Community Server.
etc....
I also noticed that mysql also eats all CPU resources.
I don’t use mySQL for any other service at all.
I have planified a scheduled backup of Webmin configuration and virtualmin but I have deactive it.
obviously, the mysql service cannot start by cli:
xxxxx@vps:/var/log$ sudo service mysql start
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.
and via webmin too:
Error! MySQL is not running on your system - database list could not be retrieved.
MySQL error message
The full MySQL error message was : DBI connect failed : Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
See this in the log of mysql service:
journalctl -fu mysql
-- Logs begin at Wed 2022-08-31 13:22:26 CEST. --
Oct 30 14:19:07 vps.XXXX systemd[1]: Failed to start MySQL Community Server.
Oct 30 14:19:08 vps.XXXX systemd[1]: mysql.service: Scheduled restart job, restart counter is at 664.
Oct 30 14:19:08 vps.XXXX systemd[1]: Stopped MySQL Community Server.
Oct 30 14:19:08 vps.XXXX systemd[1]: Starting MySQL Community Server...
Oct 30 14:21:39 vps.XXXX systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Oct 30 14:21:39 vps.XXXX systemd[1]: mysql.service: Failed with result 'exit-code'.
Oct 30 14:21:39 vps.XXXX systemd[1]: Failed to start MySQL Community Server.
Oct 30 14:21:39 vps.XXXX systemd[1]: mysql.service: Scheduled restart job, restart counter is at 665.
Oct 30 14:21:39 vps.XXXX systemd[1]: Stopped MySQL Community Server.
Oct 30 14:21:39 vps.XXXX systemd[1]: Starting MySQL Community Server...
It seems the system is trying to restart the mysql service service to no avail. (loop)
it seems too that mysql MySQL is attempting a rollback
- This is 100% the result of the transaction logs not being truncted.
I am new to Virtualmin and knowledgeable help would be greatly appreciated.
Thanks in advance.