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.
Best Answer
Following these steps probably offers a decent shot at making the MySQL instance happy again...
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.