MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
CREATE [UNDO] TABLESPACEtablespace_name
InnoDB and NDB: [ADD DATAFILE 'file_name
'] [AUTOEXTEND_SIZE [=]value
] InnoDB only: [FILE_BLOCK_SIZE = value] [ENCRYPTION [=] {'Y' | 'N'}] NDB only: USE LOGFILE GROUPlogfile_group
[EXTENT_SIZE [=]extent_size
] [INITIAL_SIZE [=]initial_size
] [MAX_SIZE [=]max_size
] [NODEGROUP [=]nodegroup_id
] [WAIT] [COMMENT [=] 'string
'] InnoDB and NDB: [ENGINE [=]engine_name
] Reserved for future use: [ENGINE_ATTRIBUTE [=] 'string
']
This statement is used to create a tablespace. The precise syntax
and semantics depend on the storage engine used. In standard MySQL
releases, this is always an InnoDB
tablespace. MySQL NDB Cluster also supports tablespaces using the
NDB
storage engine.
CREATE TABLESPACE
syntax is used to
create general tablespaces or undo tablespaces. The
UNDO
keyword, introduced in MySQL 8.0.14, must
be specified to create an undo tablespace.
A general tablespace is a shared tablespace. It can hold multiple tables, and supports all table row formats. General tablespaces can be created in a location relative to or independent of the data directory.
After creating an InnoDB
general tablespace,
use CREATE TABLE
or
tbl_name
... TABLESPACE [=]
tablespace_name
ALTER TABLE
to add tables
to the tablespace. For more information, see
Section 17.6.3.3, “General Tablespaces”.
tbl_name
TABLESPACE [=]
tablespace_name
Undo tablespaces contain undo logs. Undo tablespaces can be created in a chosen location by specifying a fully qualified data file path. For more information, see Section 17.6.3.4, “Undo Tablespaces”.
This statement is used to create a tablespace, which can contain
one or more data files, providing storage space for NDB Cluster
Disk Data tables (see Section 25.6.11, “NDB Cluster Disk Data Tables”).
One data file is created and added to the tablespace using this
statement. Additional data files may be added to the tablespace by
using the ALTER TABLESPACE
statement (see Section 15.1.10, “ALTER TABLESPACE Statement”).
All NDB Cluster Disk Data objects share the same namespace. This means that each Disk Data object must be uniquely named (and not merely each Disk Data object of a given type). For example, you cannot have a tablespace and a log file group with the same name, or a tablespace and a data file with the same name.
A log file group of one or more UNDO
log files
must be assigned to the tablespace to be created with the
USE LOGFILE GROUP
clause.
logfile_group
must be an existing log
file group created with CREATE LOGFILE
GROUP
(see Section 15.1.16, “CREATE LOGFILE GROUP Statement”).
Multiple tablespaces may use the same log file group for
UNDO
logging.
When setting EXTENT_SIZE
or
INITIAL_SIZE
, you may optionally follow the
number with a one-letter abbreviation for an order of magnitude,
similar to those used in my.cnf
. Generally,
this is one of the letters M
(for megabytes) or
G
(for gigabytes).
INITIAL_SIZE
and EXTENT_SIZE
are subject to rounding as follows:
EXTENT_SIZE
is rounded up to the nearest
whole multiple of 32K.
INITIAL_SIZE
is rounded
down to the nearest whole multiple of
32K; this result is rounded up to the nearest whole multiple
of EXTENT_SIZE
(after any rounding).
NDB
reserves 4% of a tablespace for
data node restart operations. This reserved space cannot be used
for data storage.
The rounding just described is done explicitly, and a warning is
issued by the MySQL Server when any such rounding is performed.
The rounded values are also used by the NDB kernel for calculating
INFORMATION_SCHEMA.FILES
column
values and other purposes. However, to avoid an unexpected result,
we suggest that you always use whole multiples of 32K in
specifying these options.
When CREATE TABLESPACE
is used with
ENGINE [=] NDB
, a tablespace and associated
data file are created on each Cluster data node. You can verify
that the data files were created and obtain information about them
by querying the Information Schema
FILES
table. (See the example later
in this section.)
(See Section 28.3.15, “The INFORMATION_SCHEMA FILES Table”.)
ADD DATAFILE
: Defines the name of a
tablespace data file. This option is always required when
creating an NDB
tablespace; for
InnoDB
in MySQL 8.0.14 and later, it is
required only when creating an undo tablespace. The
,
including any specified path, must be quoted with single or
double quotation marks. File names (not counting the file
extension) and directory names must be at least one byte in
length. Zero length file names and directory names are not
supported.
file_name
Because there are considerable differences in how
InnoDB
and NDB
treat
data files, the two storage engines are covered separately in
the discussion that follows.
InnoDB data files.
An InnoDB
tablespace supports only a
single data file, whose name must include a
.ibd
extension.
To place an InnoDB
general tablespace data
file in a location outside of the data directory, include a
fully qualified path or a path relative to the data directory.
Only a fully qualified path is permitted for undo tablespaces.
If you do not specify a path, a general tablespace is created
in the data directory. An undo tablespace created without
specifying a path is created in the directory defined by the
innodb_undo_directory
variable. If the
innodb_undo_directory
variable is undefined, undo tablespaces are created in the
data directory.
To avoid conflicts with implicitly created file-per-table
tablespaces, creating an InnoDB
general
tablespace in a subdirectory under the data directory is not
supported. When creating a general tablespace or undo
tablespace outside of the data directory, the directory must
exist and must be known to InnoDB
prior to
creating the tablespace. To make a directory known to
InnoDB
, add it to the
innodb_directories
value or
to one of the variables whose values are appended to the
innodb_directories
value.
innodb_directories
is a
read-only variable. Configuring it requires restarting the
server.
If the ADD DATAFILE
clause is not specified
when creating an InnoDB
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
).
A file extension is added if required by the storage engine.
An .ibd
file extension is added for
InnoDB
general tablespace data files. In a
replication environment, the data file name created on the
replication source server is not the same as the data file
name created on the replica.
As of MySQL 8.0.17, the ADD DATAFILE
clause
does not permit circular directory references when creating an
InnoDB
tablespace. For example, the
circular directory reference (/../
) in the
following statement is not permitted:
CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd 'any_directory
/../ts1.ibd';
An exception to this restriction exists on Linux, where a
circular directory reference is permitted if the preceding
directory is a symbolic link. For example, the data file path
in the example above is permitted if
any_directory
is a symbolic link.
(It is still permitted for data file paths to begin with
'../
'.)
NDB data files.
An NDB
tablespace supports multiple data
files which can have any legal file names; more data files
can be added to an NDB Cluster tablespace following its
creation by using an ALTER
TABLESPACE
statement.
An NDB
tablespace data file is created by
default in the data node file system directory—that is,
the directory named
ndb_
under the data node's data directory
(nodeid
_fs/TSDataDir
), where
nodeid
is the data node's
NodeId
. To place the
data file in a location other than the default, include an
absolute directory path or a path relative to the default
location. If the directory specified does not exist,
NDB
attempts to create it; the system user
account under which the data node process is running must have
the appropriate permissions to do so.
When determining the path used for a data file,
NDB
does not expand the
~
(tilde) character.
When multiple data nodes are run on the same physical host, the following considerations apply:
You cannot specify an absolute path when creating a data file.
It is not possible to create tablespace data files outside the data node file system directory, unless each data node has a separate data directory.
If each data node has its own data directory, data files can be created anywhere within this directory.
If each data node has its own data directory, it may also be possible to create a data file outside the node's data directory using a relative path, as long as this path resolves to a unique location on the host file system for each data node running on that host.
FILE_BLOCK_SIZE
: This option—which is
specific to InnoDB
general tablespaces, and
is ignored by NDB
—defines the block
size for the tablespace data file. Values can be specified in
bytes or kilobytes. For example, an 8 kilobyte file block size
can be specified as 8192 or 8K. If you do not specify this
option, FILE_BLOCK_SIZE
defaults to the
innodb_page_size
value.
FILE_BLOCK_SIZE
is required when you intend
to use the tablespace for storing compressed
InnoDB
tables
(ROW_FORMAT=COMPRESSED
). In this case, you
must define the tablespace FILE_BLOCK_SIZE
when creating the tablespace.
If FILE_BLOCK_SIZE
is equal the
innodb_page_size
value, the
tablespace can contain only tables having an uncompressed row
format (COMPACT
,
REDUNDANT
, and DYNAMIC
).
Tables with a COMPRESSED
row format have a
different physical page size than uncompressed tables.
Therefore, compressed tables cannot coexist in the same
tablespace as uncompressed tables.
For a general tablespace to contain compressed tables,
FILE_BLOCK_SIZE
must be specified, and the
FILE_BLOCK_SIZE
value must be a valid
compressed page size in relation to the
innodb_page_size
value. Also,
the physical page size of the compressed table
(KEY_BLOCK_SIZE
) must be equal to
FILE_BLOCK_SIZE/1024
. For example, if
innodb_page_size=16K
, and
FILE_BLOCK_SIZE=8K
, the
KEY_BLOCK_SIZE
of the table must be 8. For
more information, see Section 17.6.3.3, “General Tablespaces”.
USE LOGFILE GROUP
: Required for
NDB
, this is the name of a log file group
previously created using CREATE LOGFILE
GROUP
. Not supported for InnoDB
,
where it fails with an error.
EXTENT_SIZE
: This option is specific to
NDB, and is not supported by InnoDB, where it fails with an
error. EXTENT_SIZE
sets the size, in bytes,
of the extents used by any files belonging to the tablespace.
The default value is 1M. The minimum size is 32K, and
theoretical maximum is 2G, although the practical maximum size
depends on a number of factors. In most cases, changing the
extent size does not have any measurable effect on
performance, and the default value is recommended for all but
the most unusual situations.
An extent is a unit of
disk space allocation. One extent is filled with as much data
as that extent can contain before another extent is used. In
theory, up to 65,535 (64K) extents may used per data file;
however, the recommended maximum is 32,768 (32K). The
recommended maximum size for a single data file is
32G—that is, 32K extents × 1 MB per extent. In
addition, once an extent is allocated to a given partition, it
cannot be used to store data from a different partition; an
extent cannot store data from more than one partition. This
means, for example that a tablespace having a single datafile
whose INITIAL_SIZE
(described in the
following item) is 256 MB and whose
EXTENT_SIZE
is 128M has just two extents,
and so can be used to store data from at most two different
disk data table partitions.
You can see how many extents remain free in a given data file
by querying the Information Schema
FILES
table, and so derive an
estimate for how much space remains free in the file. For
further discussion and examples, see
Section 28.3.15, “The INFORMATION_SCHEMA FILES Table”.
INITIAL_SIZE
: This option is specific to
NDB
, and is not supported by
InnoDB
, where it fails with an error.
The INITIAL_SIZE
parameter sets the total
size in bytes of the data file that was specific using
ADD DATATFILE
. Once this file has been
created, its size cannot be changed; however, you can add more
data files to the tablespace using
ALTER
TABLESPACE ... ADD DATAFILE
.
INITIAL_SIZE
is optional; its default value
is 134217728 (128 MB).
On 32-bit systems, the maximum supported value for
INITIAL_SIZE
is 4294967296 (4 GB).
AUTOEXTEND_SIZE
: Ignored by MySQL prior to
MySQL 8.0.23; From MySQL 8.0.23, defines the amount by which
InnoDB
extends the size of the tablespace
when it becomes full. The setting must be a multiple of 4MB.
The default setting is 0, which causes the tablespace to be
extended according to the implicit default behavior. For more
information, see
Section 17.6.3.9, “Tablespace AUTOEXTEND_SIZE Configuration”.
Has no effect in any release of MySQL NDB Cluster 8.0, regardless of the storage engine used.
MAX_SIZE
: Currently ignored by MySQL;
reserved for possible future use. Has no effect in any release
of MySQL 8.0 or MySQL NDB Cluster 8.0, regardless of the
storage engine used.
NODEGROUP
: Currently ignored by MySQL;
reserved for possible future use. Has no effect in any release
of MySQL 8.0 or MySQL NDB Cluster 8.0, regardless of the
storage engine used.
WAIT
: Currently ignored by MySQL; reserved
for possible future use. Has no effect in any release of MySQL
8.0 or MySQL NDB Cluster 8.0, regardless of the storage engine
used.
COMMENT
: Currently ignored by MySQL;
reserved for possible future use. Has no effect in any release
of MySQL 8.0 or MySQL NDB Cluster 8.0, regardless of the
storage engine used.
The ENCRYPTION
clause enables or disables
page-level data encryption for an InnoDB
general tablespace. Encryption support for general tablespaces
was introduced in MySQL 8.0.13.
As of MySQL 8.0.16, if the ENCRYPTION
clause is not specified, the
default_table_encryption
setting controls whether encryption is enabled. The
ENCRYPTION
clause overrides the
default_table_encryption
setting. However, if the
table_encryption_privilege_check
variable is enabled, the
TABLE_ENCRYPTION_ADMIN
privilege is required to use an ENCRYPTION
clause setting that differs from the
default_table_encryption
setting.
A keyring plugin must be installed and configured before an encryption-enabled tablespace can be created.
When a general tablespace is encrypted, all tables residing in the tablespace are encrypted. Likewise, a table created in an encrypted tablespace is encrypted.
For more information, see Section 17.13, “InnoDB Data-at-Rest Encryption”
ENGINE
: Defines the storage engine which
uses the tablespace, where
engine_name
is the name of the
storage engine. Currently, only the InnoDB
storage engine is supported by standard MySQL 8.0
releases. MySQL NDB Cluster supports both
NDB
and InnoDB
tablespaces. The value of the
default_storage_engine
system
variable is used for ENGINE
if the option
is not specified.
The ENGINE_ATTRIBUTE
option (available as
of MySQL 8.0.21) is used to specify tablespace attributes for
primary storage engines. The option is reserved for future
use.
Permitted values are a string literal containing a valid
JSON
document or an empty string ('').
Invalid JSON
is rejected.
CREATE TABLESPACE ts1 ENGINE_ATTRIBUTE='{"key
":"value
"}';
ENGINE_ATTRIBUTE
values can be repeated
without error. In this case, the last specified value is used.
ENGINE_ATTRIBUTE
values are not checked by
the server, nor are they cleared when the table's storage
engine is changed.
For the rules covering the naming of MySQL tablespaces, see
Section 11.2, “Schema Object Names”. In addition to these rules, the
slash character (“/”) is not permitted, nor can
you use names beginning with innodb_
, as
this prefix is reserved for system use.
Creation of temporary general tablespaces is not supported.
General tablespaces do not support temporary tables.
The TABLESPACE
option may be used with
CREATE TABLE
or
ALTER TABLE
to assign an
InnoDB
table partition or subpartition to a
file-per-table tablespace. All partitions must belong to the
same storage engine. Assigning table partitions to shared
InnoDB
tablespaces is not supported. Shared
tablespaces include the InnoDB
system
tablespace and general tablespaces.
General tablespaces support the addition of tables of any row
format using
CREATE TABLE ...
TABLESPACE
.
innodb_file_per_table
does
not need to be enabled.
innodb_strict_mode
is not
applicable to general tablespaces. Tablespace management rules
are strictly enforced independently of
innodb_strict_mode
. If
CREATE TABLESPACE
parameters are incorrect
or incompatible, the operation fails regardless of the
innodb_strict_mode
setting.
When a table is added to a general tablespace using
CREATE TABLE ...
TABLESPACE
or
ALTER TABLE ...
TABLESPACE
,
innodb_strict_mode
is ignored
but the statement is evaluated as if
innodb_strict_mode
is
enabled.
Use DROP TABLESPACE
to remove a tablespace.
All tables must be dropped from a tablespace using
DROP TABLE
prior to dropping
the tablespace. Before dropping an NDB Cluster tablespace you
must also remove all its data files using one or more
ALTER
TABLESPACE ... DROP DATATFILE
statements. See
Section 25.6.11.1, “NDB Cluster Disk Data Objects”.
All parts of an InnoDB
table added to an
InnoDB
general tablespace reside in the
general tablespace, including indexes and
BLOB
pages.
For an NDB
table assigned to a tablespace,
only those columns which are not indexed are stored on disk,
and actually use the tablespace data files. Indexes and
indexed columns for all NDB
tables are
always kept in memory.
Similar to the system tablespace, truncating or dropping
tables stored in a general tablespace creates free space
internally in the general tablespace
.ibd data file which can
only be used for new InnoDB
data. Space is
not released back to the operating system as it is for
file-per-table tablespaces.
A general tablespace is not associated with any database or schema.
ALTER TABLE ...
DISCARD TABLESPACE
and
ALTER TABLE
...IMPORT TABLESPACE
are not supported for tables
that belong to a general tablespace.
The server uses tablespace-level metadata locking for DDL that references general tablespaces. By comparison, the server uses table-level metadata locking for DDL that references file-per-table tablespaces.
A generated or existing tablespace cannot be changed to a general tablespace.
There is no conflict between general tablespace names and file-per-table tablespace names. The “/” character, which is present in file-per-table tablespace names, is not permitted in general tablespace names.
mysqldump and mysqlpump
do not dump InnoDB
CREATE TABLESPACE
statements.
This example demonstrates creating a general tablespace and adding three uncompressed tables of different row formats.
mysql>CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' ENGINE=INNODB;
mysql>CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=REDUNDANT;
mysql>CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=COMPACT;
mysql>CREATE TABLE t3 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=DYNAMIC;
This example demonstrates creating a general tablespace and adding
a compressed table. The example assumes a default
innodb_page_size
value of 16K.
The FILE_BLOCK_SIZE
of 8192 requires that the
compressed table have a KEY_BLOCK_SIZE
of 8.
mysql>CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;
mysql>CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
This example demonstrates creating a general tablespace without
specifying the ADD DATAFILE
clause, which is
optional as of MySQL 8.0.14.
mysql> CREATE TABLESPACE `ts3` ENGINE=INNODB;
This example demonstrates creating an undo tablespace.
mysql> CREATE UNDO TABLESPACE undo_003
ADD DATAFILE 'undo_003
.ibu';
Suppose that you wish to create an NDB Cluster Disk Data
tablespace named myts
using a datafile named
mydata-1.dat
. An NDB
tablespace always requires the use of a log file group consisting
of one or more undo log files. For this example, we first create a
log file group named mylg
that contains one
undo long file named myundo-1.dat
, using the
CREATE LOGFILE GROUP
statement
shown here:
mysql>CREATE LOGFILE GROUP myg1
->ADD UNDOFILE 'myundo-1.dat'
->ENGINE=NDB;
Query OK, 0 rows affected (3.29 sec)
Now you can create the tablespace previously described using the following statement:
mysql>CREATE TABLESPACE myts
->ADD DATAFILE 'mydata-1.dat'
->USE LOGFILE GROUP mylg
->ENGINE=NDB;
Query OK, 0 rows affected (2.98 sec)
You can now create a Disk Data table using a
CREATE TABLE
statement with the
TABLESPACE
and STORAGE DISK
options, similar to what is shown here:
mysql>CREATE TABLE mytable (
->id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
->lname VARCHAR(50) NOT NULL,
->fname VARCHAR(50) NOT NULL,
->dob DATE NOT NULL,
->joined DATE NOT NULL,
->INDEX(last_name, first_name)
->)
->TABLESPACE myts STORAGE DISK
->ENGINE=NDB;
Query OK, 0 rows affected (1.41 sec)
It is important to note that only the dob
and
joined
columns from mytable
are actually stored on disk, due to the fact that the
id
, lname
, and
fname
columns are all indexed.
As mentioned previously, when CREATE TABLESPACE
is used with ENGINE [=] NDB
, a tablespace and
associated data file are created on each NDB Cluster data node.
You can verify that the data files were created and obtain
information about them by querying the Information Schema
FILES
table, as shown here:
mysql>SELECT FILE_NAME, FILE_TYPE, LOGFILE_GROUP_NAME, STATUS, EXTRA
->FROM INFORMATION_SCHEMA.FILES
->WHERE TABLESPACE_NAME = 'myts';
+--------------+------------+--------------------+--------+----------------+ | file_name | file_type | logfile_group_name | status | extra | +--------------+------------+--------------------+--------+----------------+ | mydata-1.dat | DATAFILE | mylg | NORMAL | CLUSTER_NODE=5 | | mydata-1.dat | DATAFILE | mylg | NORMAL | CLUSTER_NODE=6 | | NULL | TABLESPACE | mylg | NORMAL | NULL | +--------------+------------+--------------------+--------+----------------+ 3 rows in set (0.01 sec)
For additional information and examples, see Section 25.6.11.1, “NDB Cluster Disk Data Objects”.