MySQL Group Replication 快速入门，环境搭建

MySQL Group Replication（MGR）使您能够创建弹性、高可用、容错的复制拓扑，可工作在单主（单台 MySQL 实例接收更新）或多主模式（所有 MySQL 实例接收更新）下。当某个组成员实例发生故障，组复制没有内置方法来执行客户端到组成员的故障转移，而是需自己配置路由器（如 MySQL-Router ）或代理（如 ProxySQL）等组件来自动执行该操作

节点规划

1、节点角色、系统版本、IP 地址说明

序号	节点名称	节点 IP	角色	MySQL Version	系统环境
01	x1	192.168.31.81	Replica	MySQL Server 8.0.30	CentOS 7.9（Kernel 3.10）
02	x2	192.168.31.82	Replica	MySQL Server 8.0.30	CentOS 7.9（Kernel 3.10）
03	x3	192.168.31.83	Master	MySQL Server 8.0.30	CentOS 7.9（Kernel 3.10）

MGR 从 MySQL 5.7.17 开始引入，但随着 5.7 版本逐渐退出历史舞台（MySQL 5.7 已于 2020 年 10 月起不再做大的功能更新），更多 MGR 相关特性仅在 MySQL 8.0 上才有。推荐 MySQL 8.0.22 及之后的版本，整体会更稳定可靠

2、用于组复制的 MySQL Server 实例必须满足以下要求:

MySQL 组复制成员节点数最大值为 9
数据必须存储在事务存储引擎 InnoDB 中
每个表都必须具有已定义的主键或非空唯一键，此类键需要作为表中每一行的唯一标识符
组实例间的网络需具有低延迟、高带宽，否则会影响组复制的性能和稳定性。从 MySQL 8.0.14 开始，您可以使用 IPv4 或 IPv6 或两者的混合来进行远程组复制服务器之间的 TCP 通信
组复制建立在基于 GTID（全局事务标识符）的复制之上，并使用 GTID 来跟踪每个服务器实例上已提交的事务。所有组成员都需要设置 gtid_mode=ON 和 enforce_gtid_consistency=ON

初始化基础环境

1、在各节点执行的系统初始化设置

# selinux 放行端口 33601
yum install -y policycoreutils-python
semanage port -a -t mysqld_port_t -p tcp 33061

# linux firewall 放行端口 3306、33061
firewall-cmd add-port=3306/tcp add-port=33061/tcp --permanent
firewall-cmd --reload

# 设置组成员间主机名与 IP 地址解析记录
cat /etc/hosts
192.168.31.81 x1
192.168.31.82 x2
192.168.31.83 x3

2、yum 安装 MySQL Server 实例，并启动、初始化 root 账号为简单密码 root

具体安装方法，请参考此处连接的文章

配置各 MySQL Instance

1、x1 节点配置

# 配置文件 /etc/my.cnf 信息
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log_timestamps = SYSTEM
default-authentication-plugin = caching_shx2_password
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

server_id = 1                       # 服务 ID，每台机器不同
gtid_mode = ON                      # 启用 GTID
enforce_gtid_consistency = ON       # 强制 GTID 一致性
log_replica_updates = ON
log_bin = binlog                    # 开启二进制日志记录
binlog_checksum = NONE              # 禁用二进制日志事件校验
binlog_format = ROW                 # 设置 binlog 格式为 ROW

# Group Replication
plugin_load_add='group_replication.so'      # 加载组复制组件
group_replication_recovery_use_ssl = ON
group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856' # 配置组名，格式为 uuid
group_replication_local_address = '192.168.31.81:33061'   # 定义组复制成员之间的通信端口
group_replication_group_seeds = '192.168.31.81:33061,192.168.31.82:33061,192.168.31.83:33061' # 组成员列表
group_replication_start_on_boot = off                     #  MySQL 实例启动时是否启动组复制
group_replication_bootstrap_group = Off           # 不启用引导组，组中只在一台机器上设置该变量为 On，其它机器都设置为 Off。建议手动引导组复制

备注：log_replica_updates = ON 组中的每个节点都需要记录它们所接收到并应用的所有事务，因为恢复过程是依赖于组 c 成员的二进制日志来进行的。因此，组中每个成员都必须保留每个事务的副本（binlog），即使某事务不是在该节点上开始的。

2、x2 节点配置

# 基于节点 x1 配置，修改如下配置项
server_id=2
loose-group_replication_local_address= "192.168.31.82:33061"

3、x3 节点配置

# 基于节点 x1 配置，修改如下配置项
server_id=3
loose-group_replication_local_address= "192.168.31.83:33061"

各节点 MySQL 实例配置修改后，需执行systemctl restart mysqld以生效

创建组复制 Recovery 凭据

在每个 MySQL 节点执行如下 SQL 命令，创建组复制账号

-- 零时关闭 binlog，创建复制账号 replica_user
SET SQL_LOG_BIN=0;

CREATE USER replica_user@'%' IDENTIFIED BY 'a9d16@a8C77';
GRANT REPLICATION SLAVE ON *.* TO replica_user@'%';
GRANT CONNECTION_ADMIN ON *.* TO replica_user@'%';
GRANT BACKUP_ADMIN ON *.* TO replica_user@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO replica_user@'%';
FLUSH PRIVILEGES;

SET SQL_LOG_BIN=1;

启动组复制

1、任意 MySQL 节点执行如下命令，该节点将引导并初始化组复制集群，并以 PRIMARY 节点运行

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

mysql> 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 | 48ae6403-671a-11ee-841a-52540001e124 | x3          |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
#> +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+

2、其它 MySQL 节点执行如下命令，这些节点将以 SECONDARY 节点运行

-- 从 MySQL 8.0.21 开始，可以在启动组复制时提供 recovery channel 用户凭据，而不是将它们存储在副本状态表中
mysql> START GROUP_REPLICATION USER='replica_user', PASSWORD='a9d16@a8C77';

验证组复制状态

1、查看组复制成员

mysql> use performance_schema;
mysql> select * from replication_group_members;
#> +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
#> | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
#> +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
#> | group_replication_applier | 48ae6403-671a-11ee-841a-52540001e124 | x3          |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
#> | group_replication_applier | 48afce05-671a-11ee-832f-525400d01a58 | x2          |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
#> | group_replication_applier | 48c41877-671a-11ee-862e-525400966d91 | x1          |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
#> +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+

# 主节点为 x3 ，副本节点 x1 、x2

2、向主节点 x3 插入数据，查看主节点、副本节点数据同步状态

mysql> select * from replication_group_member_stats\G;
#> SELECT * from replication_group_member_stats\G;
#> *************************** 1. row ***************************
#>                               CHANNEL_NAME: group_replication_applier
#>                                    VIEW_ID: 16971886969904679:3
#>                                  MEMBER_ID: 48ae6403-671a-11ee-841a-52540001e124    #  主节点 x3
#>                COUNT_TRANSACTIONS_IN_QUEUE: 0
#>                 COUNT_TRANSACTIONS_CHECKED: 100002      # 已通过冲突检查的事务数量
#>                   COUNT_CONFLICTS_DETECTED: 0           # 未通过冲突检查的事务数量
#>         COUNT_TRANSACTIONS_ROWS_VALIDATING: 28444
#>         TRANSACTIONS_COMMITTED_ALL_MEMBERS: ce9be252-2b71-11e6-b8f4-00212844f856:1-71561    # 已在复制组的所有成员上成功提交的事务，显示为 GTID 集。该值以固定时间间隔更新
#>             LAST_CONFLICT_FREE_TRANSACTION: ce9be252-2b71-11e6-b8f4-00212844f856:100005     # 最后检查的无冲突事务的事务标识符
#> COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0
#>          COUNT_TRANSACTIONS_REMOTE_APPLIED: 3
#>          COUNT_TRANSACTIONS_LOCAL_PROPOSED: 100002  # 源自该成员，并发送到该组的交易数量
#>          COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0       # 源自该成员，并被该组回滚的事务数
#> *************************** 2. row ***************************
#>                               CHANNEL_NAME: group_replication_applier
#>                                    VIEW_ID: 16971886969904679:3
#>                                  MEMBER_ID: 48afce05-671a-11ee-832f-525400d01a58    # 副本节点 x2
#>                COUNT_TRANSACTIONS_IN_QUEUE: 0
#>                 COUNT_TRANSACTIONS_CHECKED: 100002
#>                   COUNT_CONFLICTS_DETECTED: 0
#>         COUNT_TRANSACTIONS_ROWS_VALIDATING: 28444
#>         TRANSACTIONS_COMMITTED_ALL_MEMBERS: ce9be252-2b71-11e6-b8f4-00212844f856:1-71561
#>             LAST_CONFLICT_FREE_TRANSACTION: ce9be252-2b71-11e6-b8f4-00212844f856:100005
#> COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 17159   # 该成员从复制组接收到，并等待应用的交易数量
#>          COUNT_TRANSACTIONS_REMOTE_APPLIED: 82843   # 该成员从复制组收到并通过 appiler 线程应用的的交易数量
#>          COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0
#>          COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0
...
...

replication_group_member_stats 相关字段参考

主节点切换

当主节点发生故障或需要维护时，选择新的主节点以确保数据复制的连续性并最大程度地减少停机时间非常重要

自动切换

1、stop 主节点 x3 后，自动从从节点选择一个作为主节点，继续对外提供服务

mysql> stop group_replication;
mysql> select * from replication_group_members;
#> +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
#> | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
#> +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
#> | group_replication_applier | 48afce05-671a-11ee-832f-525400d01a58 | x2          |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
#> | group_replication_applier | 48c41877-671a-11ee-862e-525400966d91 | x1          |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
#> +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+

2、再次 start 主节点 x3 后，将以从节点身份加入组复制集群

mysql> START GROUP_REPLICATION USER='replica_user', PASSWORD='a9d16@a8C77';
mysql> select * from replication_group_members;
#> +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
#> | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
#> +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
#> | group_replication_applier | 48ae6403-671a-11ee-841a-52540001e124 | x3          |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
#> | group_replication_applier | 48afce05-671a-11ee-832f-525400d01a58 | x2          |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
#> | group_replication_applier | 48c41877-671a-11ee-862e-525400966d91 | x1          |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
#> +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+

手动切换

根据组复制成员 MEMBER_ID ，手动选择指定组成员为 PRIMARY 节点

# 设置节点 x1 作为组复制 PRIMARY 节点，负责读写任务
mysql> select group_replication_set_as_primary('48c41877-671a-11ee-862e-525400966d91');
#> +--------------------------------------------------------------------------+
#> | group_replication_set_as_primary('48c41877-671a-11ee-862e-525400966d91') |
#> +--------------------------------------------------------------------------+
#> | Primary server switched to: 48c41877-671a-11ee-862e-525400966d91         |
#> +--------------------------------------------------------------------------+

mysql> select * from replication_group_members;
#> +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
#> | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
#> +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
#> | group_replication_applier | 48ae6403-671a-11ee-841a-52540001e124 | x3          |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
#> | group_replication_applier | 48afce05-671a-11ee-832f-525400d01a58 | x2          |        3306 | ONLINE       | SECONDARY   | 8.0.30         | XCom                       |
#> | group_replication_applier | 48c41877-671a-11ee-862e-525400966d91 | x1          |        3306 | ONLINE       | PRIMARY     | 8.0.30         | XCom                       |
#> +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+