1 异步复制源架构

1.1基于主从复制的异步复制源

从MySQL 8.0.22开始支持异步连接故障转移机制，我们可以在一套主从复制架构的基础上，创建一个异步复制连接的Replica副本，当主从复制Source发生意外宕机，业务提升Replica为新的Source对外进行服务，异步连接Replica可自动检测主从架构源端连接异常，并重新指向新的Source进行数据复制。

架构图与流程展示：

• Source 、Replica为正常的主从复制架构，Async Replica为通过异步连接的复制副本
• 当主从复制架构中Source意外宕机，业务提升主从架构下Replica为New Source对外提供业务服务
• Async Replica检测连接原Source失败，将主从复制重新指向新的New Source进行数据复制

1.2 基于组复制的异步复制源

从MySQL 8.0.23开始支持对组复制的异步连接故障转移机制，异步连接副本的复制源添加组复制成员并定义为组管理后，异步连接副本可自动检测源MGR架构下的组复制角色（Prinary or Secondary），当组复制成员进行更新时，异步复制副本可自动更新复制源列表中的组成员信息。当异步连接复制节点复制源不可用时，自动切换选择组复制内可用节点进行复制。

架构图与流程展示：

• MGR单组架构下，新建一个Async Replica从组复制中Primary节点进行数据复制
• 当MGR架构中Primary节点宕机，MGR自身failover机制会选举某一Secondary节点为New Pirmary节点
• 异步连接Async Replica节点连接 Down Primary失败，自动检测组复制角色以及权重，选择可用节点为新的Source节点进行数据复制

2 异步连接failover前提与基本命令

2.1 前提

• 复制源与副本都需要开启GTID，方面搭建复制时使用auto_position模式（创建复制通道时使用 SOURCE_AUTO_POSITION | MASTER_AUTO_POSITION 参数）
• 异步连接复制对应的复制源中，所有节点都需要保持复制用户账号密码一致，方便进行复制源切换
• 复制用户额外授予以下权限

GRANT SELECT ON performance_schema.* TO 'repl_user';

2.2 基本命令

1、创建复制通道

mysql> CHANGE REPLICATION SOURCE TO MASTER_USER='rpl'         # 复制源用户
, MASTER_PASSWORD='123'                                       # 复制源密码
, MASTER_HOST='172.16.104.12', MASTER_PORT=3307               # 复制源IP、端口
, SOURCE_CONNECTION_AUTO_FAILOVER=1                           # 复制源自动failover
, SOURCE_AUTO_POSITION = 1                                    # 复制gtid auto_position
, SOURCE_CONNECT_RETRY = 1                                    # 重连次数
, SOURCE_RETRY_COUNT = 6                                      # 重试次数
FOR CHANNEL 'ch1';

2、添加/删除复制源为主从架构

-- (复制通道名称，复制源IP，复制源端口，复制源网络命名空间，复制源权重)
select asynchronous_connection_failover_add_source(channel, host, port, network_namespace, weight)
-- (复制通道名称，复制源IP，复制源端口,复制源网络命名空间)
select asynchronous_connection_failover_delete_source(channel, host, port, network_namespace)

3、添加/删除复制源为组复制架构

-- (复制通道名称，管理方式:GroupReplication，组复制名称，复制源IP，复制源端口，复制源网络命名空间，Primary节点权重，Secondary节点权重)
select asynchronous_connection_failover_add_managed(channel, managed_type, managed_name, host, port, network_namespace, primary_weight, secondary_weight)

-- (复制通道名称，组复制名称)
select asynchronous_connection_failover_delete_managed(channel,  managed_name)

4、异步复制监控

• performance_schema.replication_asynchronous_connection_failover ： 查看复制渠道中可切换的复制源节点信息
• performance_schema.replication_asynchronous_connection_failover_managed : 复制源添加为组复制时可查看该视图

3 搭建测试

本次测试环境是针对MGR架构进行异步连接复制，异步链接复制源的方式选择的不是组复制管理模式。MGR集群本身有一些存量数据。

3.1 异步连接复制搭建

1、在Async Replica节点安装克隆插件，用来进行历史数据的克隆

root@mysql80 15:23:  [(none)]> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.11 sec)

root@mysql80 15:24:  [(none)]> SET GLOBAL clone_valid_donor_list = '172.16.104.12:3307';
Query OK, 0 rows affected (0.01 sec)

root@mysql80 15:24:  [(none)]> CLONE INSTANCE FROM 'rpl'@'172.16.104.12':3307 IDENTIFIED BY '123';
Query OK, 0 rows affected (5.30 sec)

root@mysql80 15:25:  [(none)]> show databRestarting mysqld...
2022-03-03T07:25:23.145060Z mysqld_safe Number of processes running now: 0

2、为Async Replica节点的复制源channel添加节点信息，将MGR组复制三个节点进行添加

root@mysql80 15:26:  [(none)]> SELECT asynchronous_connection_failover_add_source('ch1', '172.16.104.12', 3307, '', 80);
root@mysql80 15:28:  [(none)]> SELECT asynchronous_connection_failover_add_source('ch1', '172.16.104.11', 3307, '', 90);
root@mysql80 15:28:  [(none)]> SELECT asynchronous_connection_failover_add_source('ch1', '172.16.104.13', 3307, '', 100);


root@mysql80 15:28:  [(none)]> SELECT * FROM performance_schema.replication_asynchronous_connection_failover;
+--------------+---------------+------+-------------------+--------+--------------+
| CHANNEL_NAME | HOST          | PORT | NETWORK_NAMESPACE | WEIGHT | MANAGED_NAME |
+--------------+---------------+------+-------------------+--------+--------------+
| ch1          | 172.16.104.11 | 3307 |                   |     90 |              |
| ch1          | 172.16.104.12 | 3307 |                   |     80 |              |
| ch1          | 172.16.104.13 | 3307 |                   |    100 |              |
+--------------+---------------+------+-------------------+--------+--------------+
3 rows in set (0.00 sec)

3、指定复制源创建复制通道

root@mysql80 15:33:  [(none)]> CHANGE REPLICATION SOURCE TO MASTER_USER='rpl', MASTER_PASSWORD='123', MASTER_HOST='172.16.104.12', MASTER_PORT=3307, SOURCE_CONNECTION_AUTO_FAILOVER=1, 
    ->   SOURCE_AUTO_POSITION = 1,
    ->   SOURCE_CONNECT_RETRY = 1,
    ->   SOURCE_RETRY_COUNT = 6 FOR CHANNEL 'ch1';

root@mysql80 15:33:  [(none)]> start slave;


root@mysql80 15:33:  [(none)]> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.16.104.12
                  Master_User: rpl
                  Master_Port: 3307
                Connect_Retry: 1
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 1728
               Relay_Log_File: relaylog-ch1.000002
                Relay_Log_Pos: 418
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

3.2 failover测试

1、将MGR集群Primary节点进行关闭，模拟节点宕机

root@mysql80 15:35:  [(none)]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 3185a750-63d2-11ec-8209-fa8f73556a00 | 172-16-104-12 |        3307 | ONLINE       | PRIMARY     | 8.0.27         | XCom                       |
| group_replication_applier | 47e76c4e-63d2-11ec-be7a-fa175fe3f900 | 172-16-104-13 |        3307 | ONLINE       | SECONDARY   | 8.0.27         | XCom                       |
| group_replication_applier | f28eb9ac-9abd-11ec-8291-fa5f62467200 | 172-16-104-11 |        3307 | ONLINE       | SECONDARY   | 8.0.27         | XCom                       |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)

[root@172-16-104-12 local]# /usr/local/mysql80/bin/mysqladmin -p123 shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
2022-03-03T07:36:00.084244Z mysqld_safe mysqld from pid file /data2/mysql80/run/mysql80.pid ended
  [1]+  完成                  /usr/local/mysql80/bin/mysqld_safe --defaults-file=/etc/my.cnf
  
root@mysql80 15:35:  [(none)]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 47e76c4e-63d2-11ec-be7a-fa175fe3f900 | 172-16-104-13 |        3307 | ONLINE       | PRIMARY     | 8.0.27         | XCom                       |
| group_replication_applier | f28eb9ac-9abd-11ec-8291-fa5f62467200 | 172-16-104-11 |        3307 | ONLINE       | SECONDARY   | 8.0.27         | XCom                       |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.00 sec)

2、观察Async Replica节点复制源

root@mysql80 15:36:  [(none)]> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.16.104.11
                  Master_User: rpl
                  Master_Port: 3307
                Connect_Retry: 1
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 2868
               Relay_Log_File: relaylog-ch1.000003
                Relay_Log_Pos: 1586
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

root@mysql80 15:36:  [(none)]> select CHANNEL_NAME, SOURCE_UUID, SERVICE_STATE from performance_schema.replication_connection_status;
+----------------------------+--------------------------------------+---------------+
| CHANNEL_NAME               | SOURCE_UUID                          | SERVICE_STATE |
+----------------------------+--------------------------------------+---------------+
| group_replication_applier  |                                      | OFF           |
| group_replication_recovery |                                      | OFF           |
| ch1                        | f28eb9ac-9abd-11ec-8291-fa5f62467200 | ON            |
+----------------------------+--------------------------------------+---------------+
3 rows in set (0.00 sec)

最后

以上就是粗犷羽毛为你收集整理的MySQL 异步复制源自动故障转移1 异步复制源架构2 异步连接failover前提与基本命令3 搭建测试的全部内容，希望文章能够帮你解决MySQL 异步复制源自动故障转移1 异步复制源架构2 异步连接failover前提与基本命令3 搭建测试所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。