CNDBA社区
在之前的博客中，我们陆续介绍了MySQL 的几种高可用架构，如下：
MySQL 高可用架构：双主 + keepalived 搭建手册
https://www.cndba.cn/dave/article/108023MySQL 高可用架构：双主+ haproxy + keepalived
https://www.cndba.cn/dave/article/108024MySQL 5.7 主主 双向复制 环境搭建手册
https://www.cndba.cn/dave/article/108022MySQL 高可用架构：双主 + keepalived 搭建手册
https://www.cndba.cn/dave/article/108023MySQL 高可用架构： MGR 搭建手册
https://www.cndba.cn/dave/article/108030
本篇继续MGR 架构的延续：ProxySQL + MGR。
1 为什么要采用ProxySQL + MGR 架构
其实从容灾角度来说，MGR 是满足，但是MGR 本身无法做到无感知切换。 所以我们需要通过和ProxySQL 一起使用，来满足故障的无感知切换 。
思路大致如下：三个数据库节点使用单主模式的主复制，应用连接ProxySQL读写后端数据库节点，ProxySQL根据SQL的读写性质，分发到后端对应的数据库节点，如果后端MGR主节点down了，ProxySQL可以做到自动切换，将MGR选举出来的新主作为新的可写节点整个过程应用无需任何变动，整个切换过程秒级别的间隔
2 MGR 环境搭建
这里不再过多描述，直接参考之前的博客：
MySQL 高可用架构： MGR 说明
https://www.cndba.cn/dave/article/108027MySQL 高可用架构： MGR 搭建手册
https://www.cndba.cn/dave/article/108030
当前的MGR 是单主模式，主库是mongod1：
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | a8dcf7b7-de93-11ec-a313-fa163e2997dc | mongodb2 | 3406 | ONLINE |
| group_replication_applier | d0088f31-de93-11ec-8f5b-fa163ede7970 | mongodb3 | 3406 | ONLINE |
| group_replication_applier | eadc7c4e-de90-11ec-a79a-fa163e7035c7 | mongodb1 | 3406 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.03 sec)
mysql> show status like 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | eadc7c4e-de90-11ec-a79a-fa163e7035c7 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)
mysql>
3 ProxySQL 部署
3.1 安装ProxySQL
我们这里不复用资源，直接将ProxySQL 安装到mongodb4上：
[root@mongodb3 ~]# cat /etc/hosts
127.0.0.1 localhost
172.31.185.120 mongodb1
172.31.185.165 mongodb2
172.31.185.131 mongodb3
172.30.34.0 mongodb4
[root@mongodb3 ~]#
下载地址：
https://proxysql.com/documentation/installing-proxysql/
[root@www.cndba.cn ~]# wget https://github.com/sysown/proxysql/releases/download/v2.3.2/proxysql-2.3.2-1-centos7.x86_64.rpm
注意这里使用的是yum 来安装rpm ，会自动解决依赖关系：
[root@www.cndba.cn ~]# yum install -y proxysql-2.3.2-1-centos7.x86_64.rpm
Loaded plugins: fastestmirror, langpacks
Examining proxysql-2.3.2-1-centos7.x86_64.rpm: proxysql-2.3.2-1.x86_64
Marking proxysql-2.3.2-1-centos7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package proxysql.x86_64 0:2.3.2-1 will be installed
--> Processing Dependency: perl(DBD::mysql) for package: proxysql-2.3.2-1.x86_64
Loading mirror speeds from cached hostfile
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.023-6.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
===================================================================================================================================================
Package Arch Version Repository Size
===================================================================================================================================================
Installing:
proxysql x86_64 2.3.2-1 /proxysql-2.3.2-1-centos7.x86_64 58 M
Installing for dependencies:
perl-DBD-MySQL x86_64 4.023-6.el7 c7.8 140 k
Transaction Summary
===================================================================================================================================================
Install 1 Package (+1 Dependent package)
Total size: 58 M
Total download size: 140 k
Installed size: 59 M
Downloading packages:
perl-DBD-MySQL-4.023-6.el7.x86_64.rpm | 140 kB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : perl-DBD-MySQL-4.023-6.el7.x86_64 1/2
Installing : proxysql-2.3.2-1.x86_64 2/2
warning: group proxysql does not exist - using root
warning: group proxysql does not exist - using root
Created symlink from /etc/systemd/system/multi-user.target.wants/proxysql.service to /etc/systemd/system/proxysql.service.
Verifying : perl-DBD-MySQL-4.023-6.el7.x86_64 1/2
Verifying : proxysql-2.3.2-1.x86_64 2/2
Installed:
proxysql.x86_64 0:2.3.2-1
Dependency Installed:
perl-DBD-MySQL.x86_64 0:4.023-6.el7
Complete!
[root@www.cndba.cn ~]#
启动ProxySQL:
[root@www.cndba.cn ~]# service proxysql start
Redirecting to /bin/systemctl start proxysql.service
[root@www.cndba.cn ~]# service proxysql status
Redirecting to /bin/systemctl status proxysql.service
● proxysql.service - High Performance Advanced Proxy for MySQL
Loaded: loaded (/etc/systemd/system/proxysql.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2022-05-29 08:49:28 CST; 6s ago
Process: 14482 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS)
Main PID: 14488 (proxysql)
Tasks: 24
CGroup: /system.slice/proxysql.service
├─14488 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
└─14489 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
May 29 08:49:28 dm8 systemd[1]: Starting High Performance Advanced Proxy for MySQL...
May 29 08:49:28 dm8 proxysql[14482]: 2022-05-29 08:49:28 [INFO] Using config file /etc/proxysql.cnf
May 29 08:49:28 dm8 proxysql[14482]: 2022-05-29 08:49:28 [INFO] Current RLIMIT_NOFILE: 102400
May 29 08:49:28 dm8 proxysql[14482]: 2022-05-29 08:49:28 [INFO] Using OpenSSL version: OpenSSL 1.1.1j 16 Feb 2021
May 29 08:49:28 dm8 proxysql[14482]: 2022-05-29 08:49:28 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generatin...ficates.
May 29 08:49:28 dm8 systemd[1]: Started High Performance Advanced Proxy for MySQL.
Hint: Some lines were ellipsized, use -l to show in full.
[root@www.cndba.cn ~]#
3.2 配置ProxySQL
3.2.1 创建相关函数（MGR 主节点执行）
在MGR主节点执行下面SQL语句，创建视图提供给ProxySQL判断该节点状态。
USE sys;
DELIMITER $$
CREATE FUNCTION IFZERO(a INT, b INT)
RETURNS INT
DETERMINISTIC
RETURN IF(a = 0, b, a)$$
CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT
DETERMINISTIC
RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$
CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
RETURNS TEXT(10000)
DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$
CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE result BIGINT DEFAULT 0;
DECLARE colon_pos INT;
DECLARE next_dash_pos INT;
DECLARE next_colon_pos INT;
DECLARE next_comma_pos INT;
SET gtid_set = GTID_NORMALIZE(gtid_set);
SET colon_pos = LOCATE2(':', gtid_set, 1);
WHILE colon_pos != LENGTH(gtid_set) + 1 DO
SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
SET result = result +
SUBSTR(gtid_set, next_dash_pos + 1,
LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
ELSE
SET result = result + 1;
END IF;
SET colon_pos = next_colon_pos;
END WHILE;
RETURN result;
END$$
CREATE FUNCTION gr_applier_queue_length()
RETURNS INT
DETERMINISTIC
BEGIN
RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
Received_transaction_set FROM performance_schema.replication_connection_status
WHERE Channel_name = 'group_replication_applier' ), (SELECT
@@global.GTID_EXECUTED) )));
END$$
CREATE FUNCTION gr_member_in_primary_partition()
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
performance_schema.replication_group_member_stats USING(member_id));
END$$
CREATE VIEW gr_member_routing_candidate_status AS SELECT
sys.gr_member_in_primary_partition() as viable_candidate,
IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
performance_schema.global_variables WHERE variable_name IN ('read_only',
'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$
DELIMITER ;
到各个节点检查视图是否创建成功：
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.00 sec)
mysql> SELECT * FROM sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES | YES | 0 | 0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.01 sec)
3.2.2 创建相关用户（MGR主节点）
在MGR主节点执行下面SQL语句,创建监控用户用于ProxySQL监控数据库状态：
mysql> create user 'monitor'@'%' identified by 'monitor';
Query OK, 0 rows affected (0.02 sec)
mysql> grant select on sys.* to 'monitor'@'%';
Query OK, 0 rows affected (0.01 sec)
在MGR主节点创建用户，用于ProxySQL访问
mysql> create user 'proxysql'@'%' identified by 'proxysql';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to 'proxysql'@'%';
Query OK, 0 rows affected (0.00 sec)
3.2.3 连接ProxySQL并进行相关限制
登入管理端口（默认管理用户admin，密码admin，管理端口6032，客户端口6033）
[mysql@www.cndba.cn ~]$ mysql -uadmin -padmin --prompt='proxysql> ' -P6032 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.
proxysql> 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.00 sec)
proxysql>
在mysql_servers添加后端节点：
proxysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (10,'172.31.185.120',3406,1,3000,10,'mgr_node1');
Query OK, 1 row affected (0.00 sec)
proxysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (10,'172.31.185.165',3406,1,3000,10,'mgr_node2');
Query OK, 1 row affected (0.00 sec)
proxysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (10,'172.31.185.131',3406,1,3000,10,'mgr_node3');
Query OK, 1 row affected (0.00 sec)
proxysql>
# 将mysql_servers表加载到runtime
proxysql> LOAD mysql users TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
# 将mysql_servers表保存到磁盘
proxysql> SAVE mysql servers TO DISK;
Query OK, 0 rows affected (0.04 sec)
proxysql>
设置监控用户账户密码
proxysql> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)
proxysql> set mysql-monitor_password='monitor';
Query OK, 1 row affected (0.00 sec)
proxysql> LOAD mysql variables TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
proxysql> SAVE mysql variables TO DISK;
Query OK, 147 rows affected (0.01 sec)
proxysql>
设置提供访问的用户
proxysql> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent)values('proxysql','proxysql',1,10,1);
Query OK, 1 row affected (0.00 sec)
proxysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
proxysql> save mysql users to disk;
Query OK, 0 rows affected (0.01 sec)
配置mysql_group_replication_hostgroups表：
proxysql> insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) values(10,20,30,40,1,1,0,0);
Query OK, 1 row affected (0.00 sec)
proxysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
proxysql> save mysql servers to disk;
Query OK, 0 rows affected (0.05 sec)
设置读写分离规则
proxysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',10,1);
Query OK, 1 row affected (0.00 sec)
proxysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',30,1);
Query OK, 1 row affected (0.00 sec)
proxysql> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)
proxysql> save mysql query rules to disk;
Query OK, 0 rows affected (0.02 sec)
3.3 查看相关状态
3.3.1 查看后端节点健康状态
proxysql> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10 ;
+----------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+----------------+------+------------------+-------------------------+---------------+
| 172.31.185.165 | 3406 | 1653786847191915 | 849 | NULL |
| 172.31.185.120 | 3406 | 1653786846487403 | 893 | NULL |
| 172.31.185.131 | 3406 | 1653786845782853 | 776 | NULL |
| 172.31.185.165 | 3406 | 1653786786615714 | 1033 | NULL |
| 172.31.185.120 | 3406 | 1653786786199228 | 822 | NULL |
| 172.31.185.131 | 3406 | 1653786785782791 | 1324 | NULL |
| 172.31.185.165 | 3406 | 1653786727321063 | 1084 | NULL |
| 172.31.185.131 | 3406 | 1653786726551944 | 6977 | NULL |
| 172.31.185.120 | 3406 | 1653786725782856 | 1190 | NULL |
| 172.31.185.131 | 3406 | 1653786667163748 | 1483 | NULL |
+----------------+------+------------------+-------------------------+---------------+
10 rows in set (0.00 sec)
proxysql> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
+----------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+----------------+------+------------------+----------------------+------------+
| 172.31.185.131 | 3406 | 1653786906054353 | 201 | NULL |
| 172.31.185.120 | 3406 | 1653786905948161 | 277 | NULL |
| 172.31.185.165 | 3406 | 1653786905841942 | 282 | NULL |
| 172.31.185.165 | 3406 | 1653786895998378 | 172 | NULL |
| 172.31.185.120 | 3406 | 1653786895920175 | 287 | NULL |
| 172.31.185.131 | 3406 | 1653786895841918 | 321 | NULL |
| 172.31.185.165 | 3406 | 1653786886066497 | 236 | NULL |
| 172.31.185.131 | 3406 | 1653786885954183 | 261 | NULL |
| 172.31.185.120 | 3406 | 1653786885841835 | 279 | NULL |
| 172.31.185.131 | 3406 | 1653786876019542 | 222 | NULL |
+----------------+------+------------------+----------------------+------------+
10 rows in set (0.00 sec)
3.3.2 查看MGR配置
proxysql> select * from mysql_group_replication_hostgroups/G
*************************** 1. row ***************************
writer_hostgroup: 10 # 写组
backup_writer_hostgroup: 20 # 后备写组
reader_hostgroup: 30 # 读组
offline_hostgroup: 40 # 下线组
active: 1 # 是否启用
max_writers: 1 # 最多的写节点个数
writer_is_also_reader: 0 # 决定一个节点升级为写节点(放进writer_hostgroup)后是否仍然保留在reader_hostgroup组中提供读服务。如果mgr多主模式需要设置为1
max_transactions_behind: 0 # 该字段决定最多延后写节点多少个事务
comment: NULL # 注释
1 row in set (0.00 sec)
3.3.3 查看MGR相关的监控指标
proxysql> select * from mysql_server_group_replication_log desc limit 10;
+----------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
| hostname | port | time_start_us | success_time_us | viable_candidate | read_only | transactions_behind | error |
+----------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
| 172.31.185.120 | 3406 | 1653786786000097 | 4668 | YES | NO | 0 | NULL |
| 172.31.185.120 | 3406 | 1653786791000148 | 4335 | YES | NO | 0 | NULL |
| 172.31.185.120 | 3406 | 1653786796000175 | 2984 | YES | NO | 0 | NULL |
| 172.31.185.120 | 3406 | 1653786801000345 | 2492 | YES | NO | 0 | NULL |
| 172.31.185.120 | 3406 | 1653786806000289 | 7286 | YES | NO | 0 | NULL |
| 172.31.185.120 | 3406 | 1653786811000409 | 3906 | YES | NO | 0 | NULL |
| 172.31.185.120 | 3406 | 1653786816000431 | 3699 | YES | NO | 0 | NULL |
| 172.31.185.120 | 3406 | 1653786821000562 | 7134 | YES | NO | 0 | NULL |
| 172.31.185.120 | 3406 | 1653786826000546 | 2377 | YES | NO | 0 | NULL |
| 172.31.185.120 | 3406 | 1653786831000694 | 3771 | YES | NO | 0 | NULL |
+----------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
10 rows in set (0.00 sec)
4 读写分离测试
4.1 测试读场景
注意这里连的是ProxySQL 的节点：
[mysql@www.cndba.cn ~]$ for i in `seq 1 10`; do mysql -uproxysql -pproxysql -h172.30.34.0 -P6033 -e 'select * from performance_schema.global_variables where variable_name="server_id";' ; done | grep server
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 3
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 3
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 3
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 3
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 3
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 3
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 2
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 3
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 2
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 2
[mysql@www.cndba.cn ~]$
这里的读操作默认都到了2和3上.
4.2 测试写场景
同样连接的是ProxySQL 节点：
[mysql@www.cndba.cn ~]$ for i in `seq 1 10`; do mysql -uproxysql -pproxysql -h172.30.34.0 -P6033 -e 'select * from performance_schema.global_variables where variable_name="server_id" for update;' ; done | grep server
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 1
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 1
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 1
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 1
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 1
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 1
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 1
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 1
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 1
mysql: [Warning] Using a password on the command line interface can be insecure.
server_id 1
[mysql@www.cndba.cn ~]$
For update操作都打到主节点上了。
连接ProxySQL执行写入：
[mysql@www.cndba.cn ~]$ for i in `seq 2 11`; do mysql -uproxysql -pproxysql -h172.30.34.0 -P6033 -e "insert into cndba.cndba values($i,$i+1)" ; done
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[mysql@www.cndba.cn ~]$
到从库 查询：
mysql> select * from cndba.cndba;
+----+--------------------------+
| id | url |
+----+--------------------------+
| 1 | http://www.cndba.cn/dave |
| 2 | 3 |
| 3 | 4 |
| 4 | 5 |
| 5 | 6 |
| 6 | 7 |
| 7 | 8 |
| 8 | 9 |
| 9 | 10 |
| 10 | 11 |
| 11 | 12 |
+----+--------------------------+
11 rows in set (0.00 sec)
5 MGR 故障转移测试
5.1 关闭主库
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
此时的主库变成了节点2：
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | a8dcf7b7-de93-11ec-a313-fa163e2997dc | mongodb2 | 3406 | ONLINE |
| group_replication_applier | d0088f31-de93-11ec-8f5b-fa163ede7970 | mongodb3 | 3406 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
mysql> show status like 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | a8dcf7b7-de93-11ec-a313-fa163e2997dc |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)
mysql>
测试ProxySQL中已经排除了节点1：
[mysql@www.cndba.cn ~]$ mysql -uadmin -padmin -P6032 -h127.0.0.1 -e "select hostgroup_id, hostname, port,status from runtime_mysql_servers;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+----------------+------+---------+
| hostgroup_id | hostname | port | status |
+--------------+----------------+------+---------+
| 10 | 172.31.185.165 | 3406 | ONLINE |
| 30 | 172.31.185.131 | 3406 | ONLINE |
| 20 | 172.31.185.120 | 3406 | SHUNNED |
+--------------+----------------+------+---------+
[mysql@www.cndba.cn ~]$
5.2 执行写入操作
[mysql@www.cndba.cn ~]$ for i in `seq 12 22`; do mysql -uproxysql -pproxysql -h172.30.34.0 -P6033 -e "insert into cndba.cndba values($i,$i+1)" ; done
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[mysql@www.cndba.cn ~]$
[mysql@www.cndba.cn ~]$ mysql -uproxysql -pproxysql -h172.30.34.0 -P6033 -e "select * from cndba.cndba"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------------------------+
| id | url |
+----+--------------------------+
| 1 | http://www.cndba.cn/dave |
| 2 | 3 |
| 3 | 4 |
| 4 | 5 |
| 5 | 6 |
| 6 | 7 |
| 7 | 8 |
| 8 | 9 |
| 9 | 10 |
| 10 | 11 |
| 11 | 12 |
| 12 | 13 |
| 13 | 14 |
| 14 | 15 |
| 15 | 16 |
| 16 | 17 |
| 17 | 18 |
| 18 | 19 |
| 19 | 20 |
| 20 | 21 |
| 21 | 22 |
| 22 | 23 |
+----+--------------------------+
[mysql@www.cndba.cn ~]$
此时通过ProxySQL 可以正常写入和查询。
5.3 重启节点1：
启动节点1的MySQL 和 复制组：
[mysql@mongodb1 ~]$ mysqld_safe --defaults-file=/data/mysql/3406/my3406.cnf &
[1] 30576
[mysql@mongodb1 ~]$ mysql -uroot -proot -S /data/mysql/3406/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 8
Server version: 5.7.33-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.
mysql> start group_replication;
Query OK, 0 rows affected (2.88 sec)
mysql>
查看成员状态，恢复正常：
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | a8dcf7b7-de93-11ec-a313-fa163e2997dc | mongodb2 | 3406 | ONLINE |
| group_replication_applier | d0088f31-de93-11ec-8f5b-fa163ede7970 | mongodb3 | 3406 | ONLINE |
| group_replication_applier | eadc7c4e-de90-11ec-a79a-fa163e7035c7 | mongodb1 | 3406 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> show status like 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | a8dcf7b7-de93-11ec-a313-fa163e2997dc |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)
mysql>
6 小结
通过测试，我们看到了ProxySQL的价值，即MGR底层的改变，并不影响业务，通过ProxySQL 可以做到读写分离和无感知的故障切换。
版权声明：本文为博主原创文章，未经博主允许不得转载。
