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
Recent Posts

How DigitalOcean Simplifies Cloud Computing for Developers
2023-01-18 12:35:28
How to Get Current Route Name in Laravel
2020-11-08 08:57:11
How to check the logs of running and crashed pods in Kubernetes?
2020-10-28 09:01:44
Top 10 VScode Shortcuts For Mac and Windows to Help You be More Productive
2020-10-28 07:12:51