数据库系列之MySQL高可用MGR故障自动切换

本文简要测试了MGR+ProxySQL架构下的MySQL高可用架构，实现MGR架构下主节点故障时候的无感知切换。

solihawk

1660人浏览 · 2021-08-10 08:56:45

solihawk · 2021-08-10 08:56:45 发布

本文简要测试了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
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 ;

导入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 status
 SUCCESS! MySQL running (2164)
[root@tango-centos03 mysql]# service mysql stop
Shutting down MySQL.............. SUCCESS! 
[root@tango-centos03 mysql]# service mysql status
 ERROR! 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/solihawk/article/details/119560410
文章会同步在公众号“牧羊人的方向”更新，感兴趣的可以关注公众号，谢谢！

亚马逊云科技技术品牌专区

更多推荐

STM32节点移植lorawan协议连接腾讯云物联网开发平台（IoT Explorer）

STM32移植lorawan协议连接腾讯云物联网开发平台（IoT Explorer）前言前言在移植协议之前，先给大家科普一下Lora 和 lorawan 的区别。LoRa 是LPWAN通信技术中的一种，是美国Semtech公司采用和推广的一种基于扩频技术的超远距离无线传输方案。这一方案改变了以往关于传输距离与功耗的折衷考虑方式为用户提供一种简单的能实现远距离、长电池寿命、大容量的系统，进而扩...

亚马逊云科技技术品牌专区

物联网主机E6000引领工业自动化的新篇章

亚马逊云科技技术品牌专区

搞 IoT 物联网，你居然要懂这么多种协议...

物联网协议是指在物联网环境中用于设备间通信和数据传输的协议。根据不同的作用，物联网协议可分为传输协议、通信协议和行业协议。传输协议：一般负责子网内设备间的组网及通信。例如 Wi-Fi、Ethernet、NFC、 Zigbee、Bluetooth、GPRS、3G/4G/5G等。这些协议能够确保在网络上传输的数据的安全性和可靠性。通讯协议：主要是运行在传统互联网TCP/IP协议之上的设备通讯协议，负责