使用数据目录存放表空间。
root [(none)]> create tablespace test_01 add datafile 'test_01.ibd';
Query OK, 0 rows affected (0.11 sec)
root [(none)]> use test
Database changed
root [test]> create table test01(id int) tablespace = test_01;
Query OK, 0 rows affected (0.01 sec)
root [test]> insert into test01 values (10), (20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
root [test]> show create table test01\G
*************************** 1. row ***************************
Table: test01
Create Table: CREATE TABLE `test01` (
`id` int DEFAULT NULL
) /*!50100 TABLESPACE `test_01` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
root [test]> show global variables like 'innodb_directories';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| innodb_directories | |
+--------------------+-------+
1 row in set (0.00 sec)
表空间指定数据目录外的磁盘
修改参数
innodb_directories = /data/mysql_data_3307_tablespace
重启DB
查看参数
root [(none)]> show global variables like 'innodb_directories';
+--------------------+----------------------------------+
| Variable_name | Value |
+--------------------+----------------------------------+
| innodb_directories | /data/mysql_data_3307_tablespace |
+--------------------+----------------------------------+
1 row in set (0.01 sec)
root [(none)]> use test
Database changed
root [test]> create table test02(id int) tablespace test_02;
Query OK, 0 rows affected (0.02 sec)
root [test]> insert into test02 values (10), (20);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
root [test]> show create table test02\G
*************************** 1. row ***************************
Table: test02
Create Table: CREATE TABLE `test02` (
`id` int DEFAULT NULL
) /*!50100 TABLESPACE `test_02` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
root [test]> system ls /data/mysql_data_3307_tablespace
test21.ibd