After a some backup operations we have lost an ibdata1 file. We have an option innodb_table_per_file enabled in our my.cnf, so we have all .frm and .ibd files not corrupted. When we tried to start database mysql shows next messages:
080724 13:46:28 InnoDB error:
Cannot find table Spot/AsearchChangeLog from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem.
080724 13:46:28 [ERROR] /usr/sbin/mysqld: Can’t open file: ‘AsearchChangeLog.ibd’ (errno: 1)
How can we recover our data?
I had used instructions found here but mysql gave me another error when i tried to IMPORT TABLESPACE:
80724 16:54:55 InnoDB: Error: tablespace id in file ‘./Spot/AsearchChangeLog.ibd’ is 656314, but in the InnoDB
InnoDB: data dictionary it is 1.
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_dat adict.html
InnoDB: for how to resolve the issue.
080724 16:54:55 InnoDB: cannot find or open in the database directory the .ibd file of
InnoDB: in ALTER TABLE … IMPORT TABLESPACE
How can I avoid this and import tablespace?
I know information schema for my database and tables, i know all tables names, rows and their sizes. How can I extract data from an ibd file? That should be enough for me.