MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
This section describes how to import tables using the Transportable Tablespaces feature, which permits importing tables that reside in file-per-table tablespaces. There are many reasons why you might want to import tables:
To run reports on a non-production MySQL server instance to avoid placing extra load on a production server.
To copy data to a new replica server.
To restore a table from a backed-up tablespace file.
As a faster way of moving data than importing a dump file, which requires reinserting data and rebuilding indexes.
To move a data to a server with storage media that is better suited to your storage requirements. For example, you might move busy tables to an SSD device, or move large tables to a high-capacity HDD device.
The Transportable Tablespaces feature is described under the following topics in this section:
The innodb_file_per_table
variable must be enabled, which it is by default.
The page size of the tablespace must match the page size of
the destination MySQL server instance.
InnoDB
page size is defined by the
innodb_page_size
variable,
which is configured when initializing a MySQL server
instance.
If the table is in a foreign key relationship,
foreign_key_checks
must be
disabled before executing DISCARD
TABLESPACE
. Also, you should export all foreign
key related tables at the same logical point in time, as the
ALTER TABLE ...
IMPORT TABLESPACE
feature does not enforce foreign
key constraints on imported data. To do so, stop updating
the related tables, commit all transactions, acquire shared
locks on the tables, and perform the export operations.
When importing a table from another MySQL server instance, both MySQL server instances must have General Availability (GA) status and must be the same version. Otherwise, the table must be created on the same MySQL server instance into which it is being imported.
If the table was created outside of the data directory by
specifying the DATA DIRECTORY
clause in
the CREATE TABLE
statement,
the table that you replace on the destination instance must
be defined with the same DATA DIRECTORY
clause. A schema mismatch error is reported if the clauses
do not match. To determine if the source table was defined
with a DATA DIRECTORY
clause, use
SHOW CREATE TABLE
to view the
table definition. For information about using the
DATA DIRECTORY
clause, see
Section 14.6.1.2, “Creating Tables Externally”.
This example demonstrates how to import a table that resides in a file-per-table tablespace.
On the destination instance, create a table with the same
definition as the table you intend to import. (You can
obtain the table definition using SHOW
CREATE TABLE
syntax.) If the table definition does
not match, a schema mismatch error is reported when you
attempt the import operation.
mysql> USE test; mysql> CREATE TABLE t1 (c1 INT) ENGINE=INNODB;
On the destination instance, discard the tablespace of the table that you just created. (Before importing, you must discard the tablespace of the receiving table.)
mysql> ALTER TABLE t1 DISCARD TABLESPACE;
On the source instance, run
FLUSH
TABLES ... FOR EXPORT
to quiesce the table you
intend to import. When a table is quiesced, only read-only
transactions are permitted on the table.
mysql> USE test; mysql> FLUSH TABLES t1 FOR EXPORT;
FLUSH
TABLES ... FOR EXPORT
ensures that changes to the
named table are flushed to disk so that a binary table copy
can be made while the server is running. When
FLUSH
TABLES ... FOR EXPORT
is run,
InnoDB
generates a
.cfg
metadata file in the schema
directory of the table. The .cfg
file
contains metadata that is used for schema verification
during the import operation.
Copy the .ibd
file and
.cfg
metadata file from the source
instance to the destination instance. For example:
shell> scp/path/to/datadir
/test/t1.{ibd,cfg} destination-server:/path/to/datadir
/test
The .ibd
file and
.cfg
file must be copied before
releasing the shared locks, as described in the next step.
On the source instance, use
UNLOCK
TABLES
to release the locks acquired by the
FLUSH
TABLES ... FOR EXPORT
statement:
mysql> USE test; mysql> UNLOCK TABLES;
On the destination instance, import the tablespace:
mysql> USE test; mysql> ALTER TABLE t1 IMPORT TABLESPACE;
The Transportable Tablespaces feature is only supported for tables that reside in file-per-table tablespaces. It is not supported for the tables that reside in the system tablespace. Tables in the system tablespace cannot be quiesced.
DISCARD TABLESPACE
is not supported for
partitioned tables, which means that importing partitioned
tables is not supported. 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.
FLUSH
TABLES ... FOR EXPORT
is not supported on tables
with a FULLTEXT
index, as full-text
search auxiliary tables cannot be 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 the indexes after importing the table
on the destination instance.
A .cfg
metadata file is not required to
import a table. 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)
Importing without a .cfg
metadata file
should only be considered if no schema mismatches are
expected. The ability to import without a
.cfg
file could be useful in crash
recovery scenarios where metadata is not accessible.
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
lower_case_table_names=1
to
the [mysqld]
section of your
my.cnf
or my.ini
file before creating databases, tablespaces, or tables:
[mysqld] lower_case_table_names=1
The following information describes internals and messages written to the error log during a table import procedure.
When ALTER TABLE
... DISCARD TABLESPACE
is run on the destination
instance:
The table is locked in X mode.
The tablespace is detached from the table.
When
FLUSH
TABLES ... FOR EXPORT
is run on the source instance:
The table being flushed for export is locked in shared mode.
The purge coordinator thread is stopped.
Dirty pages are synchronized to disk.
Table metadata is written to the binary
.cfg
file.
Expected error log messages for this operation:
[Note] InnoDB: Sync to disk of '"test"."t1"' started. [Note] InnoDB: Stopping purge [Note] InnoDB: Writing table metadata to './test/t1.cfg' [Note] InnoDB: Table '"test"."t1"' flushed to disk
When UNLOCK
TABLES
is run on the source instance:
The binary .cfg
file is deleted.
The shared lock on the table being imported is released and the purge coordinator thread is restarted.
Expected error log messages for this operation:
[Note] InnoDB: Deleting the meta-data file './test/t1.cfg' [Note] InnoDB: Resuming purge
When ALTER TABLE
... IMPORT TABLESPACE
is run on the destination
instance, the import algorithm performs the following operations
for the tablespace being imported:
Each tablespace page is checked for corruption.
The space ID and log sequence numbers (LSNs) on each page are updated.
Flags are validated and LSN updated for the header page.
Btree pages are updated.
The page state is set to dirty so that it is written to disk.
Expected error log messages for this operation:
[Note] InnoDB: Importing tablespace for table 'test/t1' that was exported
from host 'host_name
'
[Note] InnoDB: Phase I - Update all pages
[Note] InnoDB: Sync to disk
[Note] InnoDB: Sync to disk - done!
[Note] InnoDB: Phase III - Flush changes to disk
[Note] InnoDB: Phase IV - Flush complete
You may also receive a warning that a tablespace is discarded
(if you discarded the tablespace for the destination table)
and a message stating that statistics could not be calculated
due to a missing .ibd
file:
[Warning] InnoDB: Table "test"."t1" tablespace is set as discarded. 7f34d9a37700 InnoDB: cannot calculate statistics for table "test"."t1" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html