23

Assume that the following MySQL files have been restored from a backup tape:

  • tablename.frm
  • tablename.ibd

Furthermore, assume that the MySQL installation was running with innodb_file_per_table and that the database was cleanly shutdown with mysqladmin shutdown.

Given a fresh install of the same MySQL version that the restored MySQL files were taken from, how do I import the data from tablename.ibd/tablename.frm into this new install?

knorv
  • 49,059
  • 74
  • 210
  • 294
  • I`m not sure it's possible as even when innodb is using a file per table, there is still some crap in the central idbdata file which is linked to the existing tables. – Omry Yadan Apr 11 '10 at 12:00
  • Omry: Technically it should be possible AFAIK. Please feel free to prove me wrong :-) – knorv Apr 13 '10 at 12:35
  • 3
    This doesn't belong here, it's not programming-related. Go to the DBA Exchange. There are several similar questions there already... http://dba.stackexchange.com/questions/16875/mysql-how-to-restore-table-stored-in-a-frm-and-a-ibd-file – Cylindric May 23 '14 at 15:22
  • This question appears to be off-topic because it is about Database Administration, belongs on DBA and is a direct duplicate of http://dba.stackexchange.com/questions/16875/mysql-how-to-restore-table-stored-in-a-frm-and-a-ibd-file – Cylindric May 23 '14 at 15:22
  • An answer [suggests this resource](http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file), so I will add it here for reference, and report the answer as link-only. – halfer Sep 17 '16 at 15:51

4 Answers4

3

Similar thing is answered here. please have a look https://stackoverflow.com/a/10943833/3985205

Yes this is possible. It is not enough you just copy the .frm files to the to the databse folder but you also need to copy the ib_logfiles and ibdata file into your data folder. I have just copy the .frm files and copy those files and just restart the server and my database is restored.

Community
  • 1
  • 1
Snehasish Sarkar
  • 134
  • 10
0

You must bring the table into the tablespace:

https://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/restoring_individual_tables_ibk.html

ALTER TABLE mydatabase.tablename IMPORT TABLESPACE;

There is also a exp/cfg file you must have.

Sergiy Tytarenko
  • 472
  • 7
  • 17
0

I posted the correct solution when you lost ib_logfiles and ibdata file and you have only .frm and .idb files.

Please take a look at my comment on this link:

Restore table structure from frm and ibd files

This will work for you, too.

Thank you.

Jin Lin
  • 526
  • 5
  • 4
-1

This not may work in all cases. But yes it is working in some case...

First stop all the services related to wamp or xampp which you use.

Then copy and past the tablename.frm, tablename.ibd

files under mysql/data/DATABASE NAME FOLDER. then start all the services again. the tablet created under that DB.

Nandit
  • 91
  • 7
  • You can only do that with MyISAM table type (tablename.frm tablename.MYD tablename.MYI) InnoDB will only show the table names with no content. InnoDB table type is securely complex. help link: http://stackoverflow.com/questions/5745221/import-frm-and-opt-files-to-mysql – Timothy Nwanwene Jan 09 '17 at 11:03