MySQL 指定路径创建表空间

ADD DATAFILE：指定表空间的关联文件和路径，注意必须已.ibd结尾
AUTOEXTEND_SIZE：8.0.13以后有用，当表空间满时扩容的大小，必须是4MB的倍数，最大为4G（8.0.24开始）。默认值为0，默认规则为：表空间小于1M，每次增加1页；表空间大于1M小于32M,每次增加1M；表空间大于32M，每次增加4M；
FILE_BLOCK_SIZE：定义表空间数据文件的块大小。默认为innodb_page_size，如使用默认值则只能存储未压缩的行格式（COMPACT, REDUNDANT, and DYNAMIC）。如存储压缩的表（ROW_FORMAT=COMPRESSED）则表的KEY_BLOCK_SIZE需等于FILE_BLOCK_SIZE/1024；
ENCRYPTION：是否静态加密

mysql> create table t_ts_test (id int) tablespace=ts_test;
Query OK, 0 rows affected (0.04 sec)

mysql> create table t_ts_test_1 (id int ,name varchar(255)) tablespace=ts_test;
Query OK, 0 rows affected (0.05 sec)

mysql> alter table t_ts_test_2 tablespace=ts_test;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from information_schema.innodb_tablespaces where name='ts_test'\G
*************************** 1. row ***************************
SPACE: 148
NAME: ts_test
FLAG: 18432
ROW_FORMAT: Any
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: General
FS_BLOCK_SIZE: 4096
FILE_SIZE: 131072
ALLOCATED_SIZE: 131072
AUTOEXTEND_SIZE: 0
SERVER_VERSION: 8.0.26
SPACE_VERSION: 1
ENCRYPTION: N
STATE: normal
1 row in set (0.01 sec)

mysql> select * from information_schema.files where tablespace_name='ts_test'\G
*************************** 1. row ***************************
FILE_ID: 148
FILE_NAME: /data/mysql/log/ts_test.ibd
FILE_TYPE: TABLESPACE
TABLESPACE_NAME: ts_test
TABLE_CATALOG:
TABLE_SCHEMA: NULL
TABLE_NAME: NULL
LOGFILE_GROUP_NAME: NULL
LOGFILE_GROUP_NUMBER: NULL
ENGINE: InnoDB
FULLTEXT_KEYS: NULL
DELETED_ROWS: NULL
UPDATE_COUNT: NULL
FREE_EXTENTS: 0
TOTAL_EXTENTS: 0
EXTENT_SIZE: 1048576
INITIAL_SIZE: 114688
MAXIMUM_SIZE: NULL
AUTOEXTEND_SIZE: 1048576
CREATION_TIME: NULL
LAST_UPDATE_TIME: NULL
LAST_ACCESS_TIME: NULL
RECOVER_TIME: NULL
TRANSACTION_COUNTER: NULL
VERSION: NULL
ROW_FORMAT: NULL
TABLE_ROWS: NULL
AVG_ROW_LENGTH: NULL
DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: NULL
DATA_FREE: 0
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
STATUS: NORMAL
EXTRA: NULL
1 row in set (0.00 sec)

创建表空间不在以上目录时将报错
mysql> CREATE TABLESPACE ts_tmp ADD DATAFILE '/tmp/ts_tmp.ibd' ENGINE=INNODB;
ERROR 3121 (HY000): The DATAFILE location must be in a known directory.

解决办法：将目录添加到innodb_directories变量中。注意该变量只读，需修改配置文件并重启数据库；
[mysqld]
innodb_directories="/data/mysql/data;/data/mysql/log;/tmp"

mysql> SELECT @@datadir,@@innodb_data_home_dir,@@innodb_directories;
+-------------------+------------------------+---------------------------------------+
| @@datadir | @@innodb_data_home_dir | @@innodb_directories |
+-------------------+------------------------+---------------------------------------+
| /data/mysql/data/ | /data/mysql/data | /data/mysql/data;/data/mysql/log;/tmp |
+-------------------+------------------------+---------------------------------------+
1 row in set (0.00 sec)

#MySQL将自动在指定外部路径下创建以数据库命名的目录，保存ibd文件

mysql> create table t_ts_extrnal_1 (id int,name varchar(64)) DATA DIRECTORY = '/data/mysql/log';
Query OK, 0 rows affected (0.05 sec)