Virtualmin SQL Problem : service don't satrt, MYSQL files occupied whole disk

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 :slight_smile:

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

If you search the forum you would find solutions.

@stefan1959

I don’t see how to connect to the mysql database when the service itself can’t start

Has you system got plenty memory? Have you rebooted the system?

What version of mysql are you using ?
look at https://dev.mysql.com/blog-archive/mysql-8-0-16-mysql_upgrade-is-going-away/
it may help

This is now fixed.

Thanks to Phenomlab at sudonix.com for his professionalism and unfailing help.

For reference, system is running at its maximum capacity with even the virtual memory 100% allocated. Needed to reboot the server to release the lock (which I’ve completed with no issues) and have also modified

etc/mysql/mysql.conf.d/mysqld.cnf

And increased the thread_stack size from 128k to 256k. The mysql service has now started successfully. You should run a backup of all databases ASAP so that remaining transactions are committed and the transaction logs are flushed.

This topic was automatically closed 8 days after the last reply. New replies are no longer allowed.