MySQL Forums
Forum List  »  InnoDB

Re: Importing Data with Single .ibd file
Posted by: Rick James
Date: September 07, 2014 12:06PM

With MyISAM, three files constitute all the info about a table: .MYD (data), .MYI (index, and reconstructable), and .frm (schema). Certainly if mysqld is shutdown, those 3 files are transportable. If you leave mysqld running it is trickier. IF you stop activity on the table, perhaps doing FLUSH TABLES WITH READ LOCK, you can copy those three files between mysql instances. Even so, do CHECK TABLE, pluse REPAIR TABLE if so indicated.

InnoDB is a different matter. Even with file_per_table, the .ibd (data+indexes) and .frm (schema) are not the entire knowledge that the system has of the table. There is some info hidden away in ibdata1. Search for "transportable tablespaces"; this is a new feature of MySQL/InnoDB wherein you can run a command to prepare an InnoDB table (or even PARTITION) for moving to another system. Then on that new system another command will hook it in (probably fiddling with ibdata1).

PARTITION tricks work something like: You can (in newer versions) convert a partition to a table and vise versa. Then transporting depends on being able to move the table.

MERGE, Fabric, etc -- These add further complications.

Options: ReplyQuote


Subject
Views
Written By
Posted
2669
September 06, 2014 04:12AM
Re: Importing Data with Single .ibd file
1392
September 07, 2014 12:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.