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
Cause
The mysql system database is corrupter or is missing. It is often happens after hard reset of the server.
Resolution
-
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 frommy.cnf
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.
-
Create a back up of all the databases using the instructions form this KB article.
Note: Make sure that
skip-grant-tables
is added tomy.cnf
. -
Stop the MySQL 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 system database with the command:
# mysql_install_db
-
Make sure that correct permissions and ownership are set up.
-
Start the MySQL service:
# systemctl start mysql
-
Configure the installation:
# /usr/bin/mysql_secure_installation
-
Open the file
/root/dblist.txt
and remove themysql
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 admin user is unknown".
-
Restore MySQL system users:
# plesk repair mysql
-
Restore the apsc user by running:
# plesk repair db
-
Reinstall Roundcube and Horde webmails.