ProxySQL+MGR组复制实现“自动故障恢复“和“读写分离“

电玩女神 2023-02-28 15:26 1阅读 0赞

[大神连接，点这里][Link 1]

**首先你先得有个部署好的MGR组复制集群（[mgr部署详情点我][mgr]）**  
实验中所有要用的[资源包下载地址点我][Link 2]  
这里我的IP地址就继续用我部署的mgr集群了

**主机分配：**

<table> 
 <thead> 
  <tr> 
   <th>主机名</th> 
   <th>IP地址</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>MGR-node1</td> 
   <td>192.168.182.100</td> 
  </tr> 
  <tr> 
   <td>MOR-node2</td> 
   <td>192.168.182.101</td> 
  </tr> 
  <tr> 
   <td>MGR-node3</td> 
   <td>192.168.182.102</td> 
  </tr> 
  <tr> 
   <td>proxysql-node</td> 
   <td>192.168.182.120</td> 
  </tr> 
 </tbody> 
</table>

重点再说一遍奥：  
**三台mysql集群一定要添加hosts解析和正确的主机名**

--------------------

我的三台MGRmysql版本（Server version: 5.7.31）  
我的proxysql机器的Mariadb的版本（Server version: 5.5.30）  
以及我的系统版本（CentOS Linux release 7.6.1810 ）

**如果保证和我所有版本都一样那么一定是能成功的。**

--------------------

这里可以到我的mgr组复制状态是正常的

一切准备就绪，开始正题

--------------------

# 一 部署 ProxySQL #

## 1.编辑下载mariadb的repo ##

[root@proxysql-node ~]# cat /etc/yum.repos.d/mariadb.repo
    [mariadb]
    name = MariaDB
    baseurl = https://mirrors.ustc.edu.cn/mariadb/yum/10.1/centos7-amd64/
    gpgkey=https://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
    gpgcheck=1

记得重建yum缓存，生效repo

\[root@ProxySQL-node ~\]\# yum install -y MariaDB-client

============================================================================  
如果遇到报错：

Error: MariaDB-compat conflicts with 1:mariadb-libs-5.5.60-1.el7_5.x86_64
     You could try using --skip-broken to work around the problem
     You could try running: rpm -Va --nofiles --nodigest

解决办法：

[root@ProxySQL-node ~]# rpm -qa|grep mariadb
    mariadb-libs-5.5.60-1.el7_5.x86_64

[root@ProxySQL-node ~]# rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64 --nodeps
    [root@ProxySQL-node ~]# yum install -y MariaDB-client

## 2.安装proxysql ##

proxysql的rpm包下载地址: https://pan.baidu.com/s/1S1\_b5DKVCpZSOUNmtCXrrg  
提取密码: 5t1c

[root@ProxySQL-node ~]# yum install -y perl-DBI perl-DBD-MySQL
    [root@ProxySQL-node ~]# rpm -ivh proxysql-1.4.8-1-centos7.x86_64.rpm --force

启动proxysql

[root@ProxySQL-node ~]# /etc/init.d/proxysql start
    Starting ProxySQL: DONE!
    [root@ProxySQL-node ~]# ss -lntup|grep proxy 
    tcp    LISTEN     0      128       *:6080                  *:*                   users:(("proxysql",pid=29931,fd=11))
    tcp    LISTEN     0      128       *:6032                  *:*                   users:(("proxysql",pid=29931,fd=28))
    tcp    LISTEN     0      128       *:6033                  *:*                   users:(("proxysql",pid=29931,fd=27))
    tcp    LISTEN     0      128       *:6033                  *:*                   users:(("proxysql",pid=29931,fd=26))
    tcp    LISTEN     0      128       *:6033                  *:*                   users:(("proxysql",pid=29931,fd=25))
    tcp    LISTEN     0      128       *:6033                  *:*                   users:(("proxysql",pid=29931,fd=24))

[root@ProxySQL-node ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
    ............
    ............

MySQL [(none)]> show databases;
    +-----+---------------+-------------------------------------+
    | seq | name          | file                                |
    +-----+---------------+-------------------------------------+
    | 0   | main          |                                     |
    | 2   | disk          | /var/lib/proxysql/proxysql.db       |
    | 3   | stats         |                                     |
    | 4   | monitor       |                                     |
    | 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
    +-----+---------------+-------------------------------------+
    5 rows in set (0.000 sec)
     
    接着初始化Proxysql，将之前的proxysql数据都删除
     
    MySQL [(none)]> delete from scheduler ;
    Query OK, 0 rows affected (0.000 sec)
      
    MySQL [(none)]> delete from mysql_servers;
    Query OK, 3 rows affected (0.000 sec)
      
    MySQL [(none)]> delete from mysql_users;
    Query OK, 1 row affected (0.000 sec)
      
    MySQL [(none)]> delete from mysql_query_rules;
    Query OK, 0 rows affected (0.000 sec)
      
    MySQL [(none)]> delete from mysql_group_replication_hostgroups ;
    Query OK, 1 row affected (0.000 sec)
      
    MySQL [(none)]> LOAD MYSQL VARIABLES TO RUNTIME;
    Query OK, 0 rows affected (0.000 sec)
      
    MySQL [(none)]> SAVE MYSQL VARIABLES TO DISK;
    Query OK, 94 rows affected (0.175 sec)
      
    MySQL [(none)]> LOAD MYSQL SERVERS TO RUNTIME;
    Query OK, 0 rows affected (0.003 sec)
      
    MySQL [(none)]> SAVE MYSQL SERVERS TO DISK;
    Query OK, 0 rows affected (0.140 sec)
      
    MySQL [(none)]> LOAD MYSQL USERS TO RUNTIME;
    Query OK, 0 rows affected (0.000 sec)
      
    MySQL [(none)]> SAVE MYSQL USERS TO DISK;
    Query OK, 0 rows affected (0.050 sec)
      
    MySQL [(none)]> LOAD SCHEDULER TO RUNTIME;
    Query OK, 0 rows affected (0.000 sec)
      
    MySQL [(none)]> SAVE SCHEDULER TO DISK;
    Query OK, 0 rows affected (0.096 sec)
      
    MySQL [(none)]> LOAD MYSQL QUERY RULES TO RUNTIME;
    Query OK, 0 rows affected (0.000 sec)
      
    MySQL [(none)]> SAVE MYSQL QUERY RULES TO DISK;
    Query OK, 0 rows affected (0.156 sec)

## 3.在数据库端建立proxysql登入需要的帐号 （在三个MGR任意一个节点上操作，会自动同步到其他节点） ##

[root@MGR-node1 ~]# mysql -p123456
    .........
    mysql> CREATE USER 'proxysql'@'%' IDENTIFIED BY 'proxysql';   
    Query OK, 0 rows affected (0.07 sec)
     
    mysql> GRANT ALL ON * . * TO  'proxysql'@'%';
    Query OK, 0 rows affected (0.06 sec)
     
    mysql> create user 'sbuser'@'%' IDENTIFIED BY 'sbpass';   
    Query OK, 0 rows affected (0.05 sec)
     
    mysql> GRANT ALL ON * . * TO 'sbuser'@'%'; 
    Query OK, 0 rows affected (0.08 sec)
     
    mysql> FLUSH PRIVILEGES;   
    Query OK, 0 rows affected (0.07 sec)

## 4.创建检查MGR节点状态的函数和视图 （在三个MGR任意一个节点上操作，会自动同步到其他节点） ##

在MGR-node1节点上，创建系统视图sys.gr\_member\_routing\_candidate\_status，该视图将为ProxySQL提供组复制相关的监控状态指标。  
下载addition\_to\_sys.sql脚本，在MGR-node1节点执行如下语句导入MySQL即可 (在mgr-node1节点的mysql执行后，会同步到其他两个节点上)。

下载地址: https://pan.baidu.com/s/1bNYHtExy2fmqwvEyQS3sWg  
提取密码：wst7

导入addition\_to\_sys.sql文件数据

[root@MGR-node1 ~]# mysql -p123456 < /root/addition_to_sys.sql
    mysql: [Warning] Using a password on the command line interface can be insecure.
     
    在三个mysql节点上可以查看该视图：
    [root@MGR-node1 ~]# mysql -p123456
    ............
    mysql> select * from sys.gr_member_routing_candidate_status;
    +------------------+-----------+---------------------+----------------------+
    | viable_candidate | read_only | transactions_behind | transactions_to_cert |
    +------------------+-----------+---------------------+----------------------+
    | YES              | NO        |                   0 |                    0 |
    +------------------+-----------+---------------------+----------------------+
    1 row in set (0.01 sec)

## 5.在proxysql中增加帐号 ##

root@ProxySQL-node ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
    ...........
    MySQL [(none)]> INSERT INTO MySQL_users(username,password,default_hostgroup) VALUES ('proxysql','proxysql',1);
    Query OK, 1 row affected (0.000 sec)
      
    MySQL [(none)]> UPDATE global_variables SET variable_value='proxysql' where variable_name='mysql-monitor_username';  
    Query OK, 1 row affected (0.001 sec)
      
    MySQL [(none)]> UPDATE global_variables SET variable_value='proxysql' where variable_name='mysql-monitor_password';
    Query OK, 1 row affected (0.002 sec)
      
    MySQL [(none)]> LOAD MYSQL SERVERS TO RUNTIME;
    Query OK, 0 rows affected (0.006 sec)
      
    MySQL [(none)]> SAVE MYSQL SERVERS TO DISK;
    Query OK, 0 rows affected (0.387 sec)

测试一下能否正常登入数据库

[root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e"select @@hostname"
    +------------+
    | @@hostname |
    +------------+
    | MGR-node1  |
    +------------+

===================================================================  
如果上面测试登录时报错:

[root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e"select @@hostname"
    ERROR 1045 (28000): ProxySQL Error: Access denied for user 'proxysql'@'127.0.0.1' (using password: YES)

但是检查发现，明明用户名和密码已经修改成proxysql:proxysql了

MySQL [(none)]> select * from global_variables;  
    ..........
    | mysql-interfaces                                    | 0.0.0.0:6033       |
    | mysql-default_schema                                | information_schema |
    | mysql-stacksize                                     | 1048576            |
    | mysql-server_version                                | 5.5.30             |
    | mysql-connect_timeout_server                        | 3000               |
    | mysql-monitor_username                              | proxysql           |
    | mysql-monitor_password                              | proxysql           |
     
    解决办法: 依次执行下面的命令
    MySQL [(none)]> LOAD MYSQL VARIABLES TO RUNTIME;
    MySQL [(none)]> SAVE MYSQL VARIABLES TO DISK;
     
    MySQL [(none)]> LOAD MYSQL SERVERS TO RUNTIME;
    MySQL [(none)]> SAVE MYSQL SERVERS TO DISK;
     
    MySQL [(none)]> LOAD MYSQL USERS TO RUNTIME;
    MySQL [(none)]> SAVE MYSQL USERS TO DISK;
     
    MySQL [(none)]> LOAD SCHEDULER TO RUNTIME;
    MySQL [(none)]> SAVE SCHEDULER TO DISK;
     
    MySQL [(none)]> LOAD MYSQL QUERY RULES TO RUNTIME;
    MySQL [(none)]> SAVE MYSQL QUERY RULES TO DISK;

=========================================================  
如果测试登录再出现：

[root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e"select @@hostname"
    ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching hostgroup 1 after 10000ms
     
    这是因为后端三个mysql的MGR节点还没有加入到proxysql中的原因，再进行完下面的步骤"配置proxysql"后就可以了
    [root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e"select @@hostname"
    +------------+
    | @@hostname |
    +------------+
    | MGR-node1  |
    +------------+

## 6.配置proxysql ##

[root@ProxySQL-node ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 
    .............
    MySQL [(none)]> delete from mysql_servers;
    Query OK, 3 rows affected (0.000 sec)
      
    MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.182.100',3306);
    Query OK, 1 row affected (0.001 sec)
      
    MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.182.101',3306);
    Query OK, 1 row affected (0.000 sec)
      
    MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.182.102',3306);
    Query OK, 1 row affected (0.000 sec)
      
    MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(2,'192.168.182.100',3306);
    Query OK, 1 row affected (0.000 sec)
      
    MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(2,'192.168.182.101',3306);
    Query OK, 1 row affected (0.000 sec)
      
    MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(2,'192.168.182.102',3306);
    Query OK, 1 row affected (0.000 sec)
      
    MySQL [(none)]> select * from  mysql_servers ;
    
    +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | hostgroup_id | hostname        | port | status       | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
    +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | 1            | 192.168.182.100 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.182.101 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.182.102 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.100 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.101 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.102 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    6 rows in set (0.00 sec)

hostgroup\_id = 1代表write group，针对我们提出的限制，这个地方只配置了一个节点；  
hostgroup\_id = 2代表read group，包含了MGR的所有节点,目前只是Onlinle的，等配置过scheduler后，status就会有变化 。

对于上面的hostgroup配置，默认所有的写操作会发送到hostgroup\_id为1的online节点，也就是发送到写节点上。  
所有的读操作，会发送为hostgroup\_id为2的online节点。

需要确认一下没有使用proxysql的读写分离规则（因为之前测试中配置了这个地方，所以需要删除，以免影响后面的测试）。

MySQL [(none)]> delete from mysql_query_rules;
    Query OK, 2 rows affected (0.000 sec)
      
    MySQL [(none)]> commit;
    Query OK, 0 rows affected (0.000 sec)

最后需要将global\_variables，mysql\_servers、mysql\_users表的信息加载到RUNTIME，更进一步加载到DISK:

MySQL [(none)]> LOAD MYSQL VARIABLES TO RUNTIME;
    Query OK, 0 rows affected (0.001 sec)
      
    MySQL [(none)]> SAVE MYSQL VARIABLES TO DISK;
    Query OK, 94 rows affected (0.080 sec)
      
    MySQL [(none)]> LOAD MYSQL SERVERS TO RUNTIME;
    Query OK, 0 rows affected (0.003 sec)
      
    MySQL [(none)]> SAVE MYSQL SERVERS TO DISK;
    Query OK, 0 rows affected (0.463 sec)
      
    MySQL [(none)]> LOAD MYSQL USERS TO RUNTIME;
    Query OK, 0 rows affected (0.001 sec)
      
    MySQL [(none)]> SAVE MYSQL USERS TO DISK;
    Query OK, 0 rows affected (0.134 sec)
     
    再次验证proxysql登录
    [root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e"select @@hostname"
    +------------+
    | @@hostname |
    +------------+
    | MGR-node1  |
    +------------+

## 7. 配置scheduler-模式规则 ##

首先，请在Github地址https://github.com/ZzzCrazyPig/proxysql\_groupreplication\_checker下载相应的脚本  
这个地址有三个脚本可供下载也可以直接下载我文章开头的集合包：

--------------------

proxysql\_groupreplication\_checker.sh：用于multi-primary模式，可以实现读写分离，以及故障切换，同一时间点多个节点可以多写；

--------------------

gr\_mw\_mode\_cheker.sh：用于multi-primary模式，可以实现读写分离，以及故障切换，不过在同一时间点只能有一个节点能写；

--------------------

gr\_sw\_mode\_checker.sh：用于single-primary模式，可以实现读写分离，以及故障切换；

--------------------

**由于这里实验的环境是multi-primary模式，所以选择proxysql\_groupreplication\_checker.sh脚本。**

三个脚本我已打包放在了百度云盘上，下载地址：https://pan.baidu.com/s/1lUzr58BSA\_U7wmYwsRcvzQ  
提取密码：9rm7 ，地址失效的话选择我文章开头的集合包

将下载的脚本proxysql\_groupreplication\_checker.sh放到目录/var/lib/proxysql/下，并增加可以执行的权限:

[root@ProxySQL-node ~]# chmod a+x /var/lib/proxysql/proxysql_groupreplication_checker.sh
    [root@ProxySQL-node ~]# ll /var/lib/proxysql/proxysql_groupreplication_checker.sh 
    -rwxr-xr-x 1 root root 6081 Feb 20 14:25 /var/lib/proxysql/proxysql_groupreplication_checker.sh

最后，在proxysql的scheduler表里面加载如下记录，然后加载到RUNTIME使其生效，同时还可以持久化到磁盘:  
执行语句"

INSERT INTO scheduler(id,interval_ms,filename,arg1,arg2,arg3,arg4, arg5)
    VALUES (1,'10000','/var/lib/proxysql/proxysql_groupreplication_checker.sh','1','2','1','0','/var/lib/proxysql/proxysql_groupreplication_checker.log');"

如下：

[root@ProxySQL-node ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
    ..............
    MySQL [(none)]> INSERT INTO scheduler(id,interval_ms,filename,arg1,arg2,arg3,arg4, arg5) VALUES (1,'10000','/var/lib/proxysql/proxysql_groupreplication_checker.sh','1','2','1','0','/var/lib/proxysql/proxysql_groupreplication_checker.log');
    Query OK, 1 row affected (0.000 sec)
     
    MySQL [(none)]> select * from scheduler;
    +----+--------+-------------+--------------------------------------------------------+------+------+------+------+---------------------------------------------------------+---------+
    | id | active | interval_ms | filename                                               | arg1 | arg2 | arg3 | arg4 | arg5                                                    | comment |
    +----+--------+-------------+--------------------------------------------------------+------+------+------+------+---------------------------------------------------------+---------+
    | 1  | 1      | 10000       | /var/lib/proxysql/proxysql_groupreplication_checker.sh | 1    | 2    | 1    | 0    | /var/lib/proxysql/proxysql_groupreplication_checker.log |         |
    +----+--------+-------------+--------------------------------------------------------+------+------+------+------+---------------------------------------------------------+---------+
    1 row in set (0.000 sec)
     
    MySQL [(none)]> LOAD SCHEDULER TO RUNTIME;
    Query OK, 0 rows affected (0.001 sec)
     
    MySQL [(none)]> SAVE SCHEDULER TO DISK;
    Query OK, 0 rows affected (0.118 sec)

==============================================================================  
scheduler各column的说明：  
active : 1: enable scheduler to schedule the script we provide  
interval\_ms : invoke one by one in cycle (eg: 5000(ms) = 5s represent every 5s invoke the script)  
filename: represent the script file path  
arg1~arg5: represent the input parameters the script received

脚本proxysql\_groupreplication\_checker.sh对应的参数说明如下:  
arg1 is the hostgroup\_id for write  
arg2 is the hostgroup\_id for read  
arg3 is the number of writers we want active at the same time  
arg4 represents if we want that the member acting for writes is also candidate for reads  
arg5 is the log file

schedule信息加载后，就会分析当前的环境，mysql\_servers中显示出当前只有192.168.182.100是可以写的，  
192.168.182.101和192.168.182.102是用来读的。

MySQL [(none)]> select * from  mysql_servers ;              //上面操作后，稍等一会儿后执行此命令才会有下面的结果
    +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | hostgroup_id | hostname        | port | status       | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
    +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | 1            | 192.168.182.100 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.182.101 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.182.102 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.100 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.101 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.102 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    6 rows in set (0.00 sec)

因为schedule的arg4，我这里设为了0，就表示可写的节点不能用于读。那我将arg4设置为1试一下：

MySQL [(none)]> update scheduler set arg4=1;
    Query OK, 1 row affected (0.000 sec)
     
    MySQL [(none)]> select * from scheduler;
    +----+--------+-------------+--------------------------------------------------------+------+------+------+------+---------------------------------------------------------+---------+
    | id | active | interval_ms | filename                                               | arg1 | arg2 | arg3 | arg4 | arg5                                                    | comment |
    +----+--------+-------------+--------------------------------------------------------+------+------+------+------+---------------------------------------------------------+---------+
    | 1  | 1      | 10000       | /var/lib/proxysql/proxysql_groupreplication_checker.sh | 1    | 2    | 1    | 1    | /var/lib/proxysql/proxysql_groupreplication_checker.log |         |
    +----+--------+-------------+--------------------------------------------------------+------+------+------+------+---------------------------------------------------------+---------+
    1 row in set (0.000 sec)
     
    MySQL [(none)]> SAVE SCHEDULER TO DISK;
    Query OK, 0 rows affected (0.286 sec)
     
    MySQL [(none)]> LOAD SCHEDULER TO RUNTIME;
    Query OK, 0 rows affected (0.000 sec)
     
    MySQL [(none)]> select * from  mysql_servers;          //上面操作后，稍微等一会儿执行此命令才会有下面的结果
    +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | hostgroup_id | hostname        | port | status       | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
    +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | 1            | 192.168.182.100 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.182.101 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.182.102 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.100 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.101 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.102 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    6 rows in set (0.00 sec)

arg4设置为1之后，192.168.182.100节点用来写的同时，也可以被用来读。

便于下面的测试还是将arg4设为0：

MySQL [(none)]> update scheduler set arg4=0;
    Query OK, 1 row affected (0.000 sec)
     
    MySQL [(none)]> SAVE SCHEDULER TO DISK;
    Query OK, 0 rows affected (0.197 sec)
     
    MySQL [(none)]> LOAD SCHEDULER TO RUNTIME;
    Query OK, 0 rows affected (0.000 sec)
     
    MySQL [(none)]> select * from  mysql_servers;             //稍微等一会儿执行此命令，才会有下面的结果
    +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | hostgroup_id | hostname        | port | status       | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
    +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | 1            | 192.168.182.100 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.182.101 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.182.102 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.100 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.101 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.102 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    6 rows in set (0.00 sec)

各个节点的gr\_member\_routing\_candidate\_status视图也显示了当前节点是否是正常状态的，  
proxysql就是读取的这个视图的信息来决定此节点是否可用。

[root@MGR-node1 ~]# mysql -p123456 
    ...........
    mysql> select * from sys.gr_member_routing_candidate_status\G;
    *************************** 1. row ***************************
        viable_candidate: YES
               read_only: NO
     transactions_behind: 0
    transactions_to_cert: 0
    1 row in set (0.00 sec)
     
    ERROR:
    No query specified

## 8.设置读写分离 ##

MySQL [(none)]> insert into mysql_query_rules (active, match_pattern, destination_hostgroup, apply) values (1,"^SELECT",2,1);
    Query OK, 1 row affected (0.001 sec)
     
    MySQL [(none)]> LOAD MYSQL QUERY RULES TO RUNTIME;
    Query OK, 0 rows affected (0.001 sec)
     
    MySQL [(none)]> SAVE MYSQL QUERY RULES TO DISK;
    Query OK, 0 rows affected (0.264 sec)

解释说明:  
match\_pattern的规则是基于正则表达式的，  
active表示是否启用这个sql路由项，  
match\_pattern就是我们正则匹配项，  
destination\_hostgroup表示我们要将该类sql转发到哪些mysql上面去，这里我们将select转发到group 2，。  
apply为1表示该正则匹配后，将不再接受其他匹配，直接转发。

对于for update需要在gruop1上执行，可以加上规则：

MySQL [(none)]> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT.*FOR UPDATE$',1,1);
    Query OK, 1 row affected (0.001 sec)

在proxysql本机或其他客户机上检查下，select 语句，一直连接的是192.168.182.101和192.168.182.102

[root@MGR-node3 ~]# mysql -uproxysql -pproxysql -h192.168.182.120 -P6033 -e "select @@hostname"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +------------+
    | @@hostname |
    +------------+
    | MGR-node3  |
    +------------+
    [root@MGR-node3 ~]# mysql -uproxysql -pproxysql -h192.168.182.120 -P6033 -e "select @@hostname"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +------------+
    | @@hostname |
    +------------+
    | MGR-node2  |
    +------------+
    [root@MGR-node3 ~]# mysql -uproxysql -pproxysql -h192.168.182.120 -P6033 -e "select @@hostname"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +------------+
    | @@hostname |
    +------------+
    | MGR-node2  |
    +------------+
    [root@MGR-node3 ~]# mysql -uproxysql -pproxysql -h192.168.182.120 -P6033 -e "select @@hostname"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +------------+
    | @@hostname |
    +------------+
    | MGR-node3  |
    +------------+

## 9.验证数据的读写分离效果 ##

[root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h192.168.182.120 -P6033 -e "select @@hostname" 
    +------------+
    | @@hostname |
    +------------+
    | MGR-node2  |
    +------------+
    [root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h192.168.182.120 -P6033 -e "select * from xingming.ziliao"
    +-----+-----------+
    | id  | name      |
    +-----+-----------+
    |   1 | wangshibo |
    |   2 | guohuihui |
    |  11 | beijing   |
    | 100 | anhui     |
    +-----+-----------+
     
    [root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h192.168.182.120 -P6033 -e "delete from xingming.ziliao where id=1;" 
    [root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h192.168.182.120 -P6033 -e "delete from xingming.ziliao where id=2;"
    [root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h192.168.182.120 -P6033 -e "select * from xingming.ziliao" 
    +-----+---------+
    | id  | name    |
    +-----+---------+
    |  11 | beijing |
    | 100 | anhui   |
    +-----+---------+
     
    [root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h192.168.182.120 -P6033 -e 'insert into xingming.ziliao values(21,"zhongguo"),(22,"xianggang"),(23,"taiwan");'
     
    [root@ProxySQL-node ~]# mysql -uproxysql -pproxysql -h192.168.182.120 -P6033 -e "select * from xingming.ziliao" +-----+-----------+
    | id  | name      |
    +-----+-----------+
    |  11 | beijing   |
    |  21 | zhongguo  |
    |  22 | xianggang |
    |  23 | taiwan    |
    | 100 | anhui     |

最后在proxysql管理端查看读写分离情况

[root@ProxySQL-node ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032
    ..........
    MySQL [(none)]> select hostgroup,username,digest_text,count_star from stats_mysql_query_digest;
    +-----------+----------+------------------------------------------------------+------------+
    | hostgroup | username | digest_text                                          | count_star |
    +-----------+----------+------------------------------------------------------+------------+
    | 1         | proxysql | insert into xingming.ziliao values(?,?),(?,?),(?,?)       | 1          |
    | 1         | proxysql | insert into xingming.ziliao values(?,yangyang)            | 1          |
    | 1         | proxysql | delete from kevin.haha where id=?                    | 2          |
    | 1         | proxysql | select @@version_comment limit ?                     | 120        |
    | 1         | proxysql | KILL ?                                               | 8          |
    | 1         | proxysql | select @@hostname                                    | 11         |
    | 1         | proxysql | KILL QUERY ?                                         | 10         |
    | 2         | proxysql | select @@hostname, sleep(?)                          | 53         |
    | 1         | proxysql | insert into kevin.haha values(?,yangyang),(?,shikui) | 2          |
    | 1         | proxysql | show databases                                       | 1          |
    | 2         | proxysql | select @@hostname                                    | 31         |
    | 2         | proxysql | select * from kevin.haha                             | 4          |
    | 1         | proxysql | insert into kevin.haha values(?,wawa)                | 3          |
    +-----------+----------+------------------------------------------------------+------------+
    13 rows in set (0.002 sec)
     
    MySQL [(none)]> select * from  mysql_servers;
    +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | hostgroup_id | hostname      | port | status       | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
    +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | 1            | 192.168.182.100 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.182.101 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.182.102 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.100 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.101 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.102 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    6 rows in set (0.000 sec)

通过上面可以看到：  
写操作都分配到了group1组内，即写操作分配到192.168.182.100节点上。  
读操作都分配到了group2组内，即读操作分配到192.168.182.101和192.168.182.102节点上。

## 10.设置故障应用无感应 ##

在上面的读写分离规则中，我设置了192.168.182.100为可写节点，192.168.182.101,192.168.182.102为只读节点  
如果此时192.168.182.100变成只读模式的话，应用能不能直接连到其它的节点进行写操作？

现手动将192.168.182.100变成只读模式：

[root@MGR-node1 ~]# mysql -p123456
    ........
    mysql> set global read_only=1;
    Query OK, 0 rows affected (0.00 sec)

接着观察一下mysql\_servers的状态，自动将group1的192.168.182.101改成了online，group2的192.168.182.100和192.168.182.102变成online了，就表示将192.168.182.101变为可写节点，其它两个节点变为只读节点了。

[root@ProxySQL-node ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032
    ........
    MySQL [(none)]> select * from  mysql_servers;
    +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | hostgroup_id | hostname      | port | status       | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
    +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | 1            | 192.168.182.100 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.182.101 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.182.102 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.100 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.101 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.102 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    6 rows in set (0.001 sec)

通过模拟的连接也可以看到select语句都连接到192.168.182.100和192.168.182.102进行了。 (模拟时可以稍微间隔一段时间，快速测试可能会连接同一个读节点)

[root@MGR-node3 ~]# mysql -uproxysql -pproxysql -h192.168.182.120 -P6033 -e "select @@hostname"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +------------+
    | @@hostname |
    +------------+
    | MGR-node3  |
    +------------+
    [root@MGR-node3 ~]# mysql -uproxysql -pproxysql -h192.168.182.120 -P6033 -e "select @@hostname"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +------------+
    | @@hostname |
    +------------+
    | MGR-node1  |
    +------------+
    [root@MGR-node3 ~]# mysql -uproxysql -pproxysql -h192.168.182.120 -P6033 -e "select @@hostname"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +------------+
    | @@hostname |
    +------------+
    | MGR-node3  |
    +------------+
    [root@MGR-node3 ~]# mysql -uproxysql -pproxysql -h192.168.182.120 -P6033 -e "select @@hostname"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +------------+
    | @@hostname |
    +------------+
    | MGR-node1  |
    +------------+
    [root@MGR-node3 ~]# mysql -uproxysql -pproxysql -h192.168.182.120 -P6033 -e "select @@hostname"
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +------------+
    | @@hostname |
    +------------+
    | MGR-node1  |
    +------------+

然后再将将192.168.182.100变为可写模式后，mysql\_servers也恢复过来了。

[root@MGR-node1 ~]# mysql -p123456
    ........
    mysql> set global read_only=0;
    Query OK, 0 rows affected (0.00 sec)

接着观察一下mysql\_servers的状态

[root@ProxySQL-node ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032
    .........
    MySQL [(none)]> select * from  mysql_servers;
    +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | hostgroup_id | hostname      | port | status       | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
    +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | 1            | 192.168.182.100 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.182.101 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.182.102 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.100 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.101 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.102 | 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    6 rows in set (0.000 sec)

经过测试将192.168.182.100节点停止组复制（stop group\_replication）或者该节点宕机(mysql服务挂掉)后，mysql\_servers表的信息也会正常的切换新的节点。  
待192.168.182.100恢复再加入到组复制后，mysql\_servers也会正常的将192.168.182.100改成online状态。

======================================================================================================  
可能出现的问题:

mysql>  select * from  mysql_servers ;
    +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | hostgroup_id | hostname      | port | status       | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
    +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | 1            | 192.168.182.100 | 3306 | OFFLINE_HARD | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.100 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.101 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.102 | 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    4 rows in set (0.00 sec)

也就是说，可能遇到上面所有节点都offline了的情况，查看错误日志如下：

[root@ProxySQL-node ~]# tail -f /var/lib/proxysql/proxysql.log
    ........
    [2019-02-18 16:23:52] read node [hostgroup_id: 2, hostname: 192.168.182.102, port: 3306, isOK: 0] is not OK, we will set it's status to be 'OFFLINE_SOFT' ERROR 1142 (42000) at line 1: SELECT command denied to user 'proxysql'@'192.168.182.120' for table 'gr_member_routing_candidate_status' [2019-02-18 16:23:55] current write node [hostgroup_id: 2, hostname: 192.168.182.100, port: 3306, isOK: 0] is not OK, we need to do switch over ERROR 1142 (42000) at line 1: SELECT command denied to user 'proxysql'@'192.168.182.120' for table 'gr_member_routing_candidate_status' [2019-02-18 16:23:55] read node [hostgroup_id: 2, hostname: 192.168.182.102, port: 3306, isOK: 0] is not OK, we will set it's status to be 'OFFLINE_SOFT'
    ERROR 1142 (42000) at line 1: SELECT command denied to user 'proxysql'@'192.168.182.120' for table 'gr_member_routing_candidate_status

从上面的错误日志上看出是权限的问题，proxysql用户没有足够的权限读取数据。

解决办法：

[root@MGR-node1 ~]# mysql -p123456
    .........
    mysql> GRANT ALL ON * . * TO  'proxysql'@'%'; 
    mysql> flush privileges;

再次看看，就有权限了

[root@ProxySQL-node ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032
    .........
    MySQL [(none)]> select * from  mysql_servers;
    +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | hostgroup_id | hostname      | port | status       | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
    +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    | 1            | 192.168.182.100| 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.182.101| 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 1            | 192.168.182.102| 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.100| 3306 | OFFLINE_SOFT | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.101| 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    | 2            | 192.168.182.102| 3306 | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
    +--------------+---------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
    6 rows in set (0.000 sec)

--------------------

***-------------------------当你发现自己突然嫉妒起其他人时，就请安静下来学习吧！------------------------***

[Link 1]: https://www.cnblogs.com/kevingrace/p/10384691.html
[mgr]: https://blog.csdn.net/ht9999i/article/details/107431199
[Link 2]: https://download.csdn.net/download/ht9999i/12649541