CREATE TABLE ...
DATA DIRECTORY clause permits creating a
tablespace outside of the data directory. For example, you can use
DATA DIRECTORY clause to create a
tablespace on a separate storage device with particular
performance or capacity characteristics, such as a fast
SSD or a high-capacity
Be sure of the location that you choose. The
DIRECTORY clause cannot be used with
ALTER TABLE to change the location
The tablespace data file is created in the specified directory,
within in a subdirectory named for the schema to which the table
belongs. An isl file file
that contains the tablespace path is created in the schema
directory, beneath the data directory. The
isl file is treated like a symbolic link.
(Using actual symbolic links
is not supported for
The following example demonstrates creating a file-per-table
tablespace outside of the data directory. It is assumed that the
innodb_file_per_table variable is
mysql> USE test; Database changed mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/remote/directory'; # MySQL creates the tablespace file in a subdirectory that is named # for the schema to which the table belongs shell> cd /remote/directory/test shell> ls t1.ibd # In the schema directory, MySQL creates an isl file that defines # the tablespace path shell> cd /path/to/mysql/data/test shell> ls db.opt t1.frm t1.isl
MySQL initially holds the tablespace data file open, preventing you from dismounting the device, but might eventually close the table if the server is busy. Be careful not to accidentally dismount an external device while MySQL is running, or start MySQL while the device is disconnected. Attempting to access a table when the associated tablespace data file is missing causes a serious error that requires a server restart.
A server restart might fail if the tablespace data file file is not at the expected path. In this case, manually remove the
islfile from the schema directory, and after restarting, drop the table to remove the
.frmfile and the information about the table from the data dictionary.
Before placing a tablespace on an NFS-mounted volume, review potential issues outlined in Using NFS with MySQL.
If using an LVM snapshot, file copy, or other file-based mechanism to back up the tablespace data file, always use the
FLUSH TABLES ... FOR EXPORTstatement first to ensure that all changes buffered in memory are flushed to disk before the backup occurs.
DATA DIRECTORYclause is an alternative to using symbolic links, which is not supported.