InnoDB Corruption in Xampp

Question

I have a backup from two weeks ago that will be a last resort, but the DB files themselves seem fine and it appears it's just the ibdata1 file that is having issues. As stated, I'm using Xampp and MySQL crashes right after I start it. Here is the error log: http://textuploader.com/7vfd

I hadn't done anything out of the ordinary; it seemingly just up and stopped working.

I looked up InnoDB recovery, but the solutions I tried required MySQL to be functional, which it isn't due to the corruption. Is there a way to salvage what are presumably intact IBD files with a bad ibdata1 file?

Edit: I was aware of using innodb_force_recovery = # and I had tried it...except I had tried it in the wrong my.ini. I had to use the one in the bin folder. It appears to be fixed now.

Answer 1

The ibdata1 file contains the tablespace information and other metadata about your Mysql database(s).

You can try using the innodb_force_recovery = 1 all the way to innodb_force_recovery = 6 to see if that rectifies the problem. Try changing this in your my.cnf (my.ini for windows) file and then attempt to restart your mysql server again.

If you are able to start Mysql using recovery flag, your database will be in a read-only mode. You should do a mysqldump of the data, stop mysql, re-install fresh, create your database again, and import back in the data.

Here is a link for more info on innodb recovery dev.mysql.

If you use linux...

Another more complex option is to use percona recovery tool kit. This will realign your tablespaces. Although, from experience its a bit of a challenge to navigate and takes a bit of time to implement if you are a newb.

However, akuzminsky the creator of the toolkit (how cool is that!) mentioned that he has made significant improvements to the toolkit.

Link to download toolkit Percona.com

Link with a walkthough from chriSQL.

Link to akuzinsky's website TwinDB.

Unless that data is mission critical, I would just revert back to the backup from 2 weeks prior. The amount of time and effort you may end up putting into recovering this data may out way the benefit.

Answer 2

In my case, the mysql.ini didn't have the innodb_force_recovery option. So i added it to the file and the start was able to start normally

The location to the ini file for my case was

C:\xampp\mysql\bin\my.ini

innodb_force_recovery = 1

For my case, the data got corrupted after a disk check up by windows. In the due course, the database got corrupted.

Hope this can as well help someone out there.