Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.0Mb
PDF (A4) - 38.0Mb
PDF (RPM) - 37.4Mb
HTML Download (TGZ) - 10.3Mb
HTML Download (Zip) - 10.3Mb
HTML Download (RPM) - 8.9Mb
Man Pages (TGZ) - 216.7Kb
Man Pages (Zip) - 329.5Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

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

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.

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

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

    mysql> USE test;
    mysql> CREATE TABLE t(c1 INT) ENGINE=InnoDB;
  3. 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.)

    mysql> ALTER TABLE t DISCARD TABLESPACE;
  4. On the source instance, 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) 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 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.

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

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

    mysql> USE test;
    mysql> UNLOCK TABLES;
  7. On the destination instance, 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.

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.

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

    mysql> USE test;
    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/
    db.opt  t1.frm  t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd
  2. On the destination instance, create the same partitioned table:

    mysql> USE test;
    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/
    db.opt  t1.frm  t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd
  3. 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.)

    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, leaving the following files:

    mysql> \! ls /path/to/datadir/test/
    db.opt  t1.frm
  4. On the source instance, run FLUSH TABLES ... FOR EXPORT to quiesce the partitioned table and create the .cfg metadata files:

    mysql> USE test;
    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/
    db.opt t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd
    t1.frm  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.

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

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

    mysql> USE test;
    mysql> UNLOCK TABLES;
  7. On the destination instance, import the tablespace for the partitioned table:

    mysql> USE test;
    mysql> ALTER TABLE t1 IMPORT TABLESPACE;
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.

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

    mysql> USE test;
    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/
    db.opt  t1.frm  t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd t1#P#p3.ibd
  2. On the destination instance, create the same partitioned table:

    mysql> USE test;
    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/
    db.opt  t1.frm  t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd t1#P#p3.ibd
  3. 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.)

    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/
    db.opt  t1.frm  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.

  4. On the source instance, run FLUSH TABLES ... FOR EXPORT to quiesce the partitioned table and create the .cfg metadata files.

    mysql> USE test;
    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/
    db.opt  t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd t1#P#p3.ibd
    t1.frm  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.

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

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

    mysql> USE test;
    mysql> UNLOCK TABLES;
  7. On the destination instance, import the tablespace partitions (p2 and p3):

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


User Comments
Sign Up Login You must be logged in to post a comment.