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

Symptoms

• MySQL/MariaDB fails to start due to the missing/corrupted tables in the mysql database. The following error messages appear in /var/log/mysqld.log or /var/log/mariadb/mariadb.log:

  CONFIG_TEXT: [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format '<table_name>'

  CONFIG_TEXT: [ERROR] Fatal error: Can't open and lock privilege tables: Table '<table_name>' doesn't exist

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

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

  CONFIG_TEXT: /usr/sbin/mysqld: Table '<table_name>' doesn't exist
  [ERROR] Can't open the <table_name> table. Please run mysql_upgrade to create it.

• Plesk is not accessible with one of the following error messages in a web-browser:

  PLESK_INFO: ERROR: PleskDBException: Unable to connect to database: mysql_connect(): No such file or directory /var/run/mysqld/mysqld.sock (Error code: 2002). Please check that database server is started and accessible. (Abstract.php:69)

  PLESK_INFO: ERROR: Uncaught exception 'Zend_Log_Exception' with message '"/var/log/plesk/panel.log" cannot be opened with mode "a"' in /usr/local/psa/admin/externals/Zend/Log/Writer/Stream.php:81

  PLESK_INFO: SQLSTATE[HY000] [2002] No such file or directory.

  PLESK_INFO: SQLSTATE[HY000] [2002] Resource temporarily unavailable

Cause

Tables in the mysql database are missing or corrupted.

Resolution

1. Connect to your Plesk server via SSH.

2. Stop the MariaDB/MySQL service:

   # systemctl stop mariadb && systemctl stop mysql

3. Add skip-grant-tables to the my.cnf file:

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

   3.2. Add the skip-grant-tables directive under the [mysqld] section:

   CONFIG_TEXT: [mysqld]
   skip-grant-tables

   3.3. Save the changes and close the file.

4. Start the MariaDB/MySQL service:

   # systemctl start mariadb && systemctl start mysql

   Note: If the service hangs, apply step 6 then proceed from step 4.

5. Create a database dump of the mysql database:

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -uadmin --skip-extended-insert mysql > /root/mysql_dump.`date +%F`.sql

6. Move the mysql directory to /root:

   # mv /var/lib/mysql/mysql/ /root

7. Drop the mysql database from MySQL, if exists:

   # plesk db 'drop database mysql'

8. List all available Plesk daily dumps (the default dumps folder is /var/lib/psa/dumps):

   # ls -lat `cat /etc/psa/psa.conf | grep DUMP_D | grep -v "#" | awk '{print $2}'`/mysql.daily.dump*
   
   -rw------- 1 root root 236253 Dec 3 01:51 /var/lib/psa/dumps/mysql.daily.dump.0.gz
   -rw------- 1 root root 229653 Jul 2 01:48 /var/lib/psa/dumps/mysql.daily.dump.1.gz
   -rw------- 1 root root 222485 Apr 1 01:56 /var/lib/psa/dumps/mysql.daily.dump.2.gz

9. Restore the mysql database from the most recent Plesk daily dump:

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

   Note: If the operation fails with the message ERROR 1049 (42000): Unknown database 'mysql' , run plesk db "create database mysql" to recreate it and repeat above command.

10. Remove the skip-grant-tables directive which was added on step 3.

11. Start the MariaDB/MySQL service:

    # systemctl restart mariadb && systemctl restart mysql