Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Copying File-Per-Table Tablespaces to Another Server

14.4.6 Copying File-Per-Table Tablespaces to Another Server

This section describes how to copy file-per-table tablespaces from one database server to another, otherwise known as the Transportable Tablespaces feature. Prior to MySQL 5.7.4, only non-partitioned InnoDB tables are supported. As of MySQL 5.7.4, partitioned InnoDB tables and individual InnoDB table partitions and subpartitions are also supported.

For information about other InnoDB table copying methods, see Section 14.5.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:

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

Limitations and Usage Notes

  • The tablespace copy procedure is only possible when innodb_file_per_table is set to ON, which is the default setting as of MySQL 5.6.6. 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.

  • Prior to MySQL 5.7.4, DISCARD TABLESPACE is not supported for partitioned tables meaning that transportable tablespaces is also unsupported. If you run ALTER TABLE ... DISCARD TABLESPACE on a partitioned table, the following error is returned: ERROR 1031 (HY000): Table storage engine for 'part' doesn't have this option. As of MySQL 5.7.4, 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 will be 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.

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

  • In MySQL 5.6 or later, importing a tablespace file from another server works if both servers have GA (General Availability) status and their versions are within the same series. Otherwise, the file must have been created on the server into which it is imported.

  • 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:

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

  • As of MySQL 5.7.9, 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 server differs from the setting on the destination server. For related information, see Section 14.8.2, “Specifying the Row Format for a Table”.

Download this Manual
User Comments
  Posted by Valeriy Kravchuk on July 9, 2013
Note (you can read about it at that for a long time already, since is fixed in 5.6.8, .cfg file is not necessary at least for some (not clearly identified) cases. Indeed, recent MySQL 5.6.x versions will import just .ibd, assuming its "clean" and table really has the same structure.
  Posted by Ikechukwu Umejiofor on October 16, 2013
Given a Server A and an Innodb table(t500) symlinked thus:

`id` int(11) NOT NULL,
`c` char(20) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='/var/lib/mysqlw/';

Server A mysql datadir=/var/lib/mysql/data

And you want to import this innodb table on server B but on a different DATA DIRECTORY clause option thus

so on server B you do:

`id` int(11) NOT NULL,
`c` char(20) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='/var/lib/mysqla/';

Server B mysql datadir=/var/lib/mysql/data

import of t500 from Server A to B works just fine.

However, trying to import this same table from server A with definition

`id` int(11) NOT NULL,
`c` char(20) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='/var/lib/mysqlw/';

to server B with definition:

`id` int(11) NOT NULL,
`c` char(20) DEFAULT NULL,

will fail with the following errors:

ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x6 and the meta-data file has 0x41)

...I guess a symlinked innodb table from source should also be symlinked on import on the destination server!!!

By the way am using mysql 5.6.10 community version.

  Posted by Ruben Cardenal on October 10, 2014
You'll want to issue an analyze after importing, in order to notify the data dictionary about the new indexes for that table. Otherwise, they won't be used and its cardinality will be reported as 0. At least as of 5.6.20.
Sign Up Login You must be logged in to post a comment.