MySQL 指定路径创建表空间

我们在运维MySQL时常遇到由于磁盘IO压力、容量瓶颈需要将库表存放在不同磁盘上的需求。MySQL8.0版本开始，提供了指定数据文件目录创建表空间的功能。使用该功能，我们就可以很方便的通过指定路径创建表空间的方式，将表存放到指定的磁盘上。创建表空间的语法如下

CREATE [UNDO] TABLESPACE tablespace_name
		[ADD DATAFILE 'file_name']
    [AUTOEXTEND_SIZE [=] value]
 		[FILE_BLOCK_SIZE = value]
    [ENCRYPTION [=] {'Y' | 'N'}]
    [ENGINE [=] engine_name]

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 TABLESPACE ts_test ADD DATAFILE '/data/mysql/log/ts_test.ibd' ENGINE=INNODB;        
Query OK, 0 rows affected (0.02 sec)

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 8.0.21 开始，使用 DATA DIRECTORY 子句在数据目录之外创建的表和表分区仅限于 InnoDB 已知的目录，否则将报错；

已知目录可通过以下SQL检查
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 |
+-------------------+------------------------+----------------------------------+
1 row in set (0.01 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> CREATE TABLESPACE ts_tmp ADD DATAFILE '/tmp/ts_tmp.ibd' ENGINE=INNODB;
Query OK, 0 rows affected (10.03 sec)
复制

• 建表时指定外部目录

mysql> create table t_ts_extrnal (id int,name varchar(64))  DATA DIRECTORY = '/data/mysql/log';                     
Query OK, 0 rows affected (0.06 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)

[root@node1 ~]# ll /data/mysql/log/test2/
total 224
-rw-r----- 1 mysql mysql 114688 Aug 17 23:44 t_ts_extrnal_1.ibd
-rw-r----- 1 mysql mysql 114688 Aug 17 23:39 t_ts_extrnal.ibd
复制