InnoDB tables and indexes
were stored in the system
tablespace. This monolithic approach was targeted at
machines dedicated entirely to database processing, with carefully
planned data growth, where any disk storage allocated to MySQL
would never be needed for other purposes.
file-per-table mode is
a more flexible alternative, where you store each
InnoDB table and its indexes in a separate
file. Each such
represents a separate
tablespace. This mode is
controlled by the
configuration option, and is the default in MySQL 5.6.6 and
Multiple tablespaces are useful in a number of situations:
You can reclaim disk space when truncating or dropping a
table. For tables created when file-per-table mode is turned
off, truncating or dropping them creates free space internally
in the ibdata files.
That free space can only be used for new
You can store specific tables on separate storage devices, for
I/O optimization, space management, or backup purposes. In
previous releases, you had to move entire database directories
to other drives and create symbolic links in the MySQL data
directory, as described in Section 18.104.22.168, “Using Symbolic Links”.
In MySQL 5.6 and higher, you can specify the location of each
table using the syntax
CREATE TABLE ... DATA
as explained in Section 22.214.171.124, “Specifying the Location of a Tablespace”.
You can copy individual
InnoDB tables from
one MySQL instance to another (known as the
You can enable more efficient storage for tables with large BLOB or text columns using the dynamic row format.
You can back up or restore a single table quickly, without
interrupting the use of other
tables, using the MySQL Enterprise Backup product. See
Backing Up and Restoring a Single
.ibd File for the procedure