0

I just upgraded from Debian Squeeze to Wheezy and mysql stopped working. Blindly following some advice without any understanding of what I was doing, I removed ibdata1 in an attempt to solve the problem. Unfortunately, it was only after doing so that I realised this was a terrible idea, especially as I was missing this option in my.cnf: innodb_file_per_table = 1.

I found the cause of my problem and now my DB server is working again. However, now, I am left with a mess. My problem is that some (but not all) of my tables, which appear when I do show tables;, are giving me this error:

ERROR 1146 (42S02): Table 'db.tablename' doesn't exist

I have since enabled the setting innodb_file_per_table = 1 (perhaps this was a bad idea?) and before I came to realise that I probably didn't have a chance, I was trying out the innodb_force_recovery flags.

I'm fairly confident that these tables are gone for good so I'm trying to restore my database from earlier dumps. The problem is that the import breaks on these tables that "do but don't" exist. I have tried dropping the database before doing the import but there are clearly some other files lying around. Needless to say I'm reluctant to do any more speculative deletion of files...

Any advice on how to clean up this mess?

Community
  • 1
Tom Fenech
  • 101
  • 1
  • 2

1 Answers1

0

THere are three ways to recover your tables:

  1. http://www.mysqlperformanceblog.com/2011/05/13/connecting-orphaned-ibd-files/
  2. http://www.chriscalender.com/?p=28
  3. Extract records from the ibd files with page_parser/constraints_parser from Data Recovery Toolkit

UPDATE: Data recovery toolkit moved to GitHub

akuzminsky
  • 4,997
  • 15
  • 16