Announcement

Announcement Module
Collapse
No announcement yet.

Recovery after ibdata1 delete.

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Recovery after ibdata1 delete.

    Hello, All.

    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: table `Spot/AsearchChangeLog`
    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.
Working...
X