Applicable to:
- Plesk for Linux
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
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.
CONFIG_TEXT: Could not open mysql.plugin table: "Table 'mysql.plugin' doesn't exist". Some plugins may be not load
-
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.
Cause
Tables in the mysql
database are missing or corrupted.
https://jira.plesk.ru/browse/PPS-11971 - as example of investigation. Issue caused by hard reset
Resolution
-
Connect to the Plesk server via SSH.
-
Stop the MariaDB/MySQL service:
# systemctl stop mariadb
# systemctl stop mysql
-
Add
skip-grant-tables
to themy.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-tables3.3. Save the changes and close the file.
-
-
Start the MariaDB/MySQL service:
# systemctl start mariadb
# systemctl start mysql
-
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
-
Move the
mysql
directory to/root
:# mv /var/lib/mysql/mysql/ /root
-
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 -
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'
, runplesk db "create database mysql"
to recreate it and repeat above command. -
Remove the
skip-grant-tables
directive which was added on step 3. -
Start the MariaDB/MySQL service:
# systemctl restart mariadb
# systemctl restart mysql
Comments
0 comments
Please sign in to leave a comment.