Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Feb 4 at 9:12

This question came from our site for professional and enthusiast programmers.

1  
Just for the records: this should have been an SQL dump for manual migration. Moving/copying binary database files is usually a bad idea, esepcially when it comes to a more complex setup (specific storage engines, table spaces, partitioning etc.). –  Mike Lischke Feb 4 at 8:31

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
This means without the copy of ibdata1 file from the source database Im not going to get my copy working? Thank you for your time and answer! –  Huusslv Feb 4 at 7:57
    
@Huusslv This means without the copy of ibdata1 file from the source database Im not going to get my copy working? => Yes! –  InoS Heo Feb 4 at 7:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.