Mysql数据库表分区存储到指定磁盘路径-阿里云开发者社区

开发者社区> laoyang360> 正文

Mysql数据库表分区存储到指定磁盘路径

简介: 0. 前提: mysql5.6.6以上的版本以上的版本才支持单表指定目录,且目录权限是mysql:mysql。 在mysql中数据文件存放于在my.cnf中datadir指定的路径,使用的表引擎不同产生的文件格式、表文件个数也会有所差异。 mysql的表引擎有多种,表的扩展名也不一样,如innodb用“ .ibd”,archive用“.arc ”,csv用“.csv”等。
+关注继续查看

image.png

1.步骤详解

步骤一:设定my.cnf配置文件。

innodb_file_per_table=1


验证开关已经打开。


mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | ON |

+-----------------------+-------+

1 row in set (0.00 sec)

1

2

3

4

5

6

7

步骤二:创建指定路径存储的分区表。

CREATE TABLE orders_list2 (

 id INT AUTO_INCREMENT,

 customer_surname VARCHAR(30),

 store_id INT,

 salesperson_id INT,

 order_date DATE,

 note VARCHAR(500),

 INDEX idx (id)

) ENGINE = INNODB

 PARTITION BY LIST(store_id) (

 PARTITION p1

 VALUES IN (1, 3, 4, 17)

 INDEX DIRECTORY = '/var/orders/district1'

 DATA DIRECTORY = '/var/orders/district1',

 PARTITION p2

 VALUES IN (2, 12, 14)

 INDEX DIRECTORY = '/var/orders/district2'

 DATA DIRECTORY = '/var/orders/district2',

 PARTITION p3

 VALUES IN (6, 8, 20)

 INDEX DIRECTORY = '/var/orders/district3'

 DATA DIRECTORY = '/var/orders/district3',

 PARTITION p4

 VALUES IN (5, 7, 9, 11, 16)

 INDEX DIRECTORY = '/var/orders/district4'

 DATA DIRECTORY = '/var/orders/district4',

 PARTITION p5

 VALUES IN (10, 13, 15, 18)

 INDEX DIRECTORY = '/var/orders/district5'

 DATA DIRECTORY = '/var/orders/district5'

);

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

插入记录:


insert into orders_list2(id, customer_surname, store_id, salesperson_id, order_date, note)

values(1, "yang", 1, 1, CURDATE(), "testing");

insert into orders_list2(id, customer_surname, store_id, salesperson_id, order_date, note)

values(2, "yang", 2, 2, CURDATE(), "testing");

insert into orders_list2(id, customer_surname, store_id, salesperson_id, order_date, note)

values(6, "yang", 6, 6, CURDATE(), "testing");

insert into orders_list2(id, customer_surname, store_id, salesperson_id, order_date, note)

values(8, "yang", 8, 8, CURDATE(), "testing");

insert into orders_list2(id, customer_surname, store_id, salesperson_id, order_date, note)

values(5, "yang", 5, 5, CURDATE(), "testing");

insert into orders_list2(id, customer_surname, store_id, salesperson_id, order_date, note)

values(10, "yang", 10, 10, CURDATE(), "testing");

1

2

3

4

5

6

7

8

9

10

11

12

步骤三:到指定新路径下验证。

[root@f033b3fe25e2 orders]# tree

.

├── district1

│   └── test

│   └── orders_list2#P#p1.ibd

├── district2

│   └── test

│   └── orders_list2#P#p2.ibd


├── district3

│   └── test

│   └── orders_list2#P#p3.ibd


├── district4

│   └── test

│   └── orders_list2#P#p4.ibd


└── district5

 └── test

 └── orders_list2#P#p5.ibd

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

10 directories, 5 files

[root@f033b3fe25e2 orders]# pwd

/var/orders

1

2

3

查询验证:


mysql> explain partitions select * from orders_list2;

+----+-------------+--------------+----------------+------+---------------+------+---------+------+------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------------+----------------+------+---------------+------+---------+------+------+-------+

| 1 | SIMPLE | orders_list2 | p1,p2,p3,p4,p5 | ALL | NULL | NULL | NULL | NULL | 11 | NULL |

+----+-------------+--------------+----------------+------+---------------+------+---------+------+------+-------+

1 row in set (0.00 sec)

1

2

3

4

5

6

7

步骤四:新增分区处理。

场景假设:比如最新的数据,我们想存储到SSD硬盘上。可以通过增加指定路径的分区文件达到目的。


ALTER TABLE orders_list2  ADD PARTITION (PARTITION p6 VALUES IN  (21,22,23)

DATA DIRECTORY = '/var/ssd_testing'  INDEX DIRECTORY = '/var/ssd_testing');


[root@f033b3fe25e2 var]# tree ssd_testing/

ssd_testing/

└── test

 └── orders_list2#P#p6.ibd

1

2

3

4

5

6

7

六个分区结果:


mysql> explain partitions select * from orders_list2;

+----+-------------+--------------+-------------------+------+---------------+------+---------+------+------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------------+-------------------+------+---------------+------+---------+------+------+-------+

| 1 | SIMPLE | orders_list2 | p1,p2,p3,p4,p5,p6 | ALL | NULL | NULL | NULL | NULL | 12 | NULL |

+----+-------------+--------------+-------------------+------+---------------+------+---------+------+------+-------+

1 row in set (0.00 sec)

1

2

3

4

5

6

7

参考:https://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 29 章 监控磁盘使用
第 29 章 监控磁盘使用 目录 29.1. 判断磁盘用量 29.2. 磁盘满失败 本章讨论如何监控PostgreSQL数据库系统的磁盘使用情况。 本文转自PostgreSQL中文社区,原文链接:第 29 章 监控磁盘使用
717 0
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 29 章 监控磁盘使用_29.1. 判断磁盘用量
29.1. 判断磁盘用量 每个表都有一个主要的堆磁盘文件,大多数数据都存储在其中。如果一个表有着可能会很宽(尺寸大)的列, 则另外还有一个TOAST文件与这个表相关联, 它用于存储因为太宽而不能存储在主表里面的值(参阅第 66.2 节)。
853 0
【Mysql】Mysql数据表区分大小写问题解决方案
原因 由于有的环境下,对数据表的大小写是明确区分的,但是我在导出的时候的数据表全部都是小写的,这个时候需要对环境进行配置 解决 vim /etc/my.cnf 在[mysqld]下加入一行:lower_case_table_names=1 service mysq...
668 0
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 29 章 监控磁盘使用_29.2. 磁盘满失败
29.2. 磁盘满失败 一个数据库管理员最重要的磁盘监控任务就是确保磁盘不会写满。一个写满了的数据磁盘可能不会导致数据的崩溃,但它肯定会让系统变得不可用。如果保存 WAL 文件的磁盘变满,会发生数据库服务器致命错误并且可能发生关闭。
1092 0
表格存储 SQL 数据类型详解
本文主要介绍 Tablestore SQL中的数据类型与 MySQL 数据类型之间的映射关系。 ​ ## 背景介绍 ### Tablestore 数据类型 Tablestore 中的数据类型支持如下表所示,其中主键列支持的数据类型包括String、Integer和Binary,属性列支持的数据类型包括String、Integer、Double、Boolean和Binary。 - 主键列支持的数
49 0
Mysql数据库表分区深入详解
0、mysql数据库分区的由来? 1)传统不分区数据库痛点
20 0
+关注
348
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载