Mysql – Copying MySQL tables, InnoDB tables not recognized

innodbMySQLmysql-workbench

I have to set up a database copy on my local computer.
The problem is that that I have given just the raw database table files (.frm, .ibd, .MYD, .MYI).

I created the database by creating a folder in MySQL's data folder and moved all database files there.

The problem is that some tables seem to work correctly the ones with MyISAM storage engine
(.frm, .MYD, .MYI), I can access information in them, see their structure.

My problem is with the tables with InnoDB storage. I cant see their structure in MySQL workbench and when I try to select data from them MySQL spits out an error "Table 'table_name' doesn't exist".

There are two files per one InnoDB table (.frm and .ibd).

To fix this problem I tried to enable innodb_file_per_table, I added to my.ini file in MySQL root folder line innodb_file_per_table = TRUE but this does not solve anything.

I checked working InnoDB tables in my other MySQL databases and there are the same two files per table (.frm and .ibd).

I will include bellow screenshots of what I described for more clarity

The problematic table example:

http://imageshack.com/a/img837/3878/0jl5.png

Ok table example:

enter image description here

Could someone help me out please, Im hoping I was descriptive enough.

Best Answer

While .frm, .MYD, .MYI are enough for MyISAM, ibdata1 (InnoDB Global Table space) is needed for InnoDB even if you are using innodb_file_per_table = TRUE.

ibdata1 holds meta data for InnoDB Tables. InnoDB Table can't be found without those meta data. So you are getting "Table 'table_name' doesn't exist". error.

MySQL Manual states that

You cannot freely move .ibd files between database directories as you can with MyISAM table files. The table definition stored in the InnoDB shared tablespace includes the database name. The transaction IDs and log sequence numbers stored in the tablespace files also differ between databases.

The main reason is Table id is different both source and dest. DB's ibdata1. So ibdata1 should be moved from src to dest.

Related Question