MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6

14.6.3.6 Creating a Tablespace Outside of the Data Directory

The CREATE TABLE ... DATA DIRECTORY clause permits creating a file-per-table tablespace outside of the data directory. For example, you can use the 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 HDD.

Be sure of the location that you choose. The DATA DIRECTORY clause cannot be used with ALTER TABLE to change the location later.

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 InnoDB tables.)

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

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

CREATE TABLE ... TABLESPACE syntax can also be used in combination with the DATA DIRECTORY clause to create a file-per-table tablespace outside of the data directory. To do so, specify innodb_file_per_table as the tablespace name.

mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE = innodb_file_per_table
       DATA DIRECTORY = '/remote/directory';

The innodb_file_per_table variable does not need to be enabled when using this method.

Usage Notes: