快乐学习
前程无忧、中华英才非你莫属!

Day20-MySQL架构实战-MHA+OneProxy 后续之实现水平分库,分表

前言

为什么要分库分表?

单个库数据容量太大,单个DBServer存储空间不够

单个库表太多,查询的时候,打开表操作也消耗系统资源

单个表容量太大,查询的时候,扫描行数过多,磁盘IO大,查询缓慢

单个库能承载的访问量有限,再高的访问量只能通过分库分表实现

关系型数据库分库分表目前就两种方案: 代理、客户端。

代理就不需要改动前端业务代码,客户端呢就需要改动前端代码进行数据库的分片实现。

          

一、安装

1.1、上传此压缩包到服务器,解压到/usr/local/目录下。

oneproxy-rhel5-linux64-v6.0.0-ga.tar.gz

1.2、将demo.shoneproxy.service脚本里的目录改成自行设置的安装目录,这边以/usr/local

1.3、创建调度的数据库账号:

 grant all  privileges  on  mydb3.* to 'proxy'@'%' identified by  '123456' WITH grant option;

flush privileges 

1.4、增加权限 并启动

cd /usr/local/oneproxy/

chmod +x demo.sh

./demo.sh

1.5、 确认oneproxy监听端口3307和工具管理端口4041监听正常

1.6、对用户名的密码进行加密设置

./mysqlpwd  123456

9D7E55EAF8912CCBF32069443FAC452794F8941B

1.7、编辑OneProxy的配置文件

vi  /usr/local/oneproxy/conf/proxy.conf

proxy.conf

1.8、重启oneproxy使配置生效,连接oneproxy

./oneproxy.service restart

也可以通过log查看

cd /usr/local/oneproxy/log

cat  oneproxy.log  来查看启动是否成功过!

1.9、登陆

mysql -uproxy -p123456 -P3307 -h127.0.0.1 default -c

1.10、验证负载均衡    

   mysql -h192.168.1.59 -uproxy -p123456 -P3307 mydb3 -e"select * from employee; select @@server_id;"

无需安装的轻量级数据库分库分表中间件:

http://blog.csdn.net/jerry____wang/article/details/52981745

二、OneProxy—单库水平分表

分库分表的难点:聚合

360 Altas是不支持的,参考官网:

但Onproxy是可以支持以上这些语句的。

三、OneProxy中间件注意事项

这里总结一下JAVA程序mybatis框架连接后的注意事项:

1、不支持 Server Side Cursor 接口,比如 MySQL C API 里的 Prepare、Bind、 Execute调用接口

2、不支持 use命令切换数据库

3、默认禁止 CALL, PREPARE, EXECUTE, DEALLOCATE 命令,也就是说不能用存储过程

4、单库(单实例)分表–insert/update/delete要加字段名,如insert into t1(id,name) values(1,'张三');

5、单库(单实例)分表–目前分了N张表,如果以自增id做关联查询,那么每张表的 自增id都是从1开始,在与其他表join关联查询时,数据会不准确

6、单库(单实例)分表–当where条件有分区列时,值不能有函数转换,也不能有算 术表达式,必须是原子值,否则结果不准确 。

7、分库分表(多实例)–不支持垮库join,例如user_0表在10.0.0.1机器里,现在要 join关联查询10.0.0.2机器里的money_detail表,不支持

8、分库分表(多实例)–不支持分布式事务,例如user_0表在10.0.0.1机器里,us er_1表在10.0.0.2机器里,现在想同时update更新两张表,不支持

9、读写分离 –默认读操作全部访问slave,如果想强制走主库,例如涉及金钱类的查 询操作,SQL改为select /*master*/ from t1 where id=1;

10、分库分表/分表 –where条件带分区列时,直接命中该表,如果未带分区列,会 逐一扫描所有分表(单线程),考虑性能问题,要加并行查询(多线程),SQL改为 select /*parallel*/ from t1 where name='李四'; 并行查询会增加额外的CPU消耗

11、二级分区的适用场景:先对订单号取模分1024张表,等里面的表数据达到1000万 行,再range扩容, 例如对时间字段, 分区表只保留3个月的订单,历史订单归档。

四、开始水平分表

环境准备

 

 

机器名

IP地址

配置

MHA(pxoxy)

192.168.1.59

1 Core,GB Memory,VMWARE虚拟机

master

192.168.1.60

1 Core,GB Memory,VMWARE虚拟机

slave1

192.168.1.61

1 Core,GB Memory,VMWARE虚拟机

   现在我们登录到master 的MySQL上,在“mydb3”数据库下建好如下分区表:

Create table my_range_0 (id int not null primary key, col2 int, col3 varchar(32));
Create table my_range_1 (id int not null primary key, col2 int, col3 varchar(32));
Create table my_list_0 (id int not null primary key, col2 int, col3 varchar(32));
Create table my_list_1 (id int not null primary key, col2 int, col3 varchar(32));
Create table my_hash_0 (id int not null primary key, col2 int, col3 varchar(32));
Create table my_hash_1 (id int not null primary key, col2 int, col3 varchar(32));

   然后登录到“slave1 ”的MySQL上,在“mydb3”数据库下建好如下分区表:

Create table my_range_2 (id int not null primary key, col2 int, col3 varchar(32));
Create table my_range_3 (id int not null primary key, col2 int, col3 varchar(32));
Create table my_list_2 (id int not null primary key, col2 int, col3 varchar(32));
Create table my_list_3 (id int not null primary key, col2 int, col3 varchar(32));
Create table my_list_4 (id int not null primary key, col2 int, col3 varchar(32));
Create table my_hash_2 (id int not null primary key, col2 int, col3 varchar(32));
Create table my_hash_3 (id int not null primary key, col2 int, col3 varchar(32));

 可以看到,两台MySQL服务器上都没有创建OneProxy里的三张虚拟表:“my_range”、“my_list”和“my_hash”。

 接下来我们将三张分区表的信息用JSON格式保存到文本文件(“part.txt”)中,如下所示:

 

修改配置文件中指定的part.txt

[
  {
        "table"   : "my_range",
        "pkey"    : "id",
        "type"    : "int",
        "method"  : "range",
        "partitions":
           [
               { "name" : "my_range_0", "group": "server1", "value" : "100000" },
               { "name" : "my_range_1", "group": "server1", "value" : "200000" },
               { "name" : "my_range_2", "group": "server2", "value" : "300000" },
               { "name" : "my_range_3", "group": "server2", "value" : null     }
           ]
  },
  {
        "table"   : "my_hash",
        "pkey"    : "id",
        "type"    : "int",
        "method"  : "hash",
        "partitions":
           [
               { "name" : "my_hash_0", "group": "server1" },
               { "name" : "my_hash_1", "group": "server1" },
               { "name" : "my_hash_2", "group": "server2" },
               { "name" : "my_hash_3", "group": "server2" }
           ]
  },
  {
        "table"   : "my_list",
        "pkey"    : "id",
        "type"    : "int",
        "method"  : "list",
        "partitions":
           [
               { "name" : "my_list_0", "group": "server1", "value" : ["1","2","3"] },
               { "name" : "my_list_1", "group": "server1", "value" : ["4","5","6"] },
               { "name" : "my_list_2", "group": "server2", "value" : ["7","8","9"] },
               { "name" : "my_list_3", "group": "server2", "value" : ["10","11","12"] },
               { "name" : "my_list_4", "group": "server2", "value" : [] }
           ]
  }
]

 

并准备启动OneProxy的Shell脚本文件

测试

mysql -uproxy -p123456 -P3307 -h127.0.0.1 default -c

无异常,证明配置成功!

使用SQL语句来插入几条记录看看,针对插入语句,要求语句中显式地指定列名,如下所示:

 

insert into my_range (id, col2, col3) values (100, 1, 'This is row 1');

insert into my_range (id, col2, col3) values (100100, 2, 'This is row 2');

insert into my_range (id, col2, col3) values (200100, 3, 'This is row 3');

insert into my_range (id, col2, col3) values (300100, 4, 'This is row 4');

 然后重新来查询一次“my_range”表,如下所示:

 

mysql> select * from my_range;

上述证明,我们通过前端代码查询select * from my_range表的时候,id=300100,100100被存到了2张表里。
这样后台把一张大表有规律的切成两张小表,在查询的速度上,会有成倍的提升。
MySQL中间件解析代理转发的过程消耗的性能经过测试,可以忽略不计!

在OneProxy中,可以直接使用分区的名字来进行查询,来验证一下是否每个分区各包含一条记录,也可以直接登录MySQL进行验证,如下所示:

 

mysql> select * from my_range_0;

+—–+——+—————+

| id  | col2 | col3          |

+—–+——+—————+

| 100 |    1 | This is row 1 |

+—–+——+—————+

1 row in set (0.00 sec)

 

mysql> select * from my_range_1;

+——–+——+—————+

| id     | col2 | col3          |

+——–+——+—————+

| 100100 |    2 | This is row 2 |

+——–+——+—————+

1 row in set (0.00 sec)

 

mysql> select * from my_range_2;

+——–+——+—————+

| id     | col2 | col3          |

+——–+——+—————+

| 200100 |    3 | This is row 3 |

+——–+——+—————+

1 row in set (0.00 sec)

 

mysql> select * from my_range_3;

+——–+——+—————+

| id     | col2 | col3          |

+——–+——+—————+

| 300100 |    4 | This is row 4 |

+——–+——+—————+

1 row in set (0.00 sec)

参考来源:http://www.cnblogs.com/youge-OneSQL/p/4220761.html

打赏

未经允许不得转载:同乐学堂 » Day20-MySQL架构实战-MHA+OneProxy 后续之实现水平分库,分表

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

特别的技术,给特别的你!

联系QQ:1071235258QQ群:226134712
error: Sorry,暂时内容不可复制!