How to fix Corrupt InnoDB Database

Sometimes, power outage or software & hardware malfunction corrupt the MySQL database. MySQL database mostly used two database engines to store data: MyISAM & InnoDB. MySQL database mostly get corrupted either by MyISAM corruption or by InnoDB corruption. MyISAM corruption is easy to fix & I have already described it in this post. This article will provide all the steps to recover corrupted InnoDB database.

InnoDB database engine provides transaction safe database within MySQL database. You can review the MySQL log file and confirm that database is not starting due to InnoDB corruption or some other reasons as well as find the solution to fix it.

Log File always guide for recovery:

First check log file by using UNIX command tail & check what’s written in the log file. There are two possibilities:

• Either it shows the error message like “Ending processing because of a corrupt database page”

                                            Or

• Suggest solution for checking actually where corruption exists.

Possibilities Suggestions:

1) In first case, you may check that the database get corrupt and it can be recover by proper recovery steps.

2) In second case the log file provide links of online resource containing more information about steps to recovery.

But the second case occurs very rare & it helps only in some corruption cases. Check following steps to resolve severe corruption:

Step 1: Add this line to your /etc/my.cnf configuration file:

[mysqld]
innodb_force_recovery = 4 

Step 2: Restart MySQL.

Step 3: Dump all tables

Step 4: Stop mysqld and delete the data directory. Run mysql_install_db to create MySQL default tables.

Step 5: Remove the innodb_force_recovery line from your /etc/my.cnf file and restart the database.

Step 6: Restore everything from the dump.

If neither of the two options is able to solve your problem & you also forgot to create backup. In this case you should use third party MySQL Database Recovery Software to recover corrupt InnoDB tables. Surely, the software recover corrupt InnoDB database without losing your important data.