Mysql数据库表分区跨磁盘存储 表分区放到不同的磁盘 表分区存储到指定磁盘路径

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

2016年10月21日 20:56:40 4851人阅读  评论(1)  收藏  举报

  分类：

【大数据技术】（13） 

版权声明：本文为博主原创文章，未经博主允许不得转载。转载请务必加上原作者：铭毅天下，原文地址：blog.csdn.net/laoyang360 https://blog.csdn.net/wojiushiwo987/article/details/52887016

目录(?)[+]

0. 前提：

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)

• 
  

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

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'
);

• 定新路径下验证。

[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

• 
  

10 directories, 5 files
[root@f033b3fe25e2 orders]# pwd
/var/orders

• 
  

查询验证：

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)

• 
  

步骤四：新增分区处理。

场景假设：比如最新的数据，我们想存储到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

• 
  

六个分区结果：

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)

• 
  

参考：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 
如果感觉本文对您有帮助，请点击‘顶’支持一下，您的支持是我坚持写作最大的动力，谢谢！

 

解决报错 [Err] 1210 - Incorrect arguments to DATA DIRECTORY

这里报错是因为在my.cnf中配置的datadir就是在 /data/mysql/mysql-percona-5.6.21/目录下，

解决方法：换另外一个目录，成功

CREATE TABLE orders_list (
  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 = '/data1/datap1'
  DATA DIRECTORY = '/data1/datap1',
  PARTITION p2
  VALUES IN (2, 12, 14)
  INDEX DIRECTORY = '/data1/datap2'
  DATA DIRECTORY = '/data1/datap2'
);

到目录验证：

[root@xhdb01 data1]# tree /data1
/data1
├── datap1
│   └── test                                                     --多了这个目录
│       └── orders_list#P#p1.ibd                        --只有.ibd文件
└── datap2
    └── test
        └── orders_list#P#p2.ibd                        --只有.ibd文件


[root@xhdb01 data1]# tree /data/mysql/mysql-percona-5.6.21/test
/data/mysql/mysql-percona-5.6.21/test
├── district1
├── district2
├── orders_list.frm
├── orders_list.par
├── orders_list#P#p1.isl                                --比平时多了这个文件
└── orders_list#P#p2.isl                                --比平时多了这个文件

报错[Err] 1030 - Got error -1 from storage engine

INDEX DIRECTORY = '/data1/datap1'
DATA DIRECTORY = '/data1/datap1'

这里报错是因为 /data1/datap1 目录不存在，或者存在，但没有权限。

解决方法：

创建目录：    

mkdir -p /data1/datap1

mkdir -p /data1/datap2

修改权限：    

chown -R mysql:mysql /data1/datap2

chown -R mysql:mysql /data1/datap2

linux下mysql数据库存放在不同磁盘或挂载的文件夹

原创  2014年08月20日 16:27:06

• 4362

[sql]  view plain  copy

1. 一个语句搞定  

[sql]  view plain  copy

1. ln -s /mnt/data/softlink /mnt100/data/softdb  

解释：

我mysql默认数据库目录在/mnt100/data下面，想把softdb数据库放在/mnt/data/softlink下面，运行上面这句话就搞定了，很简单的

条件是：/mnt/data/下面有已经建立好的文件夹  softlink，且/mnt100/data/下没有 softdb文件夹（默认命令会创建这个快捷方式的，不用建立）

其实就是创建一个快捷方式而已

如果数据库无法创建，说明新softlink目录不是mysql用户的权限

用这个命令给予权限

[sql]  view plain  copy

1. cd /mnt/data/softlink  
2. chown -R mysql:mysql .