Quickly copy the 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. You can use the same procedure with minor adjustments to perform a full table restore on the same instance.

On the source instance, create a table (if it does not exist):

Mysql > USE test;mysql > CREATE TABLE t (C1 INT) ENGINE=InnoDB

On the target instance, create a table (if it does not exist):

Mysql > USE test;mysql > CREATE TABLE t (C1 INT) ENGINE=InnoDB

On the target instance, discard the existing tablespace. Before importing a tablespace, InnoDB must discard the tablespace attached to the receiving table. )

Mysql > ALTER TABLE t DISCARD TABLESPACE

On the source instance, run FLUSH TABLES... FOR EXPORT to pause the table and create a .cfg metadata file:

Mysql > USE test;mysql > FLUSH TABLES t FOR EXPORT

Metadata (.cfg) is created in the InnoDB data directory.

Pay attention

The FLUSH TABLES... The FOR EXPORT statement ensures that changes to the specified table are flushed to disk so that a copy of the binary table can be created while the instance is running. When FLUSH TABLES... When FOR EXPORT runs, InnoDB produces a directory table file with .cfg in the same database. The .cfg file contains metadata for schema validation when importing tablespace files.

Copy .ibd files and .cfg metadata files 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

Be careful

The .ibd and .cfg files must release the shared lock before being copied as described in the next step.

On the source instance, used for UNLOCK TABLES release via FLUSH TABLES... FOR EXPORT acquired locks in the following ways:

Mysql > USE test;mysql > UNLOCK TABLES

On the target instance, import the tablespace:

Mysql > USE test;mysql > ALTER TABLE t IMPORT TABLESPACE

Pay attention

The ALTER TABLE... The 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 will stop updating the table, commit all transactions, acquire the shared lock on the table, and then perform the export operation.

Note: the command ALTER TABLE t DISCARD TABLESPACE will delete the data file, and misoperation will have serious consequences.

Reference documentation: transferable tablespace exampl