Description:
In the good old days we'd import MyISAM tables from one server to another using rsync or something simple. It was simple and worked.
InnoDB changed all that and it's been hard to do the same.
We've had: http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html for some time but this is really rather awkward.
* I have to have the definition of the table in order to import the data
* I think this no longer works if the source table has old temporal columns as I can't create those on a current 5.6 or 5.7 server.
* etc
The other use case of loading in a table is because I broke something and I want to recover it from backup. We may have a backup of the filesystem, or something done by xtrabackup or I guess mysqlbackup does the same thing.
How to repeat:
Basically If I have a .frm file and the relevant .ibd files that it refers to it would like to just "import them" into the current server.
* The _exact_ definition is in the .frm file as it came from the original server. So I shouldn't have to jump around the hoops needed to create an empty table and then drop the tablespace which the transportable tables approach requires.
* If the table on the destination server is broken or gone I don't care about replacing it or wiping it out.
* Yes some sort of validation may be a good idea. You do this already with transportable tablespaces, so I don't see this requires anything very new.
Suggested fix:
So I'd like some sort of SQL command such as:
LOAD TABLE <name> FROM FORM_FILE 'path/to/mytable.frm', IBD_FILE '/path/to/mytable.ibd';
If I need to ensure that the files are located in $datadir/$db that's fine.
If this takes some time (the transportable tablespace method unfortunately rewrites each page on load to the current new talbespace_id which can be slow for large tables) that's also ok.
All of this is "simpler to understand", "easy for someone to use" and practical from a:
(1) copy the files from somewhere else, or
(2) use a backup copy of the table files
point of view.
It can't really be very different from the current behaviour of TRANSPORTABLE TABLESPACES but it avoids all the messy "define by hand"
Note: there's a .cfg file that transportable tablespaces uses. How is this different to a .frm file and if you can use a .cfg file the other option would be to allow the current commands to use the .frm file and not bother to force you to create a table and drop the tablespace but do all of this in one easy go.
I've been bitten recently by a couple of failures which required me to collect the data from another source. I had a backup but no existing .cfg file and so the transportable tablespace option was really not usable as I didn't have the required info to create the table. I did have the relevant .frm and .ibd files from a recent backup but couldn't use them directly and this required me poking around for longer than necessary to fix the problem.
So a more flexible tool to allow the import of a new table directly from the .frm or .ibd files would be really useful if possible.
© 2025, Oracle Corporation and/or its affiliates