0

This question already has an answer here:

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?

marked as duplicate by RolandoMySQLDBA innodb Aug 5 '14 at 14:21

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • @RolandoMySQLDBA Actually,I don't think it is a duplicate because when ibdata1 was removed, I don't think I had any .ibd files due to my settings. I have since recovered the tables from backup so my problem is solved. Nevertheless, your answer to the other question looks useful, so thanks. – Tom Fenech Aug 5 '14 at 14:29
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

  • Thanks, but I don't have any .ibd files due to my settings at the time of deletion – Tom Fenech Apr 25 '14 at 18:43
  • omg, i misread your Q. Okay, then skip to #3. umount partition where ibdata1 was or stop all writes. Then scan the partiton with page_parser and then fetch recrods with constraints_parser – akuzminsky Apr 25 '14 at 18:47
  • Thanks, I'll give it a go but I think it's probably too late as the blunder was committed two days ago now. – Tom Fenech Apr 25 '14 at 18:52
  • of course, the sooner your stop writes the bigger chance to get your data back. Still I'd give a try page_parser. All it finds is recoverable, so it can give you an idea if it's worth efforts – akuzminsky Apr 25 '14 at 18:54

Not the answer you're looking for? Browse other questions tagged or ask your own question.