content_image/743968.jpg
table tablespaces. As with file-per-table tablespaces, the ability to place data files outside of the MySQL data directory allows you to manage performance of critical tables separately, setup RAID or DRBD for specific tables, or bind tables to particular disks, for example. 

•General tablespaces support all table row formats and associated features. 

•The TABLESPACE  option can be used with CREATE TABLE  to create tables in a general tablespaces, file-per-table tablespace, or in the system tablespace. 

•The TABLESPACE  option can be used with ALTER TABLE  to move tables between general tablespaces, file-per-table tablespaces, and the system tablespace. 

## Creating a General Tablespace 

General tablespaces are created using CREATE TABLESPACE  syntax. 

CREATE TABLESPACE tablespace_name [ADD DATAFILE ’ file_name ’] [FILE_BLOCK_SIZE = value ] [ENGINE [=] engine_name ] 

A general tablespace can be created in the data directory or outside of it. To avoid conflicts with implicitly created file-per-table tablespaces, creating a general tablespace in a subdirectory under the data directory is not supported. When creating a general tablespace outside of the data directory, the directory must exist and must be known to InnoDB  prior to creating the tablespace. To make an unknown directory known to InnoDB , add the directory to the innodb_directories  argument value. innodb_directories  is a read-only startup option. Configuring it requires restarting the server. 

Examples: 

Creating a general tablespace in the data directory: 

mysql> CREATE TABLESPACE ‘ts1‘ ADD DATAFILE ’ts1.ibd’ Engine=InnoDB; 

or 

mysql> CREATE TABLESPACE ‘ts1‘ Engine=InnoDB; 

The ADD DATAFILE  clause is optional as of MySQL 8.0.14 and required before that. If the ADD DATAFILE  clause is not specified when creating a tablespace, a tablespace data file with a unique file name is created implicitly. The unique file name is a 128 bit UUID formatted into five groups of hexadecimal numbers separated by dashes ( aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee ). General tablespace data files include an .ibd  file extension. In a replication environment, the data file name created on the source is not the same as the data file name created on the replica. 

Creating a general tablespace in a directory outside of the data directory: 

mysql> CREATE TABLESPACE ‘ts1‘ ADD DATAFILE ’/my/tablespace/directory/ts1.ibd’ Engine=InnoDB; 

You can specify a path that is relative to the data directory as long as the tablespace directory is not under the data directory. In this example, the my_tablespace  directory is at the same level as the data directory: 

mysql> CREATE TABLESPACE ‘ts1‘ ADD DATAFILE ’../my_tablespace/ts1.ibd’ Engine=InnoDB; 

<img src='content_image/743968.jpg'>

## Note 

The ENGINE = InnoDB  clause must be defined as part of the CREATE TABLESPACE  statement, or InnoDB  must be defined as the default storage engine ( default_storage_engine=InnoDB ). 

