Pre-General Availability Draft: 2018-02-22
This section describes how to copy a
tablespaces from one MySQL instance to another, otherwise known as
Tablespaces feature. This feature also supports partitioned
InnoDB tables and individual
InnoDB table partitions and subpartitions.
For information about other
copying methods, see Section 184.108.40.206, “Moving or Copying InnoDB Tables”.
There are many reasons why you might copy an
tablespace to a different 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_tableis 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.
DISCARD TABLESPACEis not supported for tablespaces with a parent-child (primary key-foreign key) relationship when
foreign_key_checksis set to
1. Before discarding a tablespace for parent-child tables, set
InnoDBtables do not support foreign keys.
ALTER TABLE ... IMPORT TABLESPACEdoes 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
InnoDBtables do not support foreign keys.
ALTER TABLE ... IMPORT TABLESPACEand
ALTER TABLE ... IMPORT PARTITION ... TABLESPACEdo not require a
.cfgmetadata file to import a tablespace. However, metadata checks are not performed when importing without a
.cfgfile, 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
.cfgfile may be more convenient when no schema mismatches are expected. Additionally, the ability to import without a
.cfgfile could be useful in crash recovery scenarios in which metadata cannot be collected from an
.cfgfile is used,
InnoDBuses the equivalent of a
SELECT MAX(ai_col) FROMstatement to initialize the in-memory auto-increment counter that is used in assigning values for to an
AUTO_INCREMENTcolumn. Otherwise, the current maximum auto-increment counter value is read from the
.cfgmetadata file. For related information, see InnoDB AUTO_INCREMENT Counter Initialization.
Due to a
.cfgmetadata 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.
ALTER TABLE ... DISCARD PARTITION ... TABLESPACEand
ALTER TABLE ... IMPORT PARTITION ... TABLESPACEon 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 their versions are within the same series. Otherwise, the file must have been created on the same server instance into which it is imported.
In replication scenarios,
innodb_file_per_tablemust be set to
ONon both the master and slave.
InnoDBstores 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.inifile before creating databases, tablespaces, or tables:
The default row format for
InnoDBtables is configurable using the
innodb_default_row_formatconfiguration 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_formatsetting on the source instance differs from the setting on the destination instance. For related information, see Section 15.10.2, “Specifying the Row Format for a Table”.
When exporting a tablespace that is encrypted using the
InnoDBtablespace encryption feature,
.cfpfile in addition to a
.cfgmetadata file. The
.cfpfile must be copied to the destination instance together with the
.cfgfile and tablespace file before performing the
ALTER TABLE ... IMPORT TABLESPACEoperation on the destination instance. The
.cfpfile contains a transfer key and an encrypted tablespace key. On import,
InnoDBuses the transfer key to decrypt the tablespace key. For related information, see Section 15.7.11, “InnoDB Tablespace Encryption”.