Bobby's Blog

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


Start MySQL/MariaDB in recovery mode due to InnoDB coruption 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.

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:

Step 3 – Backup and drop your corrupted tables

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

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

Backup the mysql folder

Recreate the folder and give it the right ownership

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

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

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)

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


Author:
Alex G
System Administrator
Website
Linkedin

About the author

Alex G.