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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: ProxySQL+MGR组复制实现“自动故障恢复“和“读写分离“(二)

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)


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5月前
|
运维 负载均衡 监控
解析ProxySQL的故障转移机制
解析ProxySQL的故障转移机制
170 0
|
12月前
|
关系型数据库 MySQL
MySQL基于GTID的组复制(MGR)
MySQL基于GTID的组复制(MGR)
61 0
|
存储 关系型数据库 Java
【Postgres扩展】pg_auto_failover支持高可用性和自动故障转移
【Postgres扩展】pg_auto_failover支持高可用性和自动故障转移
|
SQL 关系型数据库 MySQL
只读实例(slave主从)延迟排查
本文分享的方法适用于实时查看只读延迟(主从延迟),即需要在延迟发生的时候查看才能确认问题,历史延迟不适用,以下环境已经开启并行复制。
只读实例(slave主从)延迟排查
|
缓存 监控 关系型数据库
ProxySQL+MGR组复制实现“自动故障恢复“和“读写分离“(一)
ProxySQL+MGR组复制实现“自动故障恢复“和“读写分离“(一)
350 0
|
关系型数据库 MySQL
MySQL 基于 GTID 的组复制(MGR)
MySQL 基于 GTID 的组复制(MGR)
183 0
|
关系型数据库 网络安全 数据库
PGPool-II+PG流复制实现HA主备切换
基于PG的流复制能实现热备切换,但是是要手动建立触发文件实现,对于一些HA场景来说,需要当主机down了后,备机自动切换,经查询资料知道pgpool-II可以实现这种功能。
3024 0
|
SQL 关系型数据库 MySQL
MySQL MGR集群单主模式的自动搭建和自动化故障修复
MySQL MGR集群单主模式的自动搭建和自动化故障修复/*the waiting game:尽管人生如此艰难,不要放弃;不要妥协;不要失去希望*/ 随着MySQL MGR的版本的升级以及技术成熟,在把MHA拉下神坛之后, MGR越来越成为MySQL高可用的首选方案。
1251 0
|
关系型数据库 MySQL 网络安全
MySQL8.0MGR单主/多主安装与切换
MySQL8.0 MGR 单主 多主
2831 0