5.4.1.3. Copying Tablespaces to Another Server (Transportable Tablespaces)

There are many reasons why you might copy an InnoDB table to a different database server:

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 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.

Use the FLUSH TABLES statement with the FOR EXPORT clause. This puts the corresponding .ibd files into a consistent state so that they can be copied to another server. You copy a .cfg file along with the .ibd file; this extra file is used by ALTER TABLE ... IMPORT TABLESPACE to make any necessary adjustments within the tablespace file during the import process. See Section 13.7.6.3, "FLUSH Syntax" for the SQL semantics.

Example Procedure: Copying a Tablespace From One Server To Another (Transportable Tablespaces)

On the source server, create a table if one does not already exist:
```
mysql> use test;mysql> CREATE TABLE t(c1 INT) engine=InnoDB;
```
On the destination server, create a table if one does not already exist:
```
mysql> use test;mysql> CREATE TABLE t(c1 INT) engine=InnoDB;
```
On the destination server, discard the existing tablespace:
```
mysql> use test;mysql> ALTER TABLE t DISCARD TABLESPACE;
```
Note

Note: The tablespace file need not necessarily have been created on the server into which the tablespace file is being imported. In MySQL 5.6 or later, importing a tablespace file from another server works if the both servers have GA (General Availability) status and their versions are within the same series. Otherwise, the file must have been created on the server into which it is imported.
On the source server, quiesce the table and create the .cfg metadata file by running FLUSH TABLES ... FOR EXPORT:
```
mysql> use test;mysql> FLUSH TABLES t FOR EXPORT;
```
The metadata file (t.cfg) is created in the InnoDB data directory.

Note

Note: FLUSH TABLES ... FOR EXPORT applies to InnoDB tables. It is available as of MySQL 5.6.6. The statement ensures that changes to the named tables have been flushed to disk so that binary table copies can be made while the server is running. When FLUSH TABLES ... FOR EXPORT is run, InnoDB produces a .cfg in the same database directory as the table. The .cfg file contains metadata needed to import the tablespace file.
Copy the .cfg metadata file and .ibd file from the source server to the destination server. For example:
```
shell> scp /innodb_data_dir/test/t.{ibd,cfg} destination-server:/innodb_data_dir/test
```
On the source server, use UNLOCK TABLES to release the locks acquired by FLUSH TABLES ... FOR EXPORT:
```
mysql> use test;mysql> UNLOCK TABLES;
```

On the destination server, import the tablespace:

mysql> use test;mysql> ALTER TABLE t IMPORT TABLESPACE;