Search code examples
mysqlxamppinnodb

InnoDB Corruption in Xampp

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.

Solution

  • 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.