How to fix InnoDB corruption cases for the MySQL/MariaDB databases on Plesk for Linux?

Stop the affected MySQL/MariaDB service and the service plesk-web-socket to prevent it from attempting to start MySQL/MariaDB:

# systemctl stop mariadb plesk-web-socket

Back up all the MySQL/MariaDB data storage files. By default, they are located in the directory /var/lib/mysql/.
For example: \

# cp -a /var/lib/mysql /root/mysql_backup

Add the parameter innodb_force_recovery to the section [mysqld] of the MySQL/MariaDB configuration file. This option allows starting MySQL/MariaDB service in the recovery mode and try creating dumps of databases.
For example: \

# vi /etc/my.cnf
[mysqld] 
innodb_force_recovery = 2

• If the service fails to start, set the value of the parameter innodb_force_recovery to a greater value and try starting MySQL/MariaDB again.
  Value of the parameter innodb_force_recovery can be from 1 to 6.

  Warning: Only set innodb_force_recovery to a value greater than 0 in an emergency situation, so that you can start InnoDB and dump databases. Values of 4 or greater can permanently corrupt data files. Therefore, increase this value incrementally, as necessary. See more details in the official MySQL Documentation.

• If the service fails to start with an error like:

  InnoDB: Waiting for the background threads to start

  Then add the parameter innodb_purge_threads as described in the following article: Unable to start MySQL/MariaDB service: InnoDB: Waiting for the background threads to start.

• If the service fails to start even with innodb_force_recovery set to the highest possible value of 6, restore the databases from backups/dumps by using steps from Restore databases from backups subsection of the this article

Try dumping  all databases:

# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -Ns -uadmin psa -Ne"show databases" | grep -v information_schema | grep -v performance_schema > /root/db_list.txt
# mkdir /root/db_backup/ 
# cat /root/db_list.txt | while read i; do echo $i; MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -uadmin "$i" --routines --databases > /root/db_backup/"$i".sql; done

• If dumping fails with an error like:

  Incorrect information in file: './psa/APSApplicationItems.frm' when using LOCK TABLES"

  then increase the value of innodb_force_recovery, restart MySQL service, and try to dump the databases again. It is better to dump databases one by one, separately. In that case, there is no need to go through restore of all databases once again if restore failed for some reason.
  If creating a dump of some or all databases fails, then switch to Restore databases from backups subsection of the this article and do not apply further steps from this subsection

Remove all files and directories from the MySQL data directory. For example:

# rm -rf /var/lib/mysql/*

Create an empty mysqldatabase using ONE of the below commands:

# mysql_install_db --force

# mysqld --initialize

# mysql_secure_installation

Note: the exact command depends on the installed MySQL/MariaDB version, check MySQL/MariaDB documentation for information on this.

Set correct ownership for the MySQL data directory:

# chown -R mysql:mysql /var/lib/mysql

Add the parameter skip-grant-tables to the MySQL configuration file and restart MySQL to apply the change:

# systemctl restart mariadb

Restore databases from the dumps made in step 6. For example:

# for db in `cat /root/db_list.txt`; do echo -e "Importing $db..."; MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin < /root/db_backup/$db.sql; done

Remove the parameter skip-grant-tables from the MySQL configuration file and restart MySQL to apply the change:

# systemctl restart mariadb

Start the service plesk-web-socket:

# systemctl start plesk-web-socket

Log into the server via SSH.

Note: in case you have already created backup for /var/lib/mysql directory in subsection Manual SSH solution, skip steps №2 and №3

Stop the affected MySQL/MariaDB service and the service plesk-web-socket to prevent it from attempting to start MySQL/MariaDB:

# service mysql stop || service mariadb stop && service plesk-web-socket stop

Back up all the MySQL/MariaDB data storage files. By default, they are located in the directory /var/lib/mysql/.

# cp -a /var/lib/mysql /root/mysql_backup_dir

Remove all files and directories from the MySQL data directory.

# rm -rf /var/lib/mysql/*

Create an empty mysql database using ONE of the below commands:

# mysql_install_db --force

# mysqld --initialize

# mysql_secure_installation

Note: the exact command depends on the installed MySQL/MariaDB version, check MySQL/MariaDB documentation for information on this.

Set correct ownership for the MySQL data directory:

# chown -R mysql:mysql /var/lib/mysql

Add the parameter skip-grant-tables to the MySQL configuration file and restart MySQL to apply the change:

# systemctl restart mysqld

Remove the parameter skip-grant-tables from the MySQL configuration file (if it was added in step 8) and restart MySQL to apply the changes:

# service mysqld restart

Restore websites' databases from a Plesk backup (instructions can be found either in the documentation or in the knowledge base article) or from manually created dumps.

Note: If timeouts are encountered when restoring databases, set the wait_timeout value in the MySQL configuration file and restart the MySQL service. For example:

# vi /etc/my.cnf
​[mysqld] 
​wait_timeout = 1800