本文主要是介绍数据库系列之MySQL高可用MGR故障自动切换，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
本文简要测试了MGR+ProxySQL架构下的MySQL高可用架构，实现MGR架构下主节点故障时候的无感知切换。
在前文《数据库系列之MySQL组复制MGR》中介绍了MGR组复制的原理和架构，MGR组复制能够解决MySQL集群内部的故障自动转移。但是在单主模式下，业务连接的主节点发生切换的时候，应用程序并不能自动连接到新的主节点上，需要手动进行主节点切换。为了解决业务层面的故障自动转移，引入了ProxySQL组件，实现MGR架构下主节点故障时候的无感知切换。
如图所示，当主节点宕机后，MGR会重新选择主节点，ProxySQL能够自动探测到新的主节点，并将写数据操作路由到新的主节点，满足业务的正常写入。
1、环境准备
1）mysql基础环境
查看MGR状态为online
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
| group_replication_applier | be42da32-eb35-11ea-9505-000c2937f917 | tango-centos02 | 3306 | ONLINE |
| group_replication_applier | de623c4b-eb35-11ea-b1e4-000c2959d3e3 | tango-centos03 | 3306 | ONLINE |
| group_replication_applier | e5fb2194-eb27-11ea-b838-000c2992e812 | tango-centos01 | 3306 | ONLINE |
+---------------------------+--------------------------------------+----------------+-------------+--------------+
3 rows in set (0.00 sec)
2）ProxySQL安装
参考“MySQL主从复制读写分离实现”中基于ProxySQL部分内容。启动ProxySQL：
[root@tango-01 ~]# systemctl start proxysql
[root@tango-01 ~]# netstat -nltp|grep proxysql
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 1968/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 1968/proxysql
ProxySQL是3层配置系统，包括Runtime、Memory、Disk&Configuration File：
- Runtime层表示ProxySQL工作线程使用的内存数据结构；
- Memory(也被称为main)层经由一个MySQL兼容接口露出的内存数据库，用户可以使用MySQL客户端连接到管理界面，查看、编辑ProxySQL配置表；
- Disk & Configuration File。Disk层是一个存放在磁盘上的SQLite3数据库，Disk层可将内存中的配置信息保存到磁盘，以便ProxySQL重新启动后配置还可用。
简单理解就是Runtime是ProxySQL正在使用的配置信息、Memory层是用户可以编辑的信息、Disk层是把配置信息永久保存在磁盘上。
##将配置项从内存数据库加载到运行时数据结构
LOAD <item> FROM MEMORY/LOAD <item> TO RUNTIME##将配置项从运行时保存到内存数据库中
SAVE <item> TO MEMORY/SAVE <item> FROM RUNTIME##将持久性配置项目从磁盘数据库加载到内存数据库
LOAD <item> TO MEMORY/LOAD <item> FROM DISK##将配置项从内存数据库保存到磁盘数据库
SAVE <item> FROM MEMORY/SAVE <item> TO DISK##将配置项从配置文件加载到内存数据库中
LOAD <item> FROM CONFIG
2、环境配置
2.1 配置Proxysql
2.1.1 检查配置信息
登录proxysql管理端，默认端口6032，查看相关配置表是否存在信息，如果已经配置过了，可以先删除信息。
[root@tango-01 mysql]# ./bin/mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql> select * from mysql_servers;
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| 1 | 192.168.112.101 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Write Group |
| 2 | 192.168.112.102 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Read Group |
| 2 | 192.168.112.103 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Read Group |
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
3 rows in set (0.17 sec)mysql> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| proxysql | password | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
1 row in set (0.13 sec)mysql> select * from mysql_query_rules;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| 1 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT.*FOR UPDATE$ | NULL | 0 | CASELESS | NULL | NULL | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT | NULL | 0 | CASELESS | NULL | NULL | 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
2 rows in set (0.12 sec)mysql> select * from mysql_group_replication_hostgroups;
Empty set (0.01 sec)mysql> delete from mysql_servers;
Query OK, 3 rows affected (0.12 sec)mysql> delete from mysql_users;
Query OK, 1 row affected (0.01 sec)mysql> delete from mysql_query_rules;
Query OK, 2 rows affected (0.00 sec)mysql> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.14 sec)mysql> SAVE MYSQL VARIABLES TO DISK;
Query OK, 137 rows affected (0.09 sec)mysql> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.55 sec)mysql> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.20 sec)mysql> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.10 sec)mysql> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.05 sec)mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)mysql> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.17 sec)
2.1.2 查看组配置表
ProxySQL配置MGR在表mysql_group_replication_hostgroups中
mysql> insert into mysql_group_replication_hostgroups values(1,2,3,4,1,1,0,100,NULL);
Query OK, 1 row affected (0.11 sec)mysql> select * from mysql_group_replication_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 1 | 2 | 3 | 4 | 1 | 1 | 0 | 100 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)
- write_hostgroup：默认情况下会将所有流量发送到这个组。具有read_only=0的节点也将分配到这个组；
- backup_writer_hostgroup：如果集群有多个写节点(read_only=0)且超过了max_writers规定数量，则会把多出来的写节点放到备用写组里面；
- reader_hostgroup：读取的流量应该发送到该组，只读节点(read_only=1)会被分配到该组；
- offline_hostgroup：当ProxySQL监视到某个节点不正常时，会被放入该组；
- active：是否启用主机组，当启用时，ProxySQL将监视主机在各族之间移动；
- max_writers：最大写节点的数量，超过该值的节点应该被放入backup_write_hostgroup；
- writer_is_also_reader：一个节点既做写节点也做读节点，如果该值为2，则backup_writer_hostgroup的节点做读写点，但是writer_hostgroup不会做读节点；
2.1.3 创建MGR节点状态的函数和视图
在tango-centos01节点上，创建系统视图sys.gr_member_routing_candidate_status，该视图将为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
BEGINDECLARE 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 DOSET 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 THENSET 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;ELSESET 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
BEGINRETURN (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
BEGINRETURN (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 ;
导入addition_to_sys.sql文件数据
[root@tango-centos01 mysql]# ./bin/mysql -uroot -ppassword < /root/addition_to_sys.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
在三个mysql节点上可以查看该视图：
[root@tango-centos01 mysql]# ./bin/mysql -uroot –ppassword
............
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.11 sec)
然后授权给监控用户，这一步需要放到2.3.1后执行。
2.1.4 添加MySQL服务器
mysql_server表是用来存储ProxySQL路由转换的MySQL节点的信息。
mysql> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.112.101',3306);
Query OK, 1 row affected (0.71 sec)mysql> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.112.102',3306);
Query OK, 1 row affected (0.00 sec)mysql> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.112.103',3306);
Query OK, 1 row affected (0.00 sec)mysql> select * from mysql_servers;
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 192.168.112.101 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.112.102 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.112.103 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.02 sec)
然后执行以下命令生效：
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
2.2 配置监控
配置监控信息，用来监控ProxySQL与后端的MySQL通信是否正常
2.2.1 监控用户配置
在ProxySQL的变量表里面设定监控用户密码，用于ProxySQL监控后端MySQL服务器的用户信息
mysql> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.20 sec)mysql> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)mysql> select variable_name,variable_value from global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');
+------------------------+----------------+
| variable_name | variable_value |
+------------------------+----------------+
| mysql-monitor_password | monitor |
| mysql-monitor_username | monitor |
+------------------------+----------------+
2 rows in set (0.01 sec)
后台MySQL数据库也需要创建该用户并授权，monitor用户需要有usage权限去连接、ping和检查read_only信息，如果要检测复制延迟，还需要具有replication client权限。在MySQL服务器上创建监控用户：
mysql> create user monitor@'%' identified by 'monitor';
Query OK, 0 rows affected (0.30 sec)mysql> grant usage,replication client on *.* to monitor@'%';
Query OK, 0 rows affected (0.10 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)
注意：因为ProxySQL+组复制添加了新的视图，因此还需授权：
mysql> grant select on sys.* to monitor;
Query OK, 0 rows affected (0.01 sec)
2.2.2 配置监控时间间隔
把连接、ping、read_only监控间隔改为5s，默认分别为60s、10s和1.5s
mysql> select * from global_variables where variable_name like 'mysql-monitor%';
+--------------------------------------------------------------+----------------+
| variable_name | variable_value |
+--------------------------------------------------------------+----------------+
| mysql-monitor_enabled | true |
| mysql-monitor_connect_interval | 60000 |
| mysql-monitor_ping_interval | 10000 |
| mysql-monitor_read_only_interval | 1500 |
+--------------------------------------------------------------+----------------+
31 rows in set (0.05 sec)mysql> update global_variables set variable_value='5000' where variable_name in('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
Query OK, 3 rows affected (0.00 sec)mysql> select * from global_variables where variable_name like 'mysql-monitor%'; +--------------------------------------------------------------+----------------+
| variable_name | variable_value |
+--------------------------------------------------------------+----------------+
| mysql-monitor_connect_interval | 5000 |
| mysql-monitor_ping_interval | 5000 |
| mysql-monitor_read_only_interval | 5000 |
在修改完变量之后，一定要加载到内存中生效以及永久保存到磁盘中：
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
2.2.3 检查监控信息
监控配置完成后，我们需要检查ProxySQL与后端MySQL通信是否有异常，monitor数据库中的表用于存储监视信息。
mysql> show tables from monitor;
+--------------------------------------+
| tables |
+--------------------------------------+
| mysql_server_aws_aurora_check_status |
| mysql_server_aws_aurora_failovers |
| mysql_server_aws_aurora_log |
| mysql_server_connect_log |
| mysql_server_galera_log |
| mysql_server_group_replication_log |
| mysql_server_ping_log |
| mysql_server_read_only_log |
| mysql_server_replication_lag_log |
+--------------------------------------+
9 rows in set (0.06 sec)
1）查看ProxySQL与后台服务器连接是否正常：
mysql> 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 |
+-----------------+------+------------------+-------------------------+---------------+
| 192.168.112.102 | 3306 | 1626010060482799 | 7885 | NULL |
| 192.168.112.101 | 3306 | 1626010060435070 | 7818 | NULL |
| 192.168.112.103 | 3306 | 1626010060392373 | 8380 | NULL |
| 192.168.112.101 | 3306 | 1626010055527941 | 7887 | NULL |
| 192.168.112.103 | 3306 | 1626010055461763 | 13077 | NULL |
| 192.168.112.102 | 3306 | 1626010055392170 | 21548 | NULL |
| 192.168.112.103 | 3306 | 1626010050459140 | 8479 | NULL |
| 192.168.112.101 | 3306 | 1626010050424961 | 7825 | NULL |
| 192.168.112.102 | 3306 | 1626010050390742 | 7847 | NULL |
| 192.168.112.103 | 3306 | 1626010045524843 | 7054 | NULL |
+-----------------+------+------------------+-------------------------+---------------+
10 rows in set (0.00 sec)
2）查看组复制是否正常，检查节点是否只读和交易滞后时间：
mysql> select * from mysql_server_group_replication_log order by time_start_us desc limit 10;
+-----------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
| hostname | port | time_start_us | success_time_us | viable_candidate | read_only | transactions_behind | error |
+-----------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
| 192.168.112.103 | 3306 | 1626011487030240 | 5312 | YES | NO | 0 | NULL |
| 192.168.112.102 | 3306 | 1626011487025966 | 4132 | YES | NO | 0 | NULL |
| 192.168.112.101 | 3306 | 1626011487021645 | 4430 | YES | NO | 0 | NULL |
| 192.168.112.103 | 3306 | 1626011482040056 | 11171 | YES | NO | 0 | NULL |
| 192.168.112.102 | 3306 | 1626011482029605 | 10298 | YES | NO | 0 | NULL |
| 192.168.112.101 | 3306 | 1626011482019074 | 10640 | YES | NO | 0 | NULL |
| 192.168.112.103 | 3306 | 1626011477022158 | 5085 | YES | NO | 0 | NULL |
| 192.168.112.102 | 3306 | 1626011477018017 | 4609 | YES | NO | 0 | NULL |
| 192.168.112.101 | 3306 | 1626011477012678 | 5124 | YES | NO | 0 | NULL |
| 192.168.112.103 | 3306 | 1626011472020643 | 5232 | YES | NO | 0 | NULL |
+-----------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
10 rows in set (0.03 sec)
3）查看ProxySQL ping后端MySQL服务器是否正常：
mysql> select * from mysql_server_ping_log order by time_start_us desc limit 10;
+-----------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+-----------------+------+------------------+----------------------+------------+
| 192.168.112.102 | 3306 | 1626010155400644 | 4326 | NULL |
| 192.168.112.103 | 3306 | 1626010155352647 | 4347 | NULL |
| 192.168.112.101 | 3306 | 1626010155304706 | 2967 | NULL |
| 192.168.112.103 | 3306 | 1626010150390731 | 7044 | NULL |
| 192.168.112.101 | 3306 | 1626010150347020 | 2168 | NULL |
| 192.168.112.102 | 3306 | 1626010150303801 | 707 | NULL |
| 192.168.112.102 | 3306 | 1626010145401849 | 3872 | NULL |
| 192.168.112.103 | 3306 | 1626010145350884 | 3883 | NULL |
| 192.168.112.101 | 3306 | 1626010145299002 | 3749 | NULL |
| 192.168.112.102 | 3306 | 1626010140401854 | 3841 | NULL |
+-----------------+------+------------------+----------------------+------------+
10 rows in set (0.00 sec)
通过监控信息，我们可以得出结论，所有配置都是健康的，继续下一步
2.3 用户配置
使用了ProxySQL，则要先使用账号密码访问到ProxySQL的数据库，然后再由ProxySQL进行用户请求的转发，当中间件用户与数据库用户以及密码一致时，才能正常访问数据库。
1）在MySQL数据库上创建用户：proxyuser
mysql> create user `proxyuser`@`%` identified by '123456';
Query OK, 0 rows affected (0.05 sec)mysql> grant all privileges on *.* to `proxyuser`@`%`;
Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
2）在ProxySQL上创建用户：proxyuser
mysql> insert into mysql_users(username,password,default_hostgroup) values('proxyuser','123456',1);
Query OK, 1 row affected (0.03 sec)mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)mysql> save mysql users to disk;
Query OK, 0 rows affected (0.05 sec)
3、故障切换测试
3.1 主节点故障测试
1）查看当前写节点
##查看mysql_server在memory层的配置信息
mysql> select * from mysql_servers;
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 192.168.112.101 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.112.102 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.112.103 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
加载到RUNTIME后，由于组定义中最多只有1个写节点，其余的主节点移动到备用写组里面，可以看到192.168.112.103为写节点、其余为读节点。
mysql> select * from runtime_mysql_servers;
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 192.168.112.103 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 192.168.112.102 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 192.168.112.101 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
使用ProxySQL来访问MyQSL集群，发现可以支持读写
##使用ProxySQL 6033端口访问MySQL数据库
[root@tango-01 mysql]# ./bin/mysql -uproxyuser -p123456 -P6033 -h192.168.112.10
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tango |
+--------------------+
9 rows in set (0.29 sec)mysql> use tango;
mysql> show tables;
+-----------------+
| Tables_in_tango |
+-----------------+
| tb01 |
+-----------------+
1 row in set (0.16 sec)##通过主机名，额可以看到，我们访问到的是写节点
mysql> select @@hostname;
+----------------+
| @@hostname |
+----------------+
| tango-centos03 |
+----------------+
1 row in set (0.01 sec)##插入、查询数据
mysql> insert into tango.tb01 values(10,'aa');
Query OK, 1 row affected (0.06 sec)mysql> select * from tango.tb01;
+----+------+
| id | name |
+----+------+
| 10 | aa |
+----+------+
6 rows in set (0.00 sec)
2）关闭写节点
##直接关闭mysql服务
[root@tango-centos03 mysql]# service mysql statusSUCCESS! MySQL running (2164)
[root@tango-centos03 mysql]# service mysql stop
Shutting down MySQL.............. SUCCESS!
[root@tango-centos03 mysql]# service mysql statusERROR! MySQL is not running##需要注意的是，以前连接在主节点上的会话会断开，不会转移到新的主节点
mysql> select * from tango.tb01;
ERROR 2013 (HY000): Lost connection to MySQL server during query
3）查看是否会有备用写节点转为写节点，可以看到192.168.112.102服务器已经转为写节点，而已经关闭的192.168.112.103服务器状态为SHUNNE，已经进入离线组offline_hostgroup。
mysql> select * from runtime_mysql_servers;
+--------------+-----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 192.168.112.102 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 4 | 192.168.112.103 | 3306 | 0 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 192.168.112.101 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+-----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
4）再次使用ProxySQL来访问MyQSL集群，发现可以支持读写，业务不会因主节点的改变而受影响。
##使用ProxySQL 6033端口访问MySQL数据库
[root@tango-01 mysql]# ./bin/mysql -uproxyuser -p123456 -P6033 -h192.168.112.10
mysql> use tango;##通过主机名，额可以看到，我们访问到的是新的写节点
mysql> select @@hostname;
+----------------+
| @@hostname |
+----------------+
| tango-centos02 |
+----------------+
1 row in set (0.00 sec)##插入、查询数据
mysql> insert into tango.tb01 values(12,'bb');
Query OK, 1 row affected (0.12 sec)mysql> select * from tango.tb01;
+----+------+
| id | name |
+----+------+
| 10 | aa |
| 12 | bb |
+----+------+
7 rows in set (0.01 sec)
3.2 故障影响测试
使用sysbench进行测试，确认主节点故障过程中对业务的影响。
1）安装sysbench
[root@tango-01 ~]# yum install -y sysbench
[root@tango-01 ~]# sysbench --version
sysbench 1.0.17
2）初始化表数据
[root@tango-01 ~]# cd /usr/share/sysbench/
[root@tango-01 sysbench]# sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.112.102 --mysql-port=3306 --mysql-user=root --mysql-password=password --oltp-test-mode=complex --oltp-tables-count=5 --oltp-table-size=50000 prepare
3）执行测试
##通过proxy连接到数据库
[root@tango-01 sysbench]# sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.112.10 --mysql-port=6033 --mysql-user=proxyuser --mysql-password=123456 --oltp-test-mode=complex --oltp-tables-count=5 --oltp-table-size=50000 --threads=100 --time=600 --report-interval=1 run
4）测试结果
TPS和QPS排除测试环境资源抖动因素，在正常范围内波动，没有出现TPS或者QPS降为0的情况出现。
总结：通过上面的测试，可以看到MGR结合ProxySQL已经可以实现业务的自动故障转移，当主节点出现故障时，业务可以自动切换到其它节点对外服务，这个切换过程对业务基本是无感知的。
参考资料：
- https://blog.csdn.net/weixin_30895723/article/details/107739977
- https://www.cnblogs.com/kevingrace/p/10384691.html
- https://blog.csdn.net/solihawk/article/details/117697624
- 数据库系列之MySQL组复制MGR
转载请注明原文地址：https://blog.csdn.net/solihawk/article/details/119560410
文章会同步在公众号“牧羊人的方向”更新，感兴趣的可以关注公众号，谢谢！
这篇关于数据库系列之MySQL高可用MGR故障自动切换的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
