One of my clients experienced a problem, Virtualmin couldn't connect to MySQL 8.0 server, the problem appeared after the VPS rebooted. In Virtualmin MySQL Database Server page, There is an error message:
DBI connect failed : Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’
And when trying to rewrite/change the password in MySQL server configurations, the message:
DBI connect failed : Access denied for user 'root'@'localhost' (using password: YES)
On the error.log:
Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
Most likely the mysql.user table corrupted. This is a problem because the last backup was 1 month ago and there are about 10 databases.
So the first step is try to reset the MySQL root password via mysqld_safe.
service mysql stop
mysqld_safe --skip-grant-tables &
The error message show up:
mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
The solutions before running the mysqld_safe we need to create the directory first:
mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld
After entering the mysql, I'm checking the users with SELECT user,host FROM mysql.user; and found that the root user does not exist, so it's natural that we can't change the root password in the first place.
Because the main goal is to secure the existing database first, I created new MySQL superuser to backup all databases as soon as possible.
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'MyNewPass';
Make it a superuser:
GRANT ALL PRIVILEGES ON *.* TO 'new_user'@'localhost' WITH GRANT OPTION;
Than reload all the privileges:
FLUSH PRIVILEGES;
Backing up all databases using mysqldump:
mysqldump -u new_user -p –all-databases > all_databases_dump.sql
After that, go to the database settings in Virtualmin, change the old user and password to the new user and password we just created, and save it.
Finally Virtualmin able to connect to the MySQL server again.