MySQL 5.5 : (InnoDB) - Manually edited an .ibd file to test recovery, now entire MySQL installation is broken!

Question

So I wanted to test MySQL recovery from Innodb corruption. file-per-table is enabled. I have a few test db's on the MySQL instance. I shutdown mysql. Manually edited/added random characters to an .idb file within a DB.
... I wanted to test credibility of innodbchecksum, mysqlcheck, etc.

MySQL started but shut down right away with errors. Only innodb-force-recovery=6 seemed to keep MySQL up.

I couldn't dump the corrupted DB at all. I couldn't select the table. if I tried MySQL crashed again. I also couldn't drop the table or database even. error.log mentioned something about not being able to remove the folder.

I manually did an rm on the entire db folder. edit: I understand it may be impossible to get the data back after wrecking the .idb file, I am only interested in bringing MySQL back to normal status.

MySQL seemed to have started but with errors everywhere in the log saying:

121122 17:34:01  InnoDB: Error: page 7 log sequence number 1 2040399532
InnoDB: is in the future! Current system log sequence number 0 8204.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files...

So my question is, how can this MySQL instance be recovered? If an .idb file is corrupted due to disk error or such, is the entire MySQL instance hosed? is there any recovery from this?

Edit: there must be a way of forcing drop a corrupted DB or even table without breaking the tablespace.

Answer 1

Following these steps probably offers a decent shot at making the MySQL instance happy again...

1. recreate the database's directory that you deleted
2. create an InnoDB table with the same exact column names and definitions and the same table name, but in a different schema (or on a different server)
3. copy only the .frm file for that table into the database's directory, not the .ibd file
4. be sure the file and directory permissions are correct
5. start MySQL normally (without innodb-force-recovery)
6. DROP TABLE database_name.table_name;

The server should then respond by removing the .frm file, removing the table from InnoDB's internal data dictionary, and return a simple warning that the .ibd file was missing.

You'll have to repeat the process for all of the tables, but if you have the schema on another machine (or identical tables in another schema on this one, already) you should be able to copy all the .frm files over in one shot, start up, then drop them all.

But don't try opening them, don't "USE database_name", and don't connect with a graphical client that tries to enumerate the tables. Start the command line client with the --no-auto-rehash option so it doesn't try to enumerate them in the background, either.

This should let you drop the tables, at which point, you may be stable again.