MySQL service fails to start on a Plesk server: Table 'mysql.user' doesn't exist

Plesk for Linux kb: technical

Applicable to:

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

CONFIG_TEXT: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

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

https://jira.plesk.ru/browse/PPS-11971 - as example of investigation. Issue caused by hard reset

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:

CONFIG_TEXT: [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
Remove the skip-grant-tables directive which was added on step 2.
Restart the MySQL/MariaDB service:

# systemctl restart mysql

# systemctl restart mariadb

If there are no daily dumps reinitialize MySQL:

Connect to the server via SSH.
Back up all the databases as described in the article How to back up all MySQL databases via a command-line interface in Plesk for Linux.

Note: MariaDB should work with skip-grant-tables at the moment.
Stop MariaDB:

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

# mkdir /root/mysql

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

# mysql_install_db
Make sure that correct permissions and ownership was set up (see https://support.plesk.com/hc/en-us/articles/213401789), example for CentOS:

# chown -R mysql:mysql /var/lib/mysql
# find /var/lib/mysql/* -type d -exec chmod 0700 {} ;
# find /var/lib/mysql/* -type f -exec chmod 0660 {} ;
# chmod 755 /var/lib/mysql
Start MariaDB:

# systemctl start mariadb
Configure the installation:

# /usr/bin/mysql_secure_installation
Modify the file /root/dblist.txt created on step 2 - exclude the broken mysql database from there.
Restore all other databases created on step 2:

# 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 admin user is unknown".
Restore MySQL system users:

# plesk repair mysql
Restore the apsc user by running:

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