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

• 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 command 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, one of the following symptoms can be observed:

  • Example 1

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

  • Example 2

    • In the MySQL data directory, all files of each database have the extension .Crypt:

      # grep datadir /etc/my.cnf
      datadir=/var/lib/mysql
      
      # ls -al /var/lib/mysql/psa/
      total 3096
      drwx------ 2 mysql mysql 20480 Dec 17 08:35 .
      drwxr-xr-x 116 mysql mysql 4096 Dec 17 12:28 ..
      -rw-r--r-- 1 mysql mysql 9017 Dec 17 08:35 accounts.frm.Crypt
      -rw-r--r-- 1 mysql mysql 9049 Dec 17 08:35 actions.frm.Crypt
      -rw-r--r-- 1 mysql mysql 9161 Dec 17 08:35 admin_aliases.frm.Crypt
      -rw-r--r-- 1 mysql mysql 9017 Dec 17 08:35 admin_aliases_param.frm.Crypt
      -rw-r--r-- 1 mysql mysql 9225 Dec 17 08:35 ai_vendor_sources.frm.Crypt
      ..........

    • In the directory /root/, the following files with the text similar to Readme.ibd from example 1 can be found:

      # ls -lt /root/*.TXT
      -rw-r--r-- 1 root root 1658 Dec 17 08:24 /root/RECOVER_YOUR_FILES.TXT
      -rw-r--r-- 1 root root 1658 Dec 17 08:24 /root/DECRYPT_YOUR_FILES.TXT
      -rw-r--r-- 1 root root 1658 Dec 17 08:24 /root/URGENT.TXT

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, roundcubemail, phpmyadmin. Other databases need to be restored from a 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.