15.6.3.7 Copying Tablespaces to Another Instance

Contents

This section describes how to copy a file-per-table tablespaces from one MySQL instance to another, otherwise known as the Transportable Tablespaces feature. This feature also supports partitioned InnoDB tables and individual InnoDB table partitions and subpartitions.

For information about other InnoDB table copying methods, see Section 15.6.1.2, “Moving or Copying InnoDB Tables”.

There are many reasons why you might copy an InnoDB file-per-table tablespace to a different instance:

To run reports without putting extra load on a production server.
To set up identical data for a table on a new slave server.
To restore a backed-up version of a table or partition after a problem or mistake.
As a faster way of moving data around than importing the results of a mysqldump command. The data is available immediately, rather than having to be re-inserted and the indexes rebuilt.
To move a file-per-table tablespace to a server with storage medium that better suits system requirements. For example, you may want to have busy tables on an SSD device, or large tables on a high-capacity HDD device.

Limitations and Usage Notes

The tablespace copy procedure is only possible when innodb_file_per_table is enabled, which is the default setting. Tables residing in the shared system tablespace cannot be quiesced.
When a table is quiesced, only read-only transactions are allowed on the affected table.
When importing a tablespace, the page size must match the page size of the importing instance.
ALTER TABLE ... DISCARD TABLESPACE is supported for partitioned InnoDB tables, and ALTER TABLE ... DISCARD PARTITION ... TABLESPACE is supported for InnoDB table partitions.
DISCARD TABLESPACE is not supported for tablespaces with a parent-child (primary key-foreign key) relationship when foreign_key_checks is set to 1. Before discarding a tablespace for parent-child tables, set foreign_key_checks=0. Partitioned InnoDB tables do not support foreign keys.
ALTER TABLE ... IMPORT TABLESPACE does not enforce foreign key constraints on imported data. If there are foreign key constraints between tables, all tables should be exported at the same (logical) point in time. Partitioned InnoDB tables do not support foreign keys.
ALTER TABLE ... IMPORT TABLESPACE and ALTER TABLE ... IMPORT PARTITION ... TABLESPACE do not require a .cfg metadata file to import a tablespace. However, metadata checks are not performed when importing without a .cfg file, and a warning similar to the following is issued:
```
Message: InnoDB: IO Read error: (2, No such file or directory) Error opening '.\
test\t.cfg', will attempt to import without schema verification
1 row in set (0.00 sec)
```
The ability to import without a .cfg file may be more convenient when no schema mismatches are expected. Additionally, the ability to import without a .cfg file could be useful in crash recovery scenarios in which metadata cannot be collected from an .ibd file.
If no .cfg file is used, InnoDB uses the equivalent of a SELECT MAX(ai_col) FROM table_name FOR UPDATE statement to initialize the in-memory auto-increment counter that is used in assigning values for to an AUTO_INCREMENT column. Otherwise, the current maximum auto-increment counter value is read from the .cfg metadata file. For related information, see InnoDB AUTO_INCREMENT Counter Initialization.
Due to a .cfg metadata file limitation, schema mismatches are not reported for partition type or partition definition differences when importing tablespace files for partitioned tables. Column differences are reported.
When running ALTER TABLE ... DISCARD PARTITION ... TABLESPACE and ALTER TABLE ... IMPORT PARTITION ... TABLESPACE on subpartitioned tables, both partition and subpartition table names are allowed. When a partition name is specified, subpartitions of that partition are included in the operation.
Importing a tablespace file from another MySQL server instance works if both instances have GA (General Availability) status and the server instance into which the file is imported is at the same or higher release level within the same release series. Importing a tablespace file into a server instance running an earlier release of MySQL is not supported.
In replication scenarios, innodb_file_per_table must be set to ON on both the master and slave.
On Windows, InnoDB stores database, tablespace, and table names internally in lowercase. To avoid import problems on case-sensitive operating systems such as Linux and UNIX, create all databases, tablespaces, and tables using lowercase names. A convenient way to accomplish this is to add the following line to the [mysqld] section of your my.cnf or my.ini file before creating databases, tablespaces, or tables:
```
[mysqld]
lower_case_table_names=1
```
Note

It is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized.
ALTER TABLE ... DISCARD TABLESPACE and ALTER TABLE ...IMPORT TABLESPACE are not supported with tables that belong to an InnoDB general tablespace. For more information, see CREATE TABLESPACE.
The default row format for InnoDB tables is configurable using the innodb_default_row_format configuration option. Attempting to import a table that does not explicitly define a row format (ROW_FORMAT), or that uses ROW_FORMAT=DEFAULT, could result in a schema mismatch error if the innodb_default_row_format setting on the source instance differs from the setting on the destination instance. For related information, see Defining the Row Format of a Table.
When exporting an encrypted tablespace, InnoDB generates a .cfp file in addition to a .cfg metadata file. The .cfp file must be copied to the destination instance together with the .cfg file and tablespace file before performing the ALTER TABLE ... IMPORT TABLESPACE operation on the destination instance. The .cfp file contains a transfer key and an encrypted tablespace key. On import, InnoDB uses the transfer key to decrypt the tablespace key. For related information, see Section 15.6.3.9, “InnoDB Data-at-Rest Encryption”.
FLUSH TABLES ... FOR EXPORT is not supported on tables that have a FULLTEXT index. Full-text search auxiliary tables are not flushed. After importing a table with a FULLTEXT index, run OPTIMIZE TABLE to rebuild the FULLTEXT indexes. Alternatively, drop FULLTEXT indexes before the export operation and recreate them after importing the table on the destination instance.

Contents Haut

15.6.3.7.1 Transportable Tablespace Examples

Note

If you are transporting tables that are encrypted using the InnoDB tablespace encryption, see Limitations and Usage Notes before you begin for additional procedural information.

Contents Haut

Example 1: Copying an InnoDB Table to Another Instance

This procedure demonstrates how to copy a regular InnoDB table from a running MySQL server instance to another running instance. The same procedure with minor adjustments can be used to perform a full table restore on the same instance.

On the source instance, create a table if one does not exist:
1. mysql> USE test;
2. mysql> CREATE TABLE t(c1 INT) ENGINE=InnoDB;
On the destination instance, create a table if one does not exist:
1. mysql> USE test;
2. mysql> CREATE TABLE t(c1 INT) ENGINE=InnoDB;
On the destination instance, discard the existing tablespace. (Before a tablespace can be imported, InnoDB must discard the tablespace that is attached to the receiving table.)
1. mysql> ALTER TABLE t DISCARD TABLESPACE;
On the source instance, run FLUSH TABLES ... FOR EXPORT to quiesce the table and create the .cfg metadata file:
1. mysql> USE test;
2. mysql> FLUSH TABLES t FOR EXPORT;
The metadata (.cfg) is created in the InnoDB data directory.

Note

The FLUSH TABLES ... FOR EXPORT statement ensures that changes to the named table have been flushed to disk so that a binary table copy can be made while the instance is running. When FLUSH TABLES ... FOR EXPORT is run, InnoDB produces a .cfg file in the same database directory as the table. The .cfg file contains metadata used for schema verification when importing the tablespace file.
Copy the .ibd file and .cfg metadata file from the source instance to the destination instance. For example:
```
shell> scp /path/to/datadir/test/t.{ibd,cfg} destination-server:/path/to/datadir/test
```
Note

The .ibd file and .cfg file must be copied before releasing the shared locks, as described in the next step.
On the source instance, use UNLOCK TABLES to release the locks acquired by FLUSH TABLES ... FOR EXPORT:
1. mysql> USE test;
2. mysql> UNLOCK TABLES;
On the destination instance, import the tablespace:
1. mysql> USE test;
2. mysql> ALTER TABLE t IMPORT TABLESPACE;
Note

The ALTER TABLE ... IMPORT TABLESPACE feature does not enforce foreign key constraints on imported data. If there are foreign key constraints between tables, all tables should be exported at the same (logical) point in time. In this case you would stop updating the tables, commit all transactions, acquire shared locks on the tables, and then perform the export operation.

Contents Haut

Example 2: Copying an InnoDB Partitioned Table to Another Instance

This procedure demonstrates how to copy a partitioned InnoDB table from a running MySQL server instance to another running instance. The same procedure with minor adjustments can be used to perform a full restore of a partitioned InnoDB table on the same instance.

On the source instance, create a partitioned table if one does not exist. In the following example, a table with three partitions (p0, p1, p2) is created:
1. mysql> USE test;
2. mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 3;
In the /datadir/test directory, there is a separate tablespace (.ibd) file for each of the three partitions.
```
mysql> \! ls /path/to/datadir/test/
t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd
```
On the destination instance, create the same partitioned table:
1. mysql> USE test;
2. mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 3;
In the /datadir/test directory, there is a separate tablespace (.ibd) file for each of the three partitions.
```
mysql> \! ls /path/to/datadir/test/
t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd
```
On the destination instance, discard the tablespace for the partitioned table. (Before the tablespace can be imported on the destination instance, the tablespace that is attached to the receiving table must be discarded.)
1. mysql> ALTER TABLE t1 DISCARD TABLESPACE;
The three .ibd files that make up the tablespace for the partitioned table are discarded from the /datadir/test directory.
On the source instance, run FLUSH TABLES ... FOR EXPORT to quiesce the partitioned table and create the .cfg metadata files:
1. mysql> USE test;
2. mysql> FLUSH TABLES t1 FOR EXPORT;
Metadata (.cfg) files, one for each tablespace (.ibd) file, are created in the /datadir/test directory on the source instance:
```
mysql> \! ls /path/to/datadir/test/
t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd
t1#P#p0.cfg  t1#P#p1.cfg  t1#P#p2.cfg
```
Note

FLUSH TABLES ... FOR EXPORT statement ensures that changes to the named table have been flushed to disk so that binary table copy can be made while the instance is running. When FLUSH TABLES ... FOR EXPORT is run, InnoDB produces a .cfg metadata file for the table's tablespace files in the same database directory as the table. The .cfg files contain metadata used for schema verification when importing tablespace files. FLUSH TABLES ... FOR EXPORT can only be run on the table, not on individual table partitions.
Copy the .ibd and .cfg files from the source instance database directory to the destination instance database directory. For example:
```
shell>scp /path/to/datadir/test/t1*.{ibd,cfg} destination-server:/path/to/datadir/test
```
Note

The .ibd and .cfg files must be copied before releasing the shared locks, as described in the next step.
On the source instance, use UNLOCK TABLES to release the locks acquired by FLUSH TABLES ... FOR EXPORT:
1. mysql> USE test;
2. mysql> UNLOCK TABLES;
On the destination instance, import the tablespace for the partitioned table:
1. mysql> USE test;
2. mysql> ALTER TABLE t1 IMPORT TABLESPACE;

Contents Haut

Example 3: Copying InnoDB Table Partitions to Another Instance

This procedure demonstrates how to copy InnoDB table partitions from a running MySQL server instance to another running instance. The same procedure with minor adjustments can be used to perform a restore of InnoDB table partitions on the same instance. In the following example, a partitioned table with four partitions (p0, p1, p2, p3) is created on the source instance. Two of the partitions (p2 and p3) are copied to the destination instance.

On the source instance, create a partitioned table if one does not exist. In the following example, a table with four partitions (p0, p1, p2, p3) is created:
1. mysql> USE test;
2. mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 4;
In the /datadir/test directory, there is a separate tablespace (.ibd) file for each of the four partitions.
```
mysql> \! ls /path/to/datadir/test/
t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd t1#P#p3.ibd
```
On the destination instance, create the same partitioned table:
1. mysql> USE test;
2. mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 4;
In the /datadir/test directory, there is a separate tablespace (.ibd) file for each of the four partitions.
```
mysql> \! ls /path/to/datadir/test/
t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd t1#P#p3.ibd
```
On the destination instance, discard the tablespace partitions that you plan to import from the source instance. (Before tablespace partitions can be imported on the destination instance, the corresponding partitions that are attached to the receiving table must be discarded.)
1. mysql> ALTER TABLE t1 DISCARD PARTITION p2, p3 TABLESPACE;
The .ibd files for the two discarded partitions are removed from the /datadir/test directory on the destination instance, leaving the following files:
```
mysql> \! ls /path/to/datadir/test/
t1#P#p0.ibd  t1#P#p1.ibd
```
Note

When ALTER TABLE ... DISCARD PARTITION ... TABLESPACE is run on subpartitioned tables, both partition and subpartition table names are allowed. When a partition name is specified, subpartitions of that partition are included in the operation.
On the source instance, run FLUSH TABLES ... FOR EXPORT to quiesce the partitioned table and create the .cfg metadata files.
1. mysql> USE test;
2. mysql> FLUSH TABLES t1 FOR EXPORT;
The metadata files (.cfg files) are created in the /datadir/test directory on the source instance. There is a .cfg file for each tablespace (.ibd) file.
```
mysql> \! ls /path/to/datadir/test/
t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd t1#P#p3.ibd
t1#P#p0.cfg  t1#P#p1.cfg  t1#P#p2.cfg t1#P#p3.cfg
```
Note

FLUSH TABLES ... FOR EXPORT statement ensures that changes to the named table have been flushed to disk so that binary table copy can be made while the instance is running. When FLUSH TABLES ... FOR EXPORT is run, InnoDB produces a .cfg metadata file for the table's tablespace files in the same database directory as the table. The .cfg files contain metadata used for schema verification when importing tablespace files. FLUSH TABLES ... FOR EXPORT can only be run on the table, not on individual table partitions.
Copy the .ibd and .cfg files from the source instance database directory to the destination instance database directory. In this example, only the .ibd and .cfg files for partition 2 (p2) and partition 3 (p3) are copied to the data directory on the destination instance. Partition 0 (p0) and partition 1 (p1) remain on the source instance.
```
shell> scp t1#P#p2.ibd  t1#P#p2.cfg t1#P#p3.ibd t1#P#p3.cfg destination-server:/path/to/datadir/test
```
Note

The .ibd files and .cfg files must be copied before releasing the shared locks, as described in the next step.
On the source instance, use UNLOCK TABLES to release the locks acquired by FLUSH TABLES ... FOR EXPORT:
1. mysql> USE test;
2. mysql> UNLOCK TABLES;
On the destination instance, import the tablespace partitions (p2 and p3):
1. mysql> USE test;
2. mysql> ALTER TABLE t1 IMPORT PARTITION p2, p3 TABLESPACE;
Note

When ALTER TABLE ... IMPORT PARTITION ... TABLESPACE is run on subpartitioned tables, both partition and subpartition table names are allowed. When a partition name is specified, subpartitions of that partition are included in the operation.

Contents Haut

15.6.3.7.2 Transportable Tablespace Internals

The following information describes internals and error log messaging for the transportable tablespaces copy procedure for a regular InnoDB table.

When ALTER TABLE ... DISCARD TABLESPACE is run on the destination instance:

The table is locked in X mode.
The tablespace is detached from the table.

When FLUSH TABLES ... FOR EXPORT is run on the source instance:

The table being flushed for export is locked in shared mode.
The purge coordinator thread is stopped.
Dirty pages are synchronized to disk.
Table metadata is written to the binary .cfg file.

Expected error log messages for this operation:

2013-09-24T13:10:19.903526Z 2 [Note] InnoDB: Sync to disk of '"test"."t"' started.
2013-09-24T13:10:19.903586Z 2 [Note] InnoDB: Stopping purge
2013-09-24T13:10:19.903725Z 2 [Note] InnoDB: Writing table metadata to './test/t.cfg'
2013-09-24T13:10:19.904014Z 2 [Note] InnoDB: Table '"test"."t"' flushed to disk

When UNLOCK TABLES is run on the source instance:

The binary .cfg file is deleted.
The shared lock on the table or tables being imported is released and the purge coordinator thread is restarted.

Expected error log messages for this operation:

2013-09-24T13:10:21.181104Z 2 [Note] InnoDB: Deleting the meta-data file './test/t.cfg'
2013-09-24T13:10:21.181180Z 2 [Note] InnoDB: Resuming purge

When ALTER TABLE ... IMPORT TABLESPACE is run on the destination instance, the import algorithm performs the following operations for each tablespace being imported:

Each tablespace page is checked for corruption.
The space ID and log sequence numbers (LSNs) on each page are updated
Flags are validated and LSN updated for the header page.
Btree pages are updated.
The page state is set to dirty so that it is written to disk.

Expected error log messages for this operation:

2013-07-18 15:15:01 34960 [Note] InnoDB: Importing tablespace for table 'test/t' that was exported from host 'ubuntu'
2013-07-18 15:15:01 34960 [Note] InnoDB: Phase I - Update all pages
2013-07-18 15:15:01 34960 [Note] InnoDB: Sync to disk
2013-07-18 15:15:01 34960 [Note] InnoDB: Sync to disk - done!
2013-07-18 15:15:01 34960 [Note] InnoDB: Phase III - Flush changes to disk
2013-07-18 15:15:01 34960 [Note] InnoDB: Phase IV - Flush complete

Note

You may also receive a warning that a tablespace is discarded (if you discarded the tablespace for the destination table) and a message stating that statistics could not be calculated due to a missing .ibd file:

2013-07-18 15:14:38 34960 [Warning] InnoDB: Table "test"."t" tablespace is set as discarded.
2013-07-18 15:14:38 7f34d9a37700 InnoDB: cannot calculate statistics for table "test"."t" 
because the .ibd file is missing. For help, please refer to
http://dev.mysql.com/innodb-troubleshooting.html

Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-tablespace-copying.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut