mysql5.7创建表空间

1.创建表空间存放目录
注意创建的目录不能在data目录下
mkdir -p /opt/mysql5730/tps_hxl
修改目录属主
chown -R mysql:mysql /opt/mysql5730/tps_hxl


2.创建表空间
mysql> CREATE TABLESPACE tps_hxl ADD datafile '/opt/mysql5730/tps_hxl/tps_hxl.ibd' FILE_BLOCK_SIZE=16384  ENGINE=InnoDB;
Query OK, 0 rows affected (0.12 sec)

要是在data目录下创建会报如下的错误
mysql> CREATE TABLESPACE tps_hxl ADD datafile '/opt/mysql5730/data/tps_hxl/tps_hxl.ibd' FILE_BLOCK_SIZE=16384  ENGINE=InnoDB;
ERROR 3121 (HY000): Incorrect File Name '/opt/mysql5730/data/tps_hxl/tps_hxl.ibd'.

3.测试通用表空间文件
mysql> use db_hxl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE test_general(
    -> id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(64) NOT NULL
    -> )ENGINE=InnoDB TABLESPACE=tps_hxl DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.16 sec)

4.查看表文件
mysql> select database();
+------------+
| database() |
+------------+
| db_hxl     |
+------------+
1 row in set (0.00 sec)

mysql> system ls -l /opt/mysql5730/data/db_hxl;
total 480
-rw-rw----. 1 mysql mysql     61 Jul 10 14:30 db.opt
-rw-r-----. 1 mysql mysql   8594 Nov  9 09:00 flush_test.frm
-rw-r-----. 1 mysql mysql  98304 Nov  9 09:00 flush_test.ibd
-rw-rw----. 1 mysql mysql   8782 Jul 10 14:30 rule_01.frm
-rw-rw----. 1 mysql mysql 114688 Jul 10 14:30 rule_01.ibd
-rw-r-----. 1 mysql mysql   8624 Nov  9 15:43 tb_index_test.frm
-rw-r-----. 1 mysql mysql 114688 Nov  9 15:43 tb_index_test.ibd
-rw-rw----. 1 mysql mysql   8556 Jul 10 14:30 tb_test.frm
-rw-rw----. 1 mysql mysql  98304 Jul 10 15:29 tb_test.ibd
-rw-r-----. 1 mysql mysql   8586 Nov 10 15:29 test_general.frm

5.删除表空间
mysql> drop tablespace tps_hxl;
ERROR 1529 (HY000): Failed to drop TABLESPACE tps_hxl

先删除表
mysql> drop table test_general;
Query OK, 0 rows affected (0.07 sec)


mysql> drop tablespace tps_hxl;
Query OK, 0 rows affected (0.61 sec)

相应的idb文件也删除掉了
[root@localhost tps_hxl]# pwd
/opt/mysql5730/tps_hxl
[root@localhost tps_hxl]# ls
[root@localhost tps_hxl]#

-- The End --