mysql oneproxy mha_mysql | 同乐学堂

最新推荐文章于 2022-05-29 08:40:15 发布

周子芥

最新推荐文章于 2022-05-29 08:40:15 发布

阅读量179

点赞数

文章标签： mysql oneproxy mha

本文链接：https://blog.csdn.net/weixin_36021459/article/details/113909434

版权

前言

为什么要分库分表？

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

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

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

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

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

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

一、安装

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

1.2、将demo.sh和oneproxy.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

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;"

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

二、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)

打赏

微信扫一扫，打赏作者吧～

周子芥

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫