MySQL/MariaDB service fails to start on a Plesk server: Table ‘mysql.user’ doesn’t exist

Dmitry Bystrov

4 months ago

Symptoms

MySQL/MariaDB service fails to start with the following error message in /var/log/mysql/error.log or /var/log/syslog:

[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

[ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist

[ERROR] Could not open mysql.plugin table: "Table 'mysql.plugin' doesn't exist". Some plugins may be not loaded

The mysql system database is corrupted or is missing. It often happens after hard reset of the server.

Connect to the Plesk server via SSH.
Modify the my.cnf file:

2.1. Open the my.cnf file in a text editor. In this example, we are using the vi editor:
- on CentOS/RHEL-based distributions
  
  # vi /etc/my.cnf
- on Debian/Ubuntu-based distributions
  
  # vi /etc/mysql/my.cnf
2.2. Add the skip-grant-tables line under the [mysqld] section:

[mysqld]
skip-grant-tables
<...>

2.3. Save the changes and close the file.
Restart the MySQL/MariaDB service:

# systemctl restart mysql

# systemctl restart mariadb
Switch to the directory with daily dumps:

# cd /var/lib/psa/dumps
Restore the database from the latest daily dump:

# zcat mysql.daily.dump.0.gz | sed -n '/-- Current Database: `mysql`/,/-- Current Database:*/p' | plesk db

Note: If there are no daily dumps, see the instructions below.
Remove the skip-grant-tables directive from my.cnf which was added on step 2.
Restart the MySQL/MariaDB service:

# systemctl restart mysql

# systemctl restart mariadb

If there are no daily dumps

If there are no daily dumps, reinitialize MySQL/MariaDB:

Connect to the server via SSH.
Create a back up of all the databases using the instructions form this KB article.

Note: Make sure that skip-grant-tables is added to my.cnf.
Stop the MySQL/MariaDB service:

# systemctl stop mysql
Create the directory /root/mysql/ and move MySQL there:

# mkdir /root/mysql

# mv /root/mysql/ /var/lib/mysql
Reinstall the MySQL/MariaDB system database with the command:

# mysql_install_db
Make sure that correct permissions and ownership are set up.
Start the MySQL/MariaDB service:

# systemctl start mysql
Configure the installation:

# /usr/bin/mysql_secure_installation
Open the file /root/dblist.txt and remove the mysql database from there.
Save the changes and close the file.
Restore the databases:

# for i in cat /root/dblist.txt; do MYSQL_PWD=cat /etc/psa/.psa.shadow mysql -u admin < /root/mysql_dumps_all/"$i".sql; done
Recreate the admin user using the steps from this KB article - the section "If the password for MySQL/MariaDB admin user is unknown".
Restore MySQL/MariaDB system users:

# plesk repair mysql
Restore the apsc user by running:

# plesk repair db
Restore the phpMyAdmin user.
Reinstall Roundcube and Horde webmails.

0 Shares