• 2019-05-04 06:59:15

Start MySQL/MariaDB in recovery mode due to InnoDB corruption on cPanel

Start MySQL/MariaDB in recovery mode due to InnoDB corruption on a cPanel server

 

InnoDB tables don’t get corrupted easily, but when they do, it usually happens because of hardware issues, power outages or MySQL bug. It leaves you with corrupted pages in InnoDB tablespace and recovering from that might be problem.

Step 1 – Bring up your database in recovery mode:

You'' first need to stop MySQL, however as last resort, you may also kill the process. In order to bring back your database you will need to start it in recovery mode, with innodb_force_recovery value set in the /etc/my.cnf file. You should know this recovery mode makes your databases read only. If you are able to dump your tables with an innodb_force_recovery value of 3 or less, then you are relatively safe that only some data on corrupt individual pages is lost. A value of 4 or greater is considered dangerous because data files can be permanently corrupted. A value of 6 is considered drastic because database pages are left in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.

vi /etc/my.cnf

innodb_force_recovery=1

Step 2 – Check which tables are corrupted and make a list

We need to find out which tables got corrupted. In order to do that, we execute:

mysqlcheck --all-databases

Step 3 – Backup and drop your corrupted tables

mysqldump --all-databases > all.sql

Have in mind that you might need to force the mysqldump, in case there are broken tables you can first try to repair them but most of them will most likely not support repair so you can skip them with the force command. If one of them is corrupted you can skip that as well by moving its folder away from /var/lib/mysql) and import all others.

Step 4 - Stop the MySQL

service mysql stop

Step 5 - Backup the /var/lib/mysql and create new directory to start from scratch

Backup the mysql folder

mv /var/lib/mysql /var/lib/mysql-bak

Recreate the folder and give it the right ownership

mkdir /var/lib/mysql

chown mysql:mysql /var/lib/mysql

Copy the /var/lib/mysql/mysql from the backup to the new instance of the folder and chown it recursively

cp /var/lib/mysql-bak/mysql /var/lib/mysql/mysql/

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

Step 6 - Comment out recovery mode from /etc/my.cnf and start MySQL

vi /etc/my.cnf

#innodb_force_recovery=1

Step 7 - Import the databases back from the mysqldump file you've created at the start. Have in mind that you might need to use -f again if there are errors during the process (however please use it as a last resort option)

mysql < all.sql

 

Everything should be fine and MySQL should be now up and running just fine.

 

---

Author:
Alex G
System Administrator
Website
Linkedin