MySQL Forums
Forum List  »  Data Recovery

recovering tables without having the shared datafile
Posted by: Thijs Feryn
Date: December 12, 2007 11:06AM


I've read a bunch of articles about the "innodb_file_per table" parameter, but I was wondering if one could recover a database without having the shared ibdata1 data file. In this hypothetical case (which could become real in some point of time) I would have the .frm and .ibd files.

I can imagine certain cases where one would like to move a single innodb database or table without having to move the entire ibdata1 file.

Anyone here has some experience with that?

I've tried dozens of combinations where I would use the "alter table ... import tablespace" to get an old backup of my .ibd file back online.

I also tried discarding my tablespace, dropping the table, removing the ibdata file, restarting the server and recreating the table using a "CREATE TABLE" statement (so far so good). But the final step where I put my old .ibd file and import my table space goes horribly wrong.

90% of al my actions resulted in an error from the storage engine, but the last test gave no error, but resulted in an empty result when I queried the records.

I someone would be able to give me a valid solution, I would not only have an answer to my backup/recovery question, but also a magnificent solution for shrinking my ibdata1 file for tables that were created prior to my "innodb_file_per_table" setting.

Thanks in advance.
Kind regards,

Thijs Feryn

Options: ReplyQuote

Written By
recovering tables without having the shared datafile
December 12, 2007 11:06AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.