MySQL分区表各个分区如何创建在不同目录

 

MySQL分区表各个分区如何创建在不同目录

 

分区表无法创建在共享表空间（系统表空间，通用表空间），只能放在不同的独立表空间上。

以下是分区表不同分区放在不同目录的示例：

CREATE TABLE zkm.employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)  ENGINE = InnoDB
 PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6) DATA DIRECTORY = '/mysqldata_tmp',
    PARTITION p1 VALUES LESS THAN (11) DATA DIRECTORY = '/test_share_tbs',
    PARTITION p2 VALUES LESS THAN (16) tablespace = innodb_file_per_table,
    PARTITION p3 VALUES LESS THAN (21)                                    
);

 

(root@localhost 19:35:36) [(none)](9)> show variables like 'innodb_dire%';
+--------------------+----------------------------------+
| Variable_name      | Value                            |
+--------------------+----------------------------------+
| innodb_directories | /mysqldata_tmp/;/test_share_tbs/ |
+--------------------+----------------------------------+
1 row in set (0.01 sec)

(root@localhost 19:35:42) [(none)](9)> CREATE TABLE zkm.employees (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30),
    ->     hired DATE NOT NULL DEFAULT '1970-01-01',
    ->     separated DATE NOT NULL DEFAULT '9999-12-31',
    ->     job_code INT NOT NULL,
    ->     store_id INT NOT NULL
    -> )  ENGINE = InnoDB
    ->  PARTITION BY RANGE (store_id) (
    ->     PARTITION p0 VALUES LESS THAN (6) DATA DIRECTORY = '/mysqldata_tmp',　　　　　　-- 独立表空间，放在 /mysqldata_tmp 库名目录下
    ->     PARTITION p1 VALUES LESS THAN (11) DATA DIRECTORY = '/test_share_tbs',　　　　　　-- 独立表空间，放在 /test_share_tbs 库名目录下
    ->     PARTITION p2 VALUES LESS THAN (16) tablespace = innodb_file_per_table,　　　　　　-- 独立表空间，放在变量datadir 库名目录下
    ->     PARTITION p3 VALUES LESS THAN (21)　　　　　　　　　　　　　　　　　　　　　　　　　　-- 独立表空间，放在变量datadir 库名目录下，同接着的上边一样的效果
    -> );
Query OK, 0 rows affected (0.15 sec)

对应OS的位置：

[root@dev-app80 ~]# ll /mysqldata_tmp/zkm/
total 112
-rw-r----- 1 mysql mysql 114688 Jun 30 19:36 employees#p#p0.ibd
[root@dev-app80 ~]# ll /test_share_tbs/zkm/
total 112
-rw-r----- 1 mysql mysql 114688 Jun 30 19:36 employees#p#p1.ibd
[root@dev-app80 ~]# ll /data/mysql3306/data/zkm/
total 448
-rw-r----- 1 mysql mysql 114688 Jun 30 19:36 employees#p#p2.ibd
-rw-r----- 1 mysql mysql 114688 Jun 30 19:36 employees#p#p3.ibd
-rw-r----- 1 mysql mysql 114688 Jun 30 18:49 t1.ibd
-rw-r----- 1 mysql mysql 114688 Jun 30 18:52 t2.ibd

 

 

添加新分区防偷防爬：

alter table zkm.employees add partition ( partition p4 VALUES LESS THAN (50) DATA DIRECTORY = '/mysqldata_tmp');
alter table zkm.employees add partition ( partition p5 VALUES LESS THAN (60) tablespace = innodb_file_per_table);

(root@localhost 19:38:13) [(none)](9)> alter table zkm.employees add partition ( partition p4 VALUES LESS THAN (50) DATA DIRECTORY = '/mysqldata_tmp');
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

(root@localhost 19:38:14) [(none)](9)> alter table zkm.employees add partition ( partition p5 VALUES LESS THAN (60) tablespace = innodb_file_per_table);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

 

可惜不支持修改表的某个分区数据文件的目录。

要研究研究怎么搞。

alter table partition：https://dev.mysql.com/doc/refman/8.0/en/alter-table-partition-operations.html