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: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S4 (Feature request) |
Version: | OS: | Any | |
Assigned to: | Assigned Account | CPU Architecture: | Any |
[9 Jan 2007 11:06]
Kristian Koehntopp
[6 Aug 2008 3:45]
ws lee
Was this problem solved?
[2 Jun 2010 6:30]
Marko Mäkelä
Bug #5904 is a duplicate of this.
[2 Jun 2010 6:41]
MySQL Verification Team
http://www.percona.com/docs/wiki/percona-xtradb:patch:innodb_expand_import
[27 Aug 2010 0:13]
Roel Van de Paar
See bug #56317 (More flexible InnoDB tablespace management) which is an extension of the request in this bug.
[30 Aug 2010 8:35]
Roel Van de Paar
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
[7 Sep 2010 12:44]
Axel Schwenke
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.
[7 Sep 2010 13:37]
Mark Callaghan
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.
[29 Sep 2010 15:35]
Sheeri Cabral
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.
[5 Jan 2011 1:08]
Roel Van de Paar
In terms of mysqldump --tab, see bug #56490
[15 Nov 2013 15:31]
Daniël van Eeden
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...