ProxySQL！像C罗一样的强大！

各位兄弟们，时隔多日老张又与大家见面啦。每次与大家见面，都会有好消息告诉大家，这次也不例外。前段时间出版了《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）越简单越好，越适合公司越好。

老张我就希望写任何东西，都可以给大家一些启示。在工作中，可以帮助到各位就够了！技术需要分享，我们一起努力工作，让家人过得更好！

最后说句题外话，老张近期应邀在51CTO博客上线专栏《练一套正宗的MySQL降龙十八掌》，大家感兴趣可以戳链接了解一下。