14.5.5 Copying Tablespaces to Another Server (Transportable Tablespaces)

This section describes how to copy file-per-table tablespaces (.idb files) from one database server to another using the Transportable Tablespace feature.

For information about other InnoDB table copying methods, see Section 14.6.2, “Moving or Copying InnoDB Tables to Another Machine”.

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

Tablespace Copying Limitations and Usage Notes (Transportable Tablespaces)

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

This procedure demonstrates how to copy a 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.

  1. On the source server, create a table if one does not already exist:

    mysql> use test;
    mysql> CREATE TABLE t(c1 INT) engine=InnoDB;
    
  2. On the destination server, create a table if one does not exist:

    mysql> use test;
    mysql> CREATE TABLE t(c1 INT) engine=InnoDB;
    
  3. On the destination server, discard the existing tablespace. (Before a tablespace can be imported, InnoDB must discard the tablespace that is attached to the receiving table.)

    mysql> ALTER TABLE t DISCARD TABLESPACE;
    
  4. On the source server, run FLUSH TABLES ... FOR EXPORT to quiesce the table and create the .cfg metadata file:

    mysql> use test;
    mysql> FLUSH TABLES t FOR EXPORT;
    

    The metadata (.cfg) file is created in the InnoDB data directory.

    Note

    FLUSH TABLES ... FOR EXPORT 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 file in the same database directory as the table. The .cfg file contains metadata used for schema verification when importing the tablespace file.

  5. Copy the .ibd file and .cfg metadata file from the source server to the destination server. 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.

  6. On the source server, use UNLOCK TABLES to release the locks acquired by FLUSH TABLES ... FOR EXPORT:

    mysql> use test;
    mysql> UNLOCK TABLES;
    
  7. On the destination server, import the tablespace:

    mysql> use test;
    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.

Tablespace Copying Internals (Transportable Tablespaces)

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

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

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

Expected error log messages for this operation:

2013-07-18 14:47:31 34471 [Note] InnoDB: Sync to disk of '"test"."t"' started.
2013-07-18 14:47:31 34471 [Note] InnoDB: Stopping purge
2013-07-18 14:47:31 34471 [Note] InnoDB: Writing table metadata to './test/t.cfg'
2013-07-18 14:47:31 34471 [Note] InnoDB: Table '"test"."t"' flushed to disk
 

When UNLOCK TABLES is run on the source instance:

Expected error log messages for this operation:

2013-07-18 15:01:40 34471 [Note] InnoDB: Deleting the meta-data file './test/t.cfg'
2013-07-18 15:01:40 34471 [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:

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/doc/refman/5.7/en/innodb-troubleshooting.html