MySQL中间件之ProxySQL(3)：初试读写分离

实现一个简单的读写分离

这里通过一个简单的示例实现ProxySQL的读写分离功能，算是ProxySQL的快速入门。即使是快速入门，需要配置的内容也很多，包括：后端MySQL配置、监控配置、发送SQL语句的用户、SQL语句的路由规则。所以，想要实现一个ProxySQL+MySQL，即使只实现最基本的功能，步骤也是挺多的，不过配置的逻辑都很简单。

实验环境：
OS：centos7.8
ProxySQL：2.0.14
MySQL：8.0.20

角色	主机IP	端口	server_id
ProxySQL	192.168.80.54	6032/6033	null
Master	192.168.80.54	21021	21021
Slave1	192.168.80.54	21022	21022
Slave2	192.168.80.54	21023	21023

1. 准备MySQL的主从环境

这是我是使用dbdeployer快速部署的，详情可以去看我之前的文件

dbdeployer deploy replication 8.0.20 --gtid --bind-address='0.0.0.0'

mysql 8.0 默认的认证插件是caching_sha2_password，需要改回之前版本的default_authentication_plugin

echo 'default_authentication_plugin=mysql_native_password' >>  ~/sandboxes/rsandbox_8_0_20/master/my.sandbox.cnf
echo 'default_authentication_plugin=mysql_native_password' >>  ~/sandboxes/rsandbox_8_0_20/node1/my.sandbox.cnf
echo 'default_authentication_plugin=mysql_native_password' >>  ~/sandboxes/rsandbox_8_0_20/node2/my.sandbox.cnf

从库设置read_only

echo 'read_only=1' >>  ~/sandboxes/rsandbox_8_0_20/node1/my.sandbox.cnf
echo 'read_only=1' >>  ~/sandboxes/rsandbox_8_0_20/node2/my.sandbox.cnf

重启所有实例

[root@mysql8 ~]# ~/sandboxes/rsandbox_8_0_20/restart_all 
# executing 'stop' on /root/sandboxes/rsandbox_8_0_20
stop /root/sandboxes/rsandbox_8_0_20/node1
stop /root/sandboxes/rsandbox_8_0_20/node2
stop /root/sandboxes/rsandbox_8_0_20/master
# executing 'start' on /root/sandboxes/rsandbox_8_0_20
executing 'start' on master
.. sandbox server started
executing 'start' on slave 1
.. sandbox server started
executing 'start' on slave 2
.. sandbox server started

[root@mysql8 ~]# ~/sandboxes/rsandbox_8_0_20/status_all 
REPLICATION  /root/sandboxes/rsandbox_8_0_20
master : master on  -  port	21021 (21021)
node1 : node1 on  -  port	21022 (21022)
node2 : node2 on  -  port	21023 (21023)

2.检查ProxySQL环境

启动proxysql

systemctl start proxysql.service

登陆到proxysql管理界面，--prompt重置提示符

mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin> '

确认proxysql中没有任何数据。mysql_servers，mysql_replication_hostgroups或mysql_query_rules表中为空。

admin> SELECT * FROM mysql_servers;
Empty set (0.000 sec)

admin> SELECT * from mysql_replication_hostgroups;
Empty set (0.000 sec)

admin> SELECT * from mysql_query_rules;
Empty set (0.000 sec)

3.将后端mysql节点添加到ProxySQL中

admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.80.54',21021);
Query OK, 1 row affected (0.000 sec)

admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.80.54',21022);
Query OK, 1 row affected (0.000 sec)

admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.80.54',21023);
Query OK, 1 row affected (0.000 sec)

查看这3个节点是否插入成功，以及它们的状态。

admin> select * from mysql_servers\G
*************************** 1. row ***************************
       hostgroup_id: 10
           hostname: 192.168.80.54
               port: 21021
          gtid_port: 0
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
*************************** 2. row ***************************
       hostgroup_id: 10
           hostname: 192.168.80.54
               port: 21022
          gtid_port: 0
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
*************************** 3. row ***************************
       hostgroup_id: 10
           hostname: 192.168.80.54
               port: 21023
          gtid_port: 0
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
3 rows in set (0.000 sec)

添加成功后，加载到RUNTIME，并保存到disk。ProxySQL修改表后必须执行LOAD ... TO RUNTIME才能加载到RUNTIME生效，执行save ... to disk才能将配置持久化保存到磁盘。

admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.003 sec)

admin> save mysql servers to disk;
Query OK, 0 rows affected (0.040 sec)

4.监控后端MySQL节点

添加节点之后，还需要监控后端节点。对于后端是主从复制的环境来说，这是必须的，因为ProxySQL需要通过每个节点的read_only值来自动调整它们是属于读组还是写组。

首先在后端master节点上创建一个用于监控的用户名(只需在master上创建即可，因为会复制到slave上)，这个用户名只需具有USAGE权限即可。如果还需要监控复制结构中slave是否严重延迟于master(先混个眼熟：这个俗语叫做"拖后腿"，术语叫做"replication lag")，则还需具备replication client权限。这里直接赋予这个权限。

# 在master实例上执行
[root@mysql8 ~]# mysql -uroot -pmsandbox -h127.0.0.1 -P21021

MySQL [(none)]> create user monitor@'192.168.80.%' identified by 'monitor';
Query OK, 0 rows affected (0.005 sec)

MySQL [(none)]> grant replication client on *.* to monitor@'192.168.80.%';
Query OK, 0 rows affected (0.003 sec)

然后回到ProxySQL上配置监控

admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.001 sec)

admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.001 sec)

添加后，加载到RUNTIME，并保存到disk

admin> load mysql variables to runtime;
Query OK, 0 rows affected (0.001 sec)

admin> save mysql variables to disk;
Query OK, 136 rows affected (0.004 sec)

验证监控结果：ProxySQL监控模块的指标都保存在monitor库的log表中。

以下是连接是否正常的监控(对connect指标的监控)：(在前面可能会有很多connect_error，这是因为没有配置监控信息时的错误，配置后如果connect_error的结果为NULL则表示正常)

admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 5;
+---------------+-------+------------------+-------------------------+---------------+
| hostname      | port  | time_start_us    | connect_success_time_us | connect_error |
+---------------+-------+------------------+-------------------------+---------------+
| 192.168.80.54 | 21023 | 1602662330240468 | 675                     | NULL          |
| 192.168.80.54 | 21021 | 1602662329832310 | 548                     | NULL          |
| 192.168.80.54 | 21022 | 1602662329424482 | 381                     | NULL          |
| 192.168.80.54 | 21021 | 1602662270567369 | 709                     | NULL          |
| 192.168.80.54 | 21023 | 1602662269995663 | 489                     | NULL          |
+---------------+-------+------------------+-------------------------+---------------+
5 rows in set (0.000 sec)

以下是对心跳信息的监控(对ping指标的监控)：

admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 5;
+---------------+-------+------------------+----------------------+------------+
| hostname      | port  | time_start_us    | ping_success_time_us | ping_error |
+---------------+-------+------------------+----------------------+------------+
| 192.168.80.54 | 21021 | 1602662429416400 | 137                  | NULL       |
| 192.168.80.54 | 21023 | 1602662429308793 | 187                  | NULL       |
| 192.168.80.54 | 21022 | 1602662429201595 | 180                  | NULL       |
| 192.168.80.54 | 21022 | 1602662419430132 | 133                  | NULL       |
| 192.168.80.54 | 21021 | 1602662419315822 | 187                  | NULL       |
+---------------+-------+------------------+----------------------+------------+
5 rows in set (0.000 sec)

5.对主从节点进行分组

通过监控我们可以看到，monitor.mysql_server_read_only_log日志表为空的

admin> SELECT * FROM monitor.mysql_server_read_only_log;
Empty set (0.000 sec)

这是因为还没有对ProxySQL中的节点分组：writer_hostgroup、reader_hostgroup。设置分组信息，需要修改的是main库中的mysql_replication_hostgroups表，该表只有3个字段：第一个字段名为writer_hostgroup，第二个字段为reader_hostgroup，第三个字段为注释字段，可随意写。

例如，指定写组的id为10，读组的id为20。

如果read_only=0，它们将被移至主机组10
如果read_only=1，它们将被移至主机组20

admin> INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (10,20,'cluster1');
Query OK, 1 row affected (0.000 sec)

在该配置加载到RUNTIME生效之前，先查看下各mysql server所在的组。

admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+-------+--------+--------+
| hostgroup_id | hostname      | port  | status | weight |
+--------------+---------------+-------+--------+--------+
| 10           | 192.168.80.54 | 21021 | ONLINE | 1      |
| 10           | 192.168.80.54 | 21022 | ONLINE | 1      |
| 10           | 192.168.80.54 | 21023 | ONLINE | 1      |
+--------------+---------------+-------+--------+--------+
3 rows in set (0.000 sec)
# 目前3个节点都在hostgroup_id=10的组中。

现在，将刚才mysql_replication_hostgroups表的修改加载到RUNTIME生效。

admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.005 sec)

admin> save mysql servers to disk;
Query OK, 0 rows affected (0.067 sec)

一加载，Monitor模块就会开始监控后端的read_only值，当监控到read_only值后，就会按照read_only的值将某些节点自动移动到读/写组。

admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+-------+--------+--------+
| hostgroup_id | hostname      | port  | status | weight |
+--------------+---------------+-------+--------+--------+
| 10           | 192.168.80.54 | 21021 | ONLINE | 1      |
| 20           | 192.168.80.54 | 21023 | ONLINE | 1      |
| 20           | 192.168.80.54 | 21022 | ONLINE | 1      |
+--------------+---------------+-------+--------+--------+
3 rows in set (0.000 sec)

admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10;
+---------------+-------+------------------+-----------------+-----------+-------+
| hostname      | port  | time_start_us    | success_time_us | read_only | error |
+---------------+-------+------------------+-----------------+-----------+-------+
| 192.168.80.54 | 21023 | 1602667034693600 | 266             | 1         | NULL  |
| 192.168.80.54 | 21021 | 1602667034681432 | 222             | 0         | NULL  |
| 192.168.80.54 | 21022 | 1602667034668657 | 249             | 1         | NULL  |
| 192.168.80.54 | 21021 | 1602667033204309 | 172             | 0         | NULL  |
| 192.168.80.54 | 21023 | 1602667033186378 | 210             | 1         | NULL  |
| 192.168.80.54 | 21022 | 1602667033169659 | 489             | 1         | NULL  |
| 192.168.80.54 | 21022 | 1602667031692632 | 269             | 1         | NULL  |
| 192.168.80.54 | 21021 | 1602667031680421 | 236             | 0         | NULL  |
| 192.168.80.54 | 21023 | 1602667031667752 | 226             | 1         | NULL  |
| 192.168.80.54 | 21021 | 1602667030198377 | 265             | 0         | NULL  |
+---------------+-------+------------------+-----------------+-----------+-------+
10 rows in set (0.000 sec)

6.配置mysql_users

上面的所有配置都是关于后端MySQL节点的，现在可以配置关于SQL语句的，包括：发送SQL语句的用户、SQL语句的路由规则、SQL查询的缓存、SQL语句的重写等等。

本小节是SQL请求所使用的用户配置，例如root用户。这要求我们需要先在后端MySQL节点添加好相关用户。这里以root和dev两个用户名为例。

首先，在master节点上执行：(只需master执行即可，会复制给两个slave)

MySQL [(none)]> create user root@'192.168.80.%' identified by '123';
Query OK, 0 rows affected (0.007 sec)

MySQL [(none)]> create user dev@'192.168.80.%' identified by '123';
Query OK, 0 rows affected (0.003 sec)

MySQL [(none)]> grant all on *.* to 'root'@'192.168.80.%';
Query OK, 0 rows affected (0.004 sec)

MySQL [(none)]> grant all on *.* to 'dev'@'192.168.80.%';
Query OK, 0 rows affected (0.004 sec)

然后回到ProxySQL，配置mysql_users表，将刚才的两个用户添加到该表中。

admin> insert into mysql_users(username,password,default_hostgroup) values('root','123',10);
Query OK, 1 row affected (0.000 sec)

admin> insert into mysql_users(username,password,default_hostgroup) values('dev','123',10);
Query OK, 1 row affected (0.000 sec)

admin> load mysql users to runtime;
Query OK, 0 rows affected (0.000 sec)

admin> save mysql users to disk;
Query OK, 0 rows affected (0.007 sec)

mysql_users表有不少字段，最主要的三个字段为username、password和default_hostgroup

username：前端连接ProxySQL，以及ProxySQL将SQL语句路由给MySQL所使用的用户名。
password：用户名对应的密码。可以是明文密码，也可以是hash密码。如果想使用hash密码，可以先在某个MySQL节点上执行select password(PASSWORD)，然后将加密结果复制到该字段。
default_hostgroup：该用户名默认的路由目标。例如，指定root用户的该字段值为10时，则使用root用户发送的SQL语句默认情况下将路由到hostgroup_id=10组中的某个节点。

admin> select * from mysql_users \G
*************************** 1. row ***************************
              username: root
              password: 123
                active: 1            #  注意本行
               use_ssl: 0
     default_hostgroup: 10
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1            #  注意本行
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
               comment: 
*************************** 2. row ***************************
              username: dev
              password: 123
                active: 1
               use_ssl: 0
     default_hostgroup: 10
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
               comment: 
2 rows in set (0.000 sec)

虽然本文不详细介绍mysql_users表，但上面标注了"注意本行"的两个字段必须要引起注意。

只有active=1的用户才是有效的用户。
至于transaction_persistent字段，当它的值为1时，表示事务持久化：当某连接使用该用户开启了一个事务后，那么在事务提交/回滚之前，所有的语句都路由到同一个组中，避免语句分散到不同组(更进一步的，它会自动禁用multiplexing，让同一个事务的语句从同一个连接路由出去，保证路由到同一个组的同一个节点)。在以前的版本中，默认值为0，不知道从哪个版本开始，它的默认值为1。我们期望的值为1，所以在继续下面的步骤之前，先查看下这个值，如果为0，则执行下面的语句修改为1。

update mysql_users set transaction_persistent=1 where username='root';
update mysql_users set transaction_persistent=1 where username='sqlsender';
load mysql users to runtime;
save mysql users to disk;

7.读写分离：配置路由规则

ProxySQL的路由规则非常灵活，可以基于用户、基于schema以及基于每个语句实现路由规则的定制。

本文作为入门文章，实现一个最简单的语句级路由规则，从而实现读写分离。必须注意，这只是实验，实际的路由规则绝不应该仅根据所谓的读、写操作进行分离，而是从各项指标中找出压力大、执行频繁的语句单独写规则、做缓存等等。

和查询规则有关的表有两个：mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的扩展表，1.4.7之后才支持该快速路由表。本文只介绍第一个表。

插入两个规则，目的是将select语句分离到hostgroup_id=20的读组，但由于select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，所以应该路由到hostgroup_id=10的写组。

admin> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
    -> VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),
    ->        (2,1,'^SELECT',20,1);
Query OK, 2 rows affected (0.000 sec)

admin> 
admin> load mysql query rules to runtime;
Query OK, 0 rows affected (0.000 sec)

admin> save mysql query rules to disk;
Query OK, 0 rows affected (0.007 sec)

select ... for update规则的rule_id必须要小于普通的select规则的rule_id，因为ProxySQL是根据rule_id的顺序进行规则匹配的。

8.测试读写分离

测试读：

[root@mysql8 ~]# mysql -udev -p123 -h192.168.80.54 -P6033 -e "select @@server_id;"
+-------------+
| @@server_id |
+-------------+
|       21022 |
+-------------+
[root@mysql8 ~]# mysql -udev -p123 -h192.168.80.54 -P6033 -e "select @@server_id;"
+-------------+
| @@server_id |
+-------------+
|       21023 |
+-------------+

测试写：（begin开启事务，commit结束提交）

[root@mysql8 ~]# mysql -udev -p123 -h192.168.80.54 -P6033 -e "begin;select @@server_id;commit;select @@server_id;"
+-------------+
| @@server_id |
+-------------+
|       21021 |
+-------------+
+-------------+
| @@server_id |
+-------------+
|       21022 |
+-------------+

显然，一切都按照预期进行。

最后，如果想查看路由的信息，可查询stats库中的stats_mysql_query_digest表。以下是该表的一个输出格式示例(和本文无关)。

admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------+
| hg | sum_time | count_star | digest_text                      |
+----+----------+------------+----------------------------------+
| 20 | 10675    | 14         | select @@server_id               |
| 10 | 2042     | 3          | begin                            |
| 10 | 534      | 3          | select @@server_id               |
| 10 | 349      | 3          | commit                           |
| 10 | 0        | 1          | select @@version_comment limit ? |
+----+----------+------------+----------------------------------+
5 rows in set (0.002 sec)