青蛙学Linux—ProxySQL实现MySQL读写分离

ProxySQL实现MySQL读写分离是建立在MySQL主从复制之上的，通过ProxySQL中的路由配置将对MySQL的写操作和读操作分别分配给后端不同的MySQL主机。这里通过一个例子来演示如何使用ProxySQL实现MySQL的读写分离。

实验环境：

• DB1：操作系统CentOS 7.6.1810，IP地址192.168.0.110，MySQL版本5.7.25
• DB2：操作系统CentOS 7.6.1810，IP地址192.168.0.88，MySQL版本5.7.25
• DB3：操作系统CentOS 7.6.1810，IP地址192.168.0.85，ProxySQL版本2.0.1

其中DB1为Master，提供MySQL除了查询之外的所有操作；DB2为Slave，提供查询操作；ProxySQL运行在DB3上。

1、配置MySQL主从复制

MySQL的主从复制参考https://www.cnblogs.com/yu2006070-01/p/10336146.html。

2、创建需要的MySQL用户

ProxySQL需要两个在后端服务器中真实存在的MySQL用户，一个用于监控MySQL的健康状态（MySQL是否存活以及read-only值），一个程序用户用于操作MySQL。

在DB1和DB2上创建健康检测用户：

mysql> grant super,replication client on *.* to 'proxysql_status'@'192.168.0.85' identified by 'proxysql';

在DB1和DB2上创建程序用户：

mysql> grant all on *.* to 'myadmin'@'192.168.0.85' identified by 'myadmin_proxysql';

3、在ProxySQL中配置后端MySQL主机

ProxySQL的后端主机配置信息位于main库中的mysql_servers表中。Master和Slave通过不同的分组进行区分，这里将Master设置为hostgroup 0组，Slave设置为hostgroup 1组。

添加Master到mysql_servers：

mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,comment) values (0,'192.168.0.110',3306,1,1000,'mysql master');

• weight：权重
• max_connections：最大连接数
• comment：注释

添加Slave到mysql_servers：

mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (1,'192.168.0.88',3306,1,1000,10,'mysql slave');

• max_replication_lag：如果值大于0，ProxySQL的Monitor模块将会定期检查该Slave的复制是否延后于Master，如果延迟的值大于该字段的值，ProxySQL将会暂时避开该节点，直到该Slave赶上Master

将配置load到RUNTIME层：

mysql> load mysql servers to runtime;

4、添加监控用户和程序用户到ProxySQL

ProxySQL的程序用户配置信息位于main库中的mysql_users表中。该用户不仅用于ProxySQL操作后端MySQL，还用于ProxySQL客户端连接时登录ProxySQL。

添加程序用户：

mysql> insert into mysql_users (username,password,active,default_hostgroup,transaction_persistent) values ('myadmin','myadmin_proxysql',1,0,1);

• active：是否激活
• default_hostgroup：用户默认操作的主机组，应该为Master所在的组

将配置load到RUNTIME层：

mysql> load mysql users to runtime;

添加监控用户（修改ProxySQL变量方式）：

mysql> set mysql-monitor_username='proxysql_status';
mysql> set mysql-monitor_password='proxysql';

将配置load到RUNTIME层：

mysql> load mysql variables to runtime;

5、验证配置是否成功

此时可以通过登录ProxySQL客户端操作后端MySQL。

登录ProxySQL客户端：

[root@localhost bin]# ./mysql -h 127.0.0.1 -P 6033 -u myadmin -p

执行show databases;，如果可以看到后端MySQL中的数据库，说明ProxySQL配置成功。

这里我们执行一条select和insert语句，查看执行是否成功，后端MySQL是否新增了数据。如果都成功，说明ProxySQL的配置没有问题，接下去就可以把上面的配置save到DISK层了：

mysql> save mysql servers to disk;
mysql> save mysql users to disk;
mysql> save mysql variables to disk;

6、ProxySQL路由配置

上面我们执行了一条select语句和一条insert语句，ProxySQL有SQL统计功能，通过查看以下的表：

mysql> select * from stats_mysql_query_digest;

该表的内容如果太多，可以使用以下方法清理之前的统计信息：

mysql> select * from stats_mysql_query_digest_reset;

我们来查看下刚才的两条语句在哪个hostgroup上执行：

+-----------+------------+----------+----------------+--------------------+-----------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | client_address | digest             | digest_text                             | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
+-----------+------------+----------+----------------+--------------------+-----------------------------------------+------------+------------+------------+----------+----------+----------+
| 0         | test       | myadmin  |                | 0x646E6D49263421A0 | insert into stu (name,age) values (?,?) | 1          | 1548947351 | 1548947351 | 2822     | 2822     | 2822     |
| 0         | test       | myadmin  |                | 0x93B1321596C7ED17 | select * from stu                       | 1          | 1548947342 | 1548947342 | 1275     | 1275     | 1275     |
+-----------+------------+----------+----------------+--------------------+-----------------------------------------+------------+------------+------------+----------+----------+----------+

可以看到ProxySQL将两条语句都发送给了hostgroup 0来执行了。想要实现读写分离，我们必须通过ProxySQL的路由功能来实现。

ProxySQL路由规则支持正则。

这里我们简单的配置两条路由，将select  for update发送给Master，其余的select发送给Slave，然后其他的语句也都发送给Master：

mysql> insert into mysql_query_rules (active,match_pattern,destination_hostgroup,apply) values (1,'^select.*for update$',0,1);
mysql> insert into mysql_query_rules (active,match_pattern,destination_hostgroup,apply) values (1,'^select',1,1);

将配置load到RUNTIME层：

mysql> load mysql query rules to runtime;

接下去我们分别执行以下三条SQL语句，查看路由是否生效：

mysql> select * from stu for update;
mysql> select * from stu;
mysql> insert into stu (name,age) values ('oo',40);

查看SQL统计信息：

mysql> select * from stats_mysql_query_digest;
+-----------+------------+----------+----------------+--------------------+-----------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | client_address | digest             | digest_text                             | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
+-----------+------------+----------+----------------+--------------------+-----------------------------------------+------------+------------+------------+----------+----------+----------+
| 0         | test       | myadmin  |                | 0x646E6D49263421A0 | insert into stu (name,age) values (?,?) | 1          | 1548948929 | 1548948929 | 2887     | 2887     | 2887     |
| 1         | test       | myadmin  |                | 0x93B1321596C7ED17 | select * from stu                       | 1          | 1548948874 | 1548948874 | 3271     | 3271     | 3271     |
| 0         | test       | myadmin  |                | 0xC649FB541970AF96 | select * from stu for update            | 1          | 1548948793 | 1548948793 | 2214     | 2214     | 2214     |
+-----------+------------+----------+----------------+--------------------+-----------------------------------------+------------+------------+------------+----------+----------+----------+

可以看到select * from stu;已经转发给了hostgroup 1组了，也就是Slave。至此，读写分离配置成功。

接下去就可以将配置save到DISK层了：

mysql> save mysql query rules to disk;