mysql创建表空间_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]#