ProxySQL!像C罗相同的强壮!51CTO博客 - AG环亚娱乐

ProxySQL!像C罗相同的强壮!51CTO博客

2019年03月05日09时39分00秒 | 作者: 怀蕊 | 标签: 装备,咱们,架构 | 浏览: 1598

各位兄弟们,时隔多日老张又与咱们碰头啦。每次与咱们碰头,都会有好消息通知咱们,这次也不破例。前段时间出书了《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.102  Slave2(node3) server-id:3306102
192.168.56.103  Proxysql中间件 server-id:3306103

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

试验架构:

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



注:6032是ProxySQL的办理端口号,6033是对外效劳的端口号。

办理用户名和暗码都是默许的admin。

封闭ProxySQL:
service proxysql stop

检查装置版别:

办理员登录指令:
/usr/local/mysql/bin/mysql -uadmin -padmin -h 127.0.0.1 -P 6032

可见有四个库:main、disk、stats和monitor。别离阐明一下这四个库的效果。
main:内存装备数据库,即MEMORY,表里寄存后端db实例、用户验证、路由规矩等信息。main库中有如下信息:

库下的首要表:
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。

装备结构:

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耐久化到磁盘。

加载完结之后,三台机器都是ONLINE状况。

接下来持续为ProxySQL装备监控账号,指令如下:

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

之后验证监控信息:


监控信息都已正常,没有任何报错。

装备ProxySQL主从分组信息
这儿会用到一张表mysql_replication_hostgroups:

里边的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;

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

装备对外拜访账号,默许指定主库,并对该用户敞开事务耐久化维护。

注: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;

验证登录的效劳器就是主库:


注:对外端口号需求指定为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的数据:**
![](https://s1.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;
![](https://s1.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的句子操作:
![](https://s1.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=)
 ![](https://s1.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的写组,即主库,证明读写别离设置成功。
 ![](https://s1.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=)
 ![](https://s1.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;
![](https://s1.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要先了解清楚自己公司的现有事务,看看公司的事务场景合适什么样的架构,要做好相应的数据库架构规划。了解好该架构的优缺点,以及在往后使用中或许呈现的问题,提早做好能解决问题的预案。知己知彼,重视细节,才干防止没日没夜地加班熬夜处理那些不应发作的问题。
下面总结了五条MySQL架构规划中的经历。
(1)依据公司现有事务规划合理架构。
(2)挑选老练的架构计划。
(3)量体裁衣,依据实践设备状况做出挑选。
(4)考虑计划的可行性。
(5)越简略越好,越合适公司越好。

老张我就期望写任何东西,都能够给咱们一些启示。在工作中,能够协助到各位就够了!技能需求共享,咱们一同尽力工作,让家人过得更好!

最终说句题外话,老张近期应邀在AG环亚娱乐上线专栏《练一套正宗的MySQL降龙十八掌》,咱们感兴趣能够戳链接了解一下。

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表AG环亚娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章