15.6.3.7 Copying Tablespaces to Another 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.

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

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.