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

Resolution

Note: Since the MySQL service's control, logs and configuration file's location is different on the different operating systems, this article provides general command examples only. Check the following article for additional information regarding MySQL on different operating systems
[Linux] Local MySQL server for all databases
For additional information, check your operating system and MySQL/MariaDB documentation.

Note: for Plesk on Windows, see the following article: How to fix InnoDB corruption cases for the MySQL databases on Plesk for Windows?

1. Connect to the server using SSH.

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

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

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

   For example:

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

4. Add the parameter innodb_force_recovery to the section [mysqld] of the MySQL configuration file. This option allows starting MySQL service in the recovery mode and try creating dumps of databases.

   For example:

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

5. Start the MySQL service.

   • If the service fails to start, set the value of the parameter innodb_force_recovery to a greater value and try starting MySQL 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 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 using steps from the below section Restore databases from backups and do not apply steps 6-8.

   Restore databases from backups

   1. Check which engine (MyISAM or InnoDB) is used by the database mysql and proceed based on the output of this command:

      # ls -l /var/lib/mysql/mysql/*.ibd

      • If the output is empty (MyISAM engine is used):

        1.1.1. Remove all the MySQL data storage files except the mysql folder. For example:

        # rm -rf `ls -d /var/lib/mysql/* | grep -v "/var/lib/mysql/mysql"`

        1.1.2. Remove the option innodb_force_recovery from the MySQL configuration file.

        1.1.3. Restart the MySQL service:

        # service mysqld restart

      • If the output is not empty and contains .ibd files (InnoDB engine is used):

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

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

        1.2.2. Remove the option innodb_force_recovery from the MySQL configuration file.

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

        1.2.4. Set correct ownership for the MySQL data directory:

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

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

        # service mysqld restart

   2. Restore the database mysql and Plesk system databases (psa, apsc, roundcubemail, horde, phpmyadmin) from a daily/pre-upgrade dump.

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

      # service mysqld restart

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

6. 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 MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -uadmin "$i" --routines --databases > /root/db_backup/"$i".sql; echo $i; sleep 5; 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 the only remaining method is to restore databases as described in the section Restore databases from backups in step 5, do not apply steps 7-8 in this case.

7. Check which engine (MyISAM or InnoDB) is used by the database mysql and proceed based on the output of this command:

   # ls -l /var/lib/mysql/mysql/*.ibd

   If the output is empty (MyISAM engine is used)

   7.1. Remove all the MySQL data storage files except the mysql folder. For example:

   # rm -rf `ls -d /var/lib/mysql/* | grep -v "/var/lib/mysql/mysql"`

   7.2. Remove the option innodb_force_recovery from the MySQL configuration file.

   7.3. Restart the MySQL service:

   # service mysqld restart

   7.4. Check the MySQL log file for any errors.

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

   If the output is not empty and contains .ibd files (InnoDB engine is used)

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

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

   7.2. Remove the option innodb_force_recovery from the MySQL configuration file.

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

   7.4. Set correct ownership for the MySQL data directory:

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

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

   # service mysqld restart

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

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

   # service mysqld restart

8. Start the service plesk-web-socket:

   # service plesk-web-socket start