一、环境
系统：CentOS 7.9
mysql：8.0.32
服务器IP端口
|
IP |
机器名 |
端口 |
集群端口 |
备注 |
| 172.10.10.177 | dev2 | 3306 | 33061 | 引导节点、主节点 |
| 172.10.10.178 | dev3 | 3306 | 33061 | 主节点 |
| 172.10.10.179 | dev4 | 3306 | 33061 | 主节点 |
二、准备工作
1、防火墙开通端口
firewall-cmd --permanent --zone=public --add-port=3306/tcp
firewall-cmd --permanent --zone=public --add-port=33061/tcp
firewall-cmd --reload
2、设置主机名和IP的映射，设置时区
主机名，集群查找节点时需要（如果不设置主机名，可尝试配置report_host，report_port）
[root@localhost ~]# hostname
localhost
[root@localhost ~]# hostname -i
172.10.10.177
[root@localhost ~]# vi /etc/hosts
172.10.10.177 dev2
172.10.10.178 dev3
172.10.10.179 dev4
[root@localhost ~]# hostnamectl set-hostname test5
[root@localhost ~]# timedatectl
[root@localhost ~]# timedatectl set-timezone Asia/Shanghai
3、安装mysql
二、配置集群
18.2.1.2 Configuring an Instance for Group Replication
1、配置my.cnf文件（所有节点）
主要是
server_id和report_host不同
# 每个服务的ID必须唯一
server_id=177
# 开启GTID，必须开启
gtid_mode=ON
# 强制GTID的一致性
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "172.10.10.177:33061"
group_replication_group_seeds= "172.10.10.177:33061,172.10.10.178:33061,172.10.10.179:33061"
group_replication_bootstrap_group=off
身份验证插件caching_sha2_password，获取公钥
group_replication_recovery_get_public_key=ON多主模式添加
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON“从”节点报告自己的IP，不配置取主机名
report_host=172.10.10.177
report_port=3306SELECT * FROM performance_schema.replication_group_members;
中的MEMBER_HOST中显示
参数的中文意思
mysql8.0初探:(二)MySQL Group Replication-MGR集群简介
2、重启服务（所有节点）
[root@bogon mysql-8.0.32]# systemctl stop mysqld80
[root@bogon mysql-8.0.32]# systemctl start mysqld80
3、客户端连接（所有节点）
/opt/mysql-8.0.32/bin/mysql -h 127.0.0.1 -u root -p
/opt/mysql-8.0.32/bin/mysql -h 127.0.0.1 -P3306 -u root -p
4、创建复制用户（所有节点）
18.2.1.3 User Credentials For Distributed Recovery
禁用二进制日志记录以便在每个实例上分别创建复制用户，
（可尝试先在主节点创建用户，其他节点等建好集群后再创建）
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER replicator@'%' IDENTIFIED BY 'replicator8';
mysql> GRANT REPLICATION SLAVE ON *.* TO replicator@'%';
mysql> GRANT CONNECTION_ADMIN ON *.* TO replicator@'%';
mysql> GRANT BACKUP_ADMIN ON *.* TO replicator@'%';
mysql> GRANT GROUP_REPLICATION_STREAM ON *.* TO replicator@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;# 向服务器提供用于分布式恢复的用户凭据。
mysql> CHANGE MASTER TO MASTER_USER='replicator', MASTER_PASSWORD='replicator8' FOR CHANNEL 'group_replication_recovery';
--Or from MySQL 8.0.23:
mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='replicator', SOURCE_PASSWORD='replicator8' FOR CHANNEL 'group_replication_recovery';等集群建好后验证一下复制用户
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select user,authentication_string,host from user;
+------------------+------------------------------------------------------------------------+-----------+
| user | authentication_string | host |
+------------------+------------------------------------------------------------------------+-----------+
| replicator | $A$005$3/^Yf"1\e^r)HA\_hEiR7OctgyRMJFDldlOmwtsQCP5VO9dlxRw/HPTTED/ | % |
| root | $A$005$
)mI rG.T-<cRAAEkntkCN/23h826WyUuGIdsqeSb36dOmAZRQG1cL9 | % |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
+------------------+------------------------------------------------------------------------+-----------+
5 rows in set (0.00 sec)
5、安装组复制插件（所有节点）
配置文件里已经配了，无需操作
18.2.1.4 Launching Group Replication
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
ERROR 1125 (HY000): Function 'group_replication' already exists
mysql> SHOW PLUGINS;
三、单主模式
1、引导组复制（
主节点
）
18.2.1.5 Bootstrapping the Group
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
--Or if you are providing user credentials for distributed recovery on the START GROUP_REPLICATION statement (which you can from MySQL 8.0.21), issue the following statements:
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION USER='replicator', PASSWORD='replicator8';
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
查看组复制成员（查看集群成员）
mysql> SELECT * FROM performance_schema.replication_group_members;
MySQL Group Replication 节点状态详解
（创建测试数据库，非必要，可以在集群搭建好后再测）
mysql> CREATE DATABASE test;
mysql> USE test;
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
mysql> INSERT INTO t1 VALUES (1, 'Luis');
mysql> SELECT * FROM t1;（查看binlog）
mysql> SHOW BINLOG EVENTS;————————————
2、其他节点（”从”）
18.2.1.6 Adding Instances to the Group
启动组复制
mysql> START GROUP_REPLICATION;
查看组复制成员（查看集群成员）
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 | 2ca72062-b99f-11ed-a000-000c29b604d9 | localhost.localdomain | 3306 | ONLINE | PRIMARY | 8.0.32 | XCom |
| group_replication_applier | 34b37551-b99f-11ed-9b64-000c29b50c89 | localhost.localdomain | 3306 | RECOVERING | PRIMARY | 8.0.32 | XCom |
| group_replication_applier | 3c7f9fcf-b99f-11ed-9a42-000c29a3c512 | localhost.localdomain | 3306 | RECOVERING | PRIMARY | 8.0.32 | XCom |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)MEMBER_STATE一直是RECOVERING的需要查看日志处理。
四、多主模式
# 停止组复制(所有节点执行)：
mysql> stop group_replication;
mysql> set global group_replication_single_primary_mode=OFF;
mysql> set global group_replication_enforce_update_everywhere_checks=ON;
# 随便选择某个节点执行
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
# 其他节点执行
mysql> START GROUP_REPLICATION;
# 查看组信息，所有节点的 MEMBER_ROLE 都为 PRIMARY
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 | 2ca72062-b99f-11ed-a000-000c29b604d9 | dev2 | 3306 | ONLINE | PRIMARY | 8.0.32 | XCom |
| group_replication_applier | 34b37551-b99f-11ed-9b64-000c29b50c89 | dev3 | 3306 | ONLINE | PRIMARY | 8.0.32 | XCom |
| group_replication_applier | 3c7f9fcf-b99f-11ed-9a42-000c29a3c512 | dev4 | 3306 | ONLINE | PRIMARY | 8.0.32 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)
五、切回单主模式
# 所有节点执行
mysql> stop group_replication;
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
mysql> set global group_replication_single_primary_mode=ON;
# “主”节点执行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
# 从节点执行
START GROUP_REPLICATION;
# 查看MGR组信息
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 | 2ca72062-b99f-11ed-a000-000c29b604d9 | localhost.localdomain | 3306 | ONLINE | PRIMARY | 8.0.32 | XCom |
| group_replication_applier | 34b37551-b99f-11ed-9b64-000c29b50c89 | localhost.localdomain | 3306 | ONLINE | SECONDARY | 8.0.32 | XCom |
| group_replication_applier | 3c7f9fcf-b99f-11ed-9a42-000c29a3c512 | localhost.localdomain | 3306 | ONLINE | SECONDARY | 8.0.32 | XCom |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)
六、启动时节点自动加入集群（自启动）
数据库启动时启动组复制
my.cnf
group_replication_start_on_boot=on引导节点（“主”节点）
group_replication_bootstrap_group=on
七、参考
mysql8.0初探:(二)MySQL Group Replication-MGR集群简介
MySQL8.0 集群高可用架构选型
（视频）
MySQL 8.0 InnoDB Cluster集群部署步骤
MySQL Group Replication 节点状态详解
八、报错处理
查看日志：tail -f /opt/mysql-8.0.32/data/mysqld.log
客户端连接：/opt/mysql-8.0.32/bin/mysql -h 127.0.0.1 -u root -p
1、启动组复制报错，START GROUP_REPLICATION报错1
mysql> START GROUP_REPLICATION;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
mysql> show master status\G
*************************** 1. row ***************************
File: binlog.000002
Position: 1463
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 34b37551-b99f-11ed-9b64-000c29b50c89:1-6
1 row in set (0.00 sec)
查看日志，tail -f /opt/mysql-8.0.32/data/mysqld.log，做对应处理
2、启动组复制报错，START GROUP_REPLICATION报错2
[
MY-011526
] [Repl] Plugin group_replication reported: ‘
This member has more executed transactions than those present in the group. Local transactions
: 34b37551-b99f-11ed-9b64-000c29b50c89:1-6 > Group transactions: 2ca72062-b99f-11ed-a000-000c29b604d9:1-6, aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-5′
[
MY-011522
] [Repl]
Plugin group_replication reported: ‘The member contains transactions not present in the group
. The member will now exit the group.’
RESET MASTER；
mysql> RESET MASTER;
Query OK, 0 rows affected (0.04 sec)
mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='replicator', SOURCE_PASSWORD='replicator8' FOR CHANNEL 'group_replication_recovery';
mysql> START GROUP_REPLICATION;
3、启动组复制报错，START GROUP_REPLICATION报错3
[ERROR] [
MY-010584
] [Repl] Slave I/O for channel ‘group_replication_recovery’: error connecting to master ‘replicator@localhost.localdomain:3306’ – retry-time: 60 retries: 1 message: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection. Error_code: MY-002061
[ERROR] [
MY-011582
] [Repl] Plugin group_replication reported: ‘There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.’
[ERROR] [
MY-011583
] [Repl] Plugin group_replication reported: ‘For details please check performance_schema.replication_connection_status table and error log messages of Slave I/O for channel group_replication_recovery.’
在配置文件设置group_replication_recovery_get_public_key=ON;
或用命令设置
mysql> SET GLOBAL group_replication_recovery_get_public_key=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'group_replication_recovery_get_public_key';
4、
启动组复制报错，START GROUP_REPLICATION报错4
[ERROR] [MY-011735] [Repl] Plugin group_replication reported: ‘[GCS] Error on opening a connection to peer node 172.10.10.179:33061 when joining a group. My local port is: 33061.’
[ERROR] [MY-011735] [Repl] Plugin group_replication reported: ‘[GCS] Error connecting to all peers. Member join failed. Local port: 33061’
[ERROR] [MY-011735] [Repl] Plugin group_replication reported: ‘[GCS] The member was unable to join the group. Local port: 33061’
[ERROR] [MY-011640] [Repl] Plugin group_replication reported: ‘Timeout on wait for view after joining group’
[ERROR] [MY-011735] [Repl] Plugin group_replication reported: ‘[GCS] The member is leaving a group without being on one.’
启动第一个节点时，先开启引导
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (1.27 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
5、查看组复制成员状态，MEMBER_STATE一直是RECOVERING或ERROR
SELECT * FROM performance_schema.replication_group_members;
查看日志，tail -f /opt/mysql-8.0.32/data/mysqld.log
6、从节点有复制用户，复制时创建复制用户报错
[ERROR] [MY-010584] [Repl] Slave SQL for channel ‘group_replication_recovery’: Worker 1 failed executing transaction ‘2ca72062-b99f-11ed-a000-000c29b604d9:1’ at master log binlog.000002, end_log_pos 464; Error ‘Operation CREATE USER failed for ‘replicator’@’%” on query. Default database: ”. Query: ‘CREATE USER ‘replicator’@’%’ IDENTIFIED WITH ‘caching_sha2_password’ AS ‘$A$005$Ju_\n~0hQngc`c/UslQGjU0HxgL15VsMk5QzMhdyBoh6ynOy577ryRV5g/”, Error_code: MY-001396
[ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”. We stopped at log ‘binlog.000002’ position 153
个人的操作，删除从节点复制用户
mysql> STOP GROUP_REPLICATION;
Query OK, 0 rows affected (3.22 sec)
mysql> set global super_read_only=0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> drop user replicator@'%';
Query OK, 0 rows affected (0.02 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set global super_read_only=1;
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (4.49 sec)
7、从节点有数据库，复制时创建复制数据库报错
[ERROR] [MY-010584] [Repl] Slave SQL for channel ‘group_replication_recovery’: Worker 1 failed executing transaction ‘aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:55’ at master log binlog.000004, end_log_pos 1725; Error ‘Can’t create database ‘testdb’; database exists’ on query. Default database: ‘testdb’. Query: ‘CREATE DATABASE `testdb`’, Error_code: MY-001007
个人的操作，删除从节点的数据库
mysql> STOP GROUP_REPLICATION;
Query OK, 0 rows affected (3.22 sec)
mysql> set global super_read_only=0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database testdb;
Query OK, 293 rows affected (3.49 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set global super_read_only=1;
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (4.49 sec)