MySQL Bugs: #25491: Detach and attach InnoDB tablespace files.

Bug #25491	Detach and attach InnoDB tablespace files.
Submitted:	9 Jan 2007 11:06	Modified:	4 May 2007 17:04
Reporter:	Kristian Koehntopp	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
A customer of mine wants to be able to move InnoDB tablespaces between different database servers. For this they require a facility to detach and attach table space files (ibd files and ibdata files) from a database.

They know about http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html, but note the first comment:

"  [Delete] [Edit]
  Note that the advice above regarding the DISCARD TABLESPACE and IMPORT TABLESPACE only works when you restore the .ibd file to the "installation from which it originated." If you try to copy ibd files into a new database instance you will get an error like this:

InnoDB: Error: tablespace id in file './test/<ibd_file_name.ibd>' is 38, but in the InnoDB data dictionary it is 401.

When you run:

ALTER TABLE <table_name> IMPORT TABLESPACE

It is possible to copy InnoDB tables between two databases using mysqldump and mysqlimport."

Also, there is no defined procedure to safely detach a tablespace file ("wait and hope that everything has been written") does not count.

How to repeat:
See http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html and try to detach a tablespace from mysqld, then import it to another mysqld.

Suggested fix:
Supply the suggested functionality.

Was this problem solved?

Bug #5904 is a duplicate of this.

http://www.percona.com/docs/wiki/percona-xtradb:patch:innodb_expand_import

See bug #56317 (More flexible InnoDB tablespace management) which is an extension of the request in this bug.

Two workarounds for people who need to move large chunks of data, want to avoid mysqldump and/or want to obtain client side data in a reusable format:

1. To dump, use SELECT INTO OUTFILE to a network-mapped path (i.e. SELECT * FROM <table> INTO OUTFILE "\\\\somehost\\somepath\\somefile.txt";), then re-import using LOAD DATA INFILE. Note the need for double escaping.

2. mysqldump --tab, then import using LOAD DATA INFILE or mysqlimport.
http://dev.mysql.com/doc/refman/5.1/en/mysqldump-delimited-text.html
http://dev.mysql.com/doc/refman/5.1/en/reloading-delimited-text-dumps.html

Justification for that feature request
-----
The problem is to move tables from A to B in a *FAST* way. That involves moving binary files including indexes, because most if the cost of that operation is index rebuild, a smaller part of the cost is parsing. Any solution that loads data in B at disk speed is valid.

There are a number of ways to dump and reload data from one server and load it into another server, and we have been going through them in this discussion. Some of these ways are partially flawed and can be improved, for example mysqldump not being able to write CSV files locally. All of these things would make nice feature requests.

The one thing that is a problem right now, and needs improvement rather urgently is the fact that there is no way to dump and load InnoDB tables in a way that does not involve parsing and, worse, rebuilding of the indexes.

So if you need to move a 100G InnoDB with 10 indexes from A to B right now, this is a multi-day operation keeping the server A busy for a comparatively small time (mysqldump can operate at disk speed) and keeping server B busy for a very very long time, because it needs to parse all the data and rebuild the indexes, all of which are likely not to fit into the InnoDB Buffer Cache (double so, if B is productive during the import).

This InnoDB table migration problem is what needs solving - rather urgently.

discussed workarounds:

1. mysqldump - parses and rebuilds indexes in B, not a good solution.
2. SELECT INTO OUTFILE and LOAD DATA INFILE - parses less, but still builds indexes. Better, but in no way good.
3. INSERT INTO localtable SELECT * FROM federatedtable - no parsing, but index rebuild. Even better, but in no way good.
4. Copying the tablespace with all InnoDB to B, then dropping tables not needed (recloning + pruning) - impossible, if B has tables that are still needed after the import, not a generically valid solution.

I too want option #4 -- native InnoDB support. I assume this requires the use of innodb-file-per-table. Otherwise, select into outfile or mysqldump are much better options than federated.The first two are proven and reliable.

Add my vote for getting this done.  And don't forget the original request http://bugs.mysql.com/bug.php?id=5904 by JD Duncan.

In terms of mysqldump --tab, see bug #56490

Isn't this fixed with the transportable tablespaces feature in 5.6?

http://dev.mysql.com/doc/refman/5.6/en/innodb-migration.html#copy-tables-transportable-tab...