Plesk is inaccessible and it is not possible to start MySQL/MariaDB: MySQL system database not found in /var/lib/mysql

Symptoms

• The following error is shown when trying to log into Plesk:

  PLESK_INFO: ERROR: Zend_Db_Adapter_Exception: SQLSTATE[HY000] [1049] Unknown database 'psa'
  Additionally, an exception has occurred while trying to report this error: Zend_Exception
  No entry is registered for key 'translate' (Abstract.php:144)
  ERROR: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [1049] Unknown database 'psa'' in /opt/psa/admin/externals/Zend/Db/Adapter/Pdo/Abstract.php:129
  Stack trace:
  #0 /opt/psa/admin/externals/Zend/Db/Adapter/Pdo/Abstract.php(129): PDO->__construct('mysql:dbname=ps...', 'admin', '$AES-128-CBC$41...', Array)
  #1 /opt/psa/admin/externals/Zend/Db/Adapter/Pdo/Mysql.php(111): Zend_Db_Adapter_Pdo_Abstract->_connect()
  #2 /opt/psa/admin/externals/Zend/Db/Adapter/Abstract.php(460): Zend_Db_Adapter_Pdo_Mysql->_connect()
  #3/opt/psa/admin/externals/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('select param, v...', Array)
  #4/opt/psa/admin/plib/Db/Adapter/Pdo/Mysql.php(30): Zend_Db_Adapter_Pdo_Abstract->query('select param, v...', Array)
  #5 /opt/psa/admin/plib/db.php(36): Db_Adapter_Pdo_Mysql->query('select param, v...')
  #6 /opt/psa/admin/plib/db.php(212): db_query('select param, v...', false)
  #7 /opt/psa/admin/plib/Plesk/Mode.php(439): get_param('disable_provisi...')
  #8 /opt/psa/admin/pl (Abstract.php:144)

• When trying to restore Plesk configuration database psa according to the article Cannot open Plesk: ERROR: PleskFatalException Unable to connect to database: Unknown database psa , it turns out that MySQL is down and it is not possible to start it with the systemctl start mysql.service command and the following error can be found in its log:

  # journalctl -u mysql.service
  ..........
  mysql-systemd-start[1294]: MySQL system database not found in /var/lib/mysql. Please run mysqld --initialize.

• Also, there is only one database directory (with some random name) in the MySQL data directory (by default, it is /var/lib/mysql/ ):

  # ls -al /var/lib/mysql/
  total 188500
  drwx------ 10 mysql mysql 4096 Aug 30 04:47 .
  drwxr-xr-x 65 root root 4096 Aug 17 17:15 ..
  -rw-r----- 1 mysql mysql 56 May 18 20:16 auto.cnf
  -rw-r--r-- 1 mysql mysql 0 Jul 25 12:42 debian-5.7.flag
  -rw-r----- 1 mysql mysql 1157 Aug 29 14:52 ib_buffer_pool
  -rw-r----- 1 mysql mysql 79691776 Aug 30 04:47 ibdata1
  -rw-r----- 1 mysql mysql 50331648 Aug 30 04:47 ib_logfile0
  -rw-r----- 1 mysql mysql 50331648 May 18 20:16 ib_logfile1
  -rw-r----- 1 mysql mysql 12582912 Aug 30 04:47 ibtmp1
  drwxr-x--- 1 mysql mysql 50331648 May 18 20:16 WARNING

• One of the files in this database directory containing the following contents:

  # ls -al /var/lib/mysql/WARNING/
  total 25736
  drwxr-x--- 2 mysql mysql 20480 Aug 15 05:54 .
  drwx------ 10 mysql mysql 4096 Aug 30 04:47 ..
  -rw-r----- 1 mysql mysql 8624 May 18 20:16 Readme.frm
  -rw-r----- 1 mysql mysql 98304 Aug 23 13:10 Readme.ibd
  # cat /var/lib/mysql/WARNING/Readme.ibd
  ..........
  Your DataBase is downloaded and backed up on our secured servers. To recover your lost data: Send X.X BTC to our BitCoin Address and Contact us by eMail with your MySQL server IP Address and a Proof of Payment. Any eMail without your MySQL server IP Address and a Proof of Payment together will be ignored. You are welcome.

Cause

MySQL server is hacked.

Resolution

1. Connect to the server using SSH .

2. Move all files from the /var/lib/mysql/ directory to some temporary location:

   # mv /var/lib/mysql/* /root/tmp

3. Initialize MySQL instance with the mysqld --initialize command.

4. Change the password for the MySQL user root from the random one generated during the initialization:

   # cat /etc/mysql/my.cnf
   ..........
   skip-grant-tables
   # systemctl restart mysql
   # mysql
   mysql> UPDATE mysql.user SET authentication_string = PASSWORD('password') WHERE User = 'root' AND Host = 'localhost';
   mysql> FLUSH PRIVILEGES;
   # cat /etc/mysql/my.cnf <--- skip-grant-tables removed
   # systemctl restart mysql
   # mysql -u root -p
   Enter password:
   mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

5. Check Plesk dump location, find the latest dump and unarchive it:

   # grep dump /etc/psa/psa.conf
   DUMP_D /var/lib/psa/dumps
   # ls -alt /var/lib/psa/dumps/mysql.daily.dump*
   -rw------- 1 root root 239658 Dec 10 03:37 /var/lib/psa/dumps/mysql.daily.dump.0.gz
   ..........
   # gunzip /var/lib/psa/dumps/mysql.daily.dump.0.gz

6. Copy the unarhived dump. From the copied file, delete all database from it except mysql :

   # cp /var/lib/psa/dumps/mysql.daily.dump.0{,-mysql}
   # vim /var/lib/psa/dumps/mysql.daily.dump.0-mysql

7. Restore MySQL system database mysql :

   # mysql -u root -p < /var/lib/psa/dumps/mysql.daily.dump.0-mysql

8. Set the password for the admin MySQL user to match the one stored in Plesk:

   # cat /etc/psa/.psa.shadow
   $AES-128-CBC$Abc1d2efg3hI4JKlMNopqr==$StUvwx5yZab67cD890eFgH==
   # mysql -u root -p
   Enter password:
   mysql> UPDATE mysql.user SET authentication_string = PASSWORD('$AES-128-CBC$Abc1d2efg3hI4JKlMNopqr==$StUvwx5yZab67cD890eFgH==') WHERE User = 'admin' AND Host = 'localhost';
   mysql> FLUSH PRIVILEGES;

9. Restore the rest of the databases from the dump:

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin < /var/lib/psa/dumps/mysql.daily.dump.0

Note: Using this solution, only the following databases will be restored: mysql , psa , horde , apsc , roundcudemail , phpmyadmin . Other databases need to be restored from the backup.

Note: To prevent further attacks on MySQL, consider the following actions: applying the article Plesk security best practices article, and, if necessary, consulting with a security firm about protecting your infrastructure.