### 实现一个简单的读写分离 这里通过一个简单的示例实现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) ``` #### 因为有悔，所以披星戴月；因为有梦，所以奋不顾身！ 个人博客首发：`easydb.net` 微信公众号：`easydb` 关注我，不走丢！