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

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.

When creating a tablespace outside of the data directory, ensure that the directory is known to InnoDB. Otherwise, if the server halts unexpectedly before tablespace data file pages are fully flushed, startup fails when the tablespace is not found during the pre-recovery discovery phase that searches known directories for tablespace data files (see Tablespace Discovery During Crash Recovery). To make a directory known, add it to the innodb_directories argument value. innodb_directories is a read-only startup option that defines directories to scan at startup for tablespace data files. Configuring it requires restarting the server.

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.

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

Usage Notes: