Friday, October 19, 2012

How to Repair corrupt MySQL InnoDB database that won't start

One of my blog readers (Rozer) is managing their own MySQL database. Now he is unable to start MySQL Server. He asked to me to look out the issue and see if I could give some suggestion.

"Live Scenario: Mysql is not start because InnoDB is corrupt:

I have full backup, Please suggest me if there is any possible solution to repair my database!!"

As the error itself says that InnoDB database get corrupted. After researching on this error, I found some good solution of this error. Here is all conversation between me & Rozer: 

Me: Do you try innodb_force_recovery = 1 to 6?

You should try all these six options in an increasing order of 1,2...6. Or you can just try 6 directly. if you have not tried then i will suggest you to try in increasing order and see if you can start mysqld service. 

Rozer: Yes I tried !! It is not work!! 

Me: Are there any relevant errors in mysql's error log? cat /var/log/mysqld.log

Rozer: NO!! I did not find Anything!!

Me: Thats good that you have full backup of your database. In this case, you can delete the ibdata and ib_logfile and recreate them. After that you can easily restore your databases. This will resolve any corruption errors that you may have in the innodb files.

Please follow these steps: 

Rozer: It works.. Thanks Adam for helping me!!

Me: I am happy that you got back your database but there are some other situations in which you are unable to repair your database by using above steps. In some cases such as if database get severely corrupted & all method does not work well  or You don't have backup & want to repair your database. In such cases, You should use MySQL InnODB Database Recovery Software. These software works well even all methods get failed to repair database. 

Rozer: Thanks for the informing me about such a nice tool.

I will definitely suggest to all users to update your backup on weekly basis!! 

No comments:

Post a Comment