ProxySQL!像C罗一样的强大!

  1. 云栖社区>
  2. 数据和云>
  3. 博客>
  4. 正文

ProxySQL!像C罗一样的强大!

技术小能手 2018-06-22 12:51:15 浏览3585
展开阅读全文

各位兄弟们,时隔多日老张又与大家见面啦。每次与大家见面,都会有好消息告诉大家,这次也不例外。前段时间出版了《MySQL王者晋级之路》一书,反响还不错。争取今年再出版一本 MongoDB 运维实战的书籍,供给那些想要学习 NoSQL 的同学们作为工作中的参考。

现在正赶上世界杯,老张最喜欢的球队是葡萄牙——最爱C罗,喜欢他那种在比赛中好强不服输的精神。我们做技术也是一样,不要因为一点困难,就放弃了当初的梦想。只有不断努力,提升自己,才能在更好的平台上实现自我价值。

今儿,老张给大家介绍一款 MySQL 的一款中间件的产品——ProxySQL,它是灵活强大的 MySQL 代理层。像C罗一样的强大,可以实现读写分离,支持 Query 路由功能,支持动态指定某个 SQL 进行 cache,支持动态加载配置、故障切换和一些 SQL的过滤功能。还有一些同类产品比如 DBproxy、MyCAT、OneProxy 等。但经过反复对比和测试之后,决定给大家介绍一款性能不谙的 MySQL 中间件产品 ProxySQL。

有关 ProxySQL 更多的详细信息可访问:https://github.com/sysown/proxysql/wiki。

接下来通过实战来全面了解一下 ProxySQL 的特性和使用场景,先介绍一下环境,我们的系统是CentOS6.7,MySQL版本是5.7.14,准备一主两从架构来配合ProxySQL。


环境配置:
192.168.56.100 Master(node1) server-id:3306100

192.168.56.101 Slave1(node2) server-id:3306101

192.168.56.103 Proxysql中间件 server-id:3306103

192.168.56.102 Slave2(node3) server-id:3306102

注:两个从库都要开启 read_only=on。

实验架构

acc3cb5ee4a03d5d5fec68ce74171f7e11b64933

ProxySQL的安装与启动

首先要安装一些依赖的软件包,配置好 Yum 源进行安装即可。

在 192.168.56.103 上执行如下操作:


yum -y install perl-DBD-MySQL

yum -y install perl-DBI

yum -y install perl-Time-HiRes

yum -y install perl-IO-Socket-SSL

ProxySQL 软件包的两个下载地址
GitHub官网:https://github.com/sysown/proxysql/releases
percona官网:https://www.percona.com/downloads/proxysql/

安装ProxySQL:

rpm -ivh proxysql-1.3.9-1-centos67.x86_64.rpm

配置文件路径为 /etc/proxysql.cnf。

启动 ProxySQL:

service proxysql start
266e4d22f2a0e70f2d786fcafd427bc48bc6df56

注:6032 是 ProxySQL 的管理端口号,6033 是对外服务的端口号。

管理用户名和密码都是默认的 admin。

关闭 ProxySQL:

service proxysql stop

查看安装版本:

88484e01201d80bbb9896bbe72939994ccb0a6fa

管理员登录命令:
/usr/local/mysql/bin/mysql -uadmin -padmin -h 127.0.0.1 -P 6032
51c618cf6e1eba2c54fae634acde6dcccd201722

可见有四个库:main、disk、stats和monitor。分别说明一下这四个库的作用。

main:内存配置数据库,即MEMORY,表里存放后端db实例、用户验证、路由规则等信息。main库中有如下信息:

3e4d2d248333c2b417544499d398507804500af5

库下的主要表:
mysql_servers—后端可以连接MySQL服务器的列表。
mysql_users—配置后端数据库的账号和监控的账号。
mysql_query_rules—指定Query路由到后端不同服务器的规则列表。

注:表名以runtime_开头的表示ProxySQL当前运行的配置内容,不能通过DML语句修改。只能修改对应的不以 runtime开头的表,然后“LOAD”使其生效,“SAVE”使其存到硬盘以供下次重启加载。

disk库—持久化磁盘的配置。
stats库—统计信息的汇总。
monitor库—一些监控的收集信息,包括数据库的健康状态等。

配置 ProxySQL 监控

首先在 master(192.168.56.100)上创建 ProxySQL 的监控账户和对外访问账户并赋予权限。

命令如下:

create user 'monitor'@'192.168.56.%' 
identified by 'monitor';grant all privileges on *.* 
to 'monitor'@'192.168.56.%' with grant option;create user 'zs'@'192.168.56.%' 
identified by 'zs';grant all privileges on *.* to 'zs'@'192.168.56.%' with grant option;flush privileges;

ProxySQL的多层配置系统
ProxySQL 有一套很完整的配置系统,方便 DBA 对线上的操作。整套配置系统分为三层,最顶层为 RUNTIME,中间层为 MEMORY 和最底层,也就是持久层的 DISK和 CONFIG FILE。

配置结构:

bd51f1a9d5251dd546dee9905b2955070fe3b5b4

RUNTIME:代表 ProxySQL 当前生效的正在使用的配置,无法直接修改这里的配置,必须要从下一层 “load” 进来。

MEMORY:MEMORY这一层上面连接 RUNTIME 层,下面连接持久化层。在这层可以正常操作 ProxySQL 配置,随便修改,不会影响生产环境。修改一个配置一般都是先在 MEMORY 层完成,然后确认正常之后再加载到 RUNTIME 和持久化到磁盘上。

DISK 和 CONFIG FILE:持久化配置信息,重启后内存中的配置信息会丢失,所以需要将配置信息保留在磁盘中。重启时,可以从磁盘快速加载回来。

介绍完这三层配置系统之后,用超管用户登录 ProxySQL 来添加主从服务器列表。

命令如下:

insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.56.100',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.56.101',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.56.102',3306);
load mysql servers to runtime;

save mysql servers to disk;

登录 ProxySQL 之后,凡是进行任何操作,都需要运行 load to runtime,从memory 加载到 runtime。然后再执行 save to disk 持久化到磁盘。

2671d8b94be3a132efa3b0e29516f72530ef6c4f

加载完成之后,三台机器都是 ONLINE 状态。

接下来继续为 ProxySQL 配置监控账号,命令如下:

set mysql-monitor_username='monitor';set mysql-monitor_password='monitor';load mysql variables to runtime;

save mysql variables to disk;

之后验证监控信息:

1c7c54c01c83c097aee5b9969508e57293c2a0b9

监控信息都已正常,没有任何报错。配置 ProxySQL 主从分组信息

这里会用到一张表 mysql_replication_hostgroups:

a804808a6bf0caaceab4cfd902eda230d69bc256

里面的 writer_hostgroup 是写入组的编号,reader_hostgroup 是读取组的编号。实验中使用 10 作为写入组,20 作为读取组编号。

insert into mysql_replication_hostgroups values (10,20,'proxy');load mysql servers to runtime;

save mysql servers to disk;

f125dc345d267c23552955e78d8623ee678f7c9d

ProxySQL会根据 server 的 read_only 的取值将服务器进行分组。read_only=0的server,master 被分到编号为 10 的写组,read_only=1 的 server,slave 则被分到编号为 20 的读组。

bd537bfee1a6f044ce39814b5088229b365e5c48

配置对外访问账号,默认指定主库,并对该用户开启事务持久化保护。

注:mysql_users 表中的 transaction_persistent 字段默认为 0

建议在创建完用户之后设置为1,避免发生脏读、幻读等现象命令如下:

insert into mysql_users(username,password,default_hostgroup) values ('zs','zs',10);update mysql_users set transaction_persistent=1 where username='zs';load mysql users to runtime;

save mysql users to disk;

验证登录的服务器就是主库:

30e4b58885c809f57fbd8e55b91da0b448160f9e

注:对外端口号需要指定为6033。
配置读写分离策略

配置读写分离策略需要使用 mysql_query_rules 表。表中的 match_pattern 字段就是代表设置的规则,destination_hostgroup 字段代表默认指定的分组,apply 代表真正执行应用规则。
把所有以 select 开头的语句全部分配到编号为 20 的读组中。select for update 会产生一个写锁,对数据查询的实效性要求高,把它分配到编号为 10 的写组中,其他所有操作都会默认路由到写组中。
命令如下:

insert into mysql_query_rules(active,match_pattern,destination_hostgroup, apply) VALUES(1,'^SELECT.*FOR UPDATE$',10,1);insert into mysql_query_rules(active,match_pattern,destination_hostgroup, apply) VALUES(1,'^SELECT',20,1);

load mysql query rules to runtime;

save mysql query rules to disk;

通过创建的对外账户zs,连接ProxySQL登录数据库。

```**






**测试读写分离**


命令如下:





`/usr/local/mysql/bin/mysql -uzs -pzs -h 192.168.56.103 -P 6033``





**查看zs库下tt的数据:**


![](http://i2.51cto.com/images/blog/201806/18/0e4e67cca5bb59d343b3872a51cd613d.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)然后再登录管理端口,通过查询stats_mysql_query_digest这张表来监控查询状态,命令如下:select * from stats_mysql_query_digest;

![](http://i2.51cto.com/images/blog/201806/18/5c7dfa40460715c314c1742c708bf0cf.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)可见这条select语句自动路由到编号为20的读组,即slave库上。


然后继续测试,通过ProxySQL登录到数据库:




`/usr/local/mysql/bin/mysql -uzs -pzs -h 192.168.56.103 -P 6033`




执行select * from zs.tt for update和update tt set name='ff' where score=100的语句操作:


 ![](http://i2.51cto.com/images/blog/201806/18/266ee481d4751223124bac4326879ac9.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)

![](http://i2.51cto.com/images/blog/201806/18/3fe8f7324f55a65b717047fae2b71107.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)

 ![](http://i2.51cto.com/images/blog/201806/18/3120aece6efc2fe3715115bfc78675e0.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)这时再登录管理端口,监控查询状态,发现都已经成功路由到了编号为10的写组,即主库,证明读写分离设置成功。

![](http://i2.51cto.com/images/blog/201806/18/1934fb4e0d0b457e718937176abf20b5.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)作为苦逼DBA的我们,无论是初学者还是已经从业多年的“老司机”,都不要急于去把每个MySQL集群架构搭建出来。在学习的过程中,一些同学总是存在一个误区,就是觉得我会搭建所有的数据库架构就非常厉害了。其实并不是这样的,架构搭建并不是我们的最终目的,作为DBA要先了解清楚自己公司的现有业务,看看公司的业务场景适合什么样的架构,要做好相应的数据库架构设计。了解好该架构的优缺点,以及在今后应用中可能出现的问题,提前做好能解决问题的预案。知己知彼,注重细节,才能避免没日没夜地加班熬夜处理那些不该发生的问题。

 ![](http://i2.51cto.com/images/blog/201806/18/e4c27fb9757e4408afba6609499d00e1.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)读写分离设置成功之后,我们可以调整权重,让某台机器承受更多的读操作。这些技巧都可以用在运维ProxySQL上面。


调整192.168.56.102 node2节点的查询权重,让更多的读请求路由到这台机器上面。

命令如下:




update mysql_servers set weight=10 where hostname='192.168.56.102';

load mysql servers to runtime;


save mysql servers to disk;


(3)因地制宜,根据实际设备情况做出选择。

下面总结了五条MySQL架构设计中的经验。


(1)根据公司现有业务设计合理架构。


(5)越简单越好,越适合公司越好。


(2)选择成熟的架构方案。


(4)考虑方案的可行性。



原文发布时间为:2018-06-22

本文作者:张甦

本文来自云栖社区合作伙伴“数据和云”,了解相关信息可以关注“数据和云”。

网友评论

登录后评论
0/500
评论
技术小能手
+ 关注
所属团队号: 数据和云