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

. 前提:

mysql5.6.6以上的版本以上的版本才支持单表指定目录,且目录权限是MySQL:mysql。 
在mysql中数据文件存放于在my.cnf中datadir指定的路径,使用的表引擎不同产生的文件格式、表文件个数也会有所差异。 
mysql的表引擎有多种,表的扩展名也不一样,如innodb用“ .ibd”,archive用“.arc ”,csv用“.csv”等。 
这里写图片描述

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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

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

2016年10月21日 20:56 思于家中床前

作者:铭毅天下 
转载请标明出处,原文地址: 
http://blog.csdn.net/laoyang360/article/details/52887016 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值