点击上方“IT那活儿”公众号--专注于企业全栈运维技术分享，不管IT什么活儿，干就完了！！！
1.1 概述
1.2 特点
同步复制 Group Replication的一个显著特点是支持同步复制。当在一个服务器上提交事务时，它会立即被复制到组中的所有其他服务器。这确保了在任何给定时间点，所有成员都具有相同的数据集。 自动组成员管理 Group Replication自动管理组的成员资格。当向组中添加新服务器时，它会自动与现有成员同步。如果服务器失败或离开组，剩余的服务器会自动调整以适应变化，确保持续运行。 多主复制 Group Replication组中的每个服务器都可以接受读和写事务。这允许分布式工作负载，并提供了改进的读可伸缩性。 一致性 Group Replication通过使用分布式认证过程确保所有成员的一致性。这意味着在提交事务之前，它会由组中大多数成员认证，确保数据保持一致。 容错性 该组设计为容错。如果一个或多个服务器失败，剩余的服务器可以继续提供读和写请求。当故障的服务器恢复时，它会自动重新与组同步。 冲突解决 在存在冲突事务的情况下，Group Replication使用投票机制来确定冲突事务的顺序。这有助于保持组内的一致状态。 与复制的兼容性 Group Replication与传统的MySQL异步复制兼容。这意味着您可以在同一环境中使用一些使用传统复制的服务器，而另一些使用Group Replication。
2.1 软件版本
8.0.36 MySQL Community Server |
2.2 资源规划
3.1 mysql安装
1）卸载mariadb（三台主机都执行）
[root@mysql03 ~]# rpm -e --nodeps mariadb-libs
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave
2）创建组及用户
[root@mysql03 ~]#groupadd mysql useradd -r -g mysql -s bin/false mysql
3）创建安装目录
[root@mysql03 ~]#mkdir -p /u01/app/mysql/log
[root@mysql03 ~]#mkdir -p /u01/app/mysql/sock/
[root@mysql03 ~]#mkdir -p /u01/app/mysql/binlog
[root@mysql03 ~]#chown -R mysql:mysql /u01/mysql/
4）编写参数文件
[mysql]
# prompt = [\\u@\\h][\\d]>\\_
socket = /u01/app/mysql/sock/mysql.sock
[mysqld]
#basic settings#
user = mysql
port = 3306
character_set_server = utf8mb4 # 默认
collation_server = utf8mb4_bin
pid-file = /u01/app/mysql/sock/mysql.pid
socket = /u01/app/mysql/sock/mysql.sock
basedir = /u01/app/mysql
datadir = /u01/app/mysql/data/
transaction_isolation = READ-COMMITTED
server_id = 1
sysdate_is_now = 1
lower_case_table_names = 1
#log settings#
general_log_file = /u01/app/mysql/log/general.log
log_error = /u01/app/mysql/log/error.log
slow_query_log = 1
slow_query_log_file = /u01/app/mysql/log/slow-query.log
log_bin = /u01/app/mysql/binlog/mysql-bin
log_bin_index = /u01/app/mysql/binlog/mysql-bin.index
binlog_expire_logs_seconds = 1296000 # 15天之前的日志自动删除
relay_log_index = /u01/app/mysql/mysql-relay-bin.index
relay_log = /u01/app/mysql/mysql-relay-bin
log_timestamps = system
#innodb settings#
innodb_data_home_dir = /u01/app/mysql/data/
innodb_data_file_path = ibdata1:4096M:autoextend
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G
innodb_temp_tablespaces_dir = /u01/app/mysql/data/
innodb_buffer_pool_size = 1G
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 4000
innodb_flush_method = O_DIRECT
innodb_undo_directory = /u01/app/mysql/data/
innodb_redo_log_capacity=100663296
#innodb_log_files_in_group = 2
innodb_log_group_home_dir = /u01/app/mysql/log/
innodb_print_all_deadlocks = 1
gtid_mode=ON
enforce_gtid_consistency=ON
sync_binlog = 1
5）初始化数据库
./mysqld --defaults-file=/etc/my.cnf --basedir=/u01/app/mysql --datadir=/u01/app/mysql/data --user=mysql --initialize
6）修改密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '*******';
mysql>flush privileges;
7）制作mysql服务
[root@mysql03 /]#cp /u01/app/mysql/support-files/mysql.server /etc/init.d/mysql
修改以下
basedir=/u01/app/mysql
datadir=/u01/app/data
测试
[root@mysql02 ~]#service mysql restart
Shutting down MySQL... SUCCESS!
Starting MySQL.. SUCCESS!
[root@mysql02 ~]#
3.2 MGR配置
1）修改my.cnf
添加以下参数：
transaction_write_set_extraction = XXHASH64
#相当于此GROUP的名字,是UUID值,不能和集群内其他GTID值的UUID混用,可用uuidgen来生成一个新的,
#主要是用来区分整个内网里边的各个不同的GROUP,而且也是这个group内的GTID值的UUID
loose-group_replication_group_name = '5dbabbe6-8050-49a0-9131-1de449167446'
#IP地址白名单,默认只添加127.0.0.1,不会允许来自外部主机的连接,按需安全设置
loose-group_replication_ip_whitelist = 'localhost/8,mysql01/24,mysql02/24,mysql03/24'
#是否随服务器启动而自动启动组复制,不建议直接启动,怕故障恢复时有扰乱数据准确性的特殊情况
loose-group_replication_start_on_boot = OFF
#本地MGR的IP地址和端口，host:port,是MGR的端口,不是数据库的端口
loose-group_replication_local_address = 'mysql01:33081'
#需要接受本MGR实例控制的服务器IP地址和端口,是MGR的端口,不是数据库的端口
loose-group_replication_group_seeds = 'mysql01:33081,mysql02:33081,mysql03:33081'
#开启引导模式,添加组成员，用于第一次搭建MGR或重建MGR的时候使用,只需要在集群内的其中一台开启,
loose-group_replication_bootstrap_group = OFF
#是否启动单主模式，如果启动，则本实例是主库，提供读写，其他实例仅提供读,如果为off就是多主模式了
loose-group_replication_single_primary_mode = ON
#多主模式下,强制检查每一个实例是否允许该操作,如果不是多主,可以关闭
#loose-group_replication_enforce_update_everywhere_checks = on
group_replication_recovery_get_public_key=ON
2）创建同步与克隆用户
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@’%’ IDENTIFIED BY ‘******’;
grant replication slave,replication client on . to rpl_user@’%’;
create user clone_user@'%' identified by '1';
grant backup_admin on *.* to clone_user;
3）安装组复制插件
主库安装复制插件
MYSQL>install plugin clone soname 'mysql_clone.so';
MYSQL>INSTALL PLUGIN group_replication SONAME 'group_replication.so';
##确认插件安装成功
MYSQL>SHOW PLUGINS;
clone | ACTIVE | CLONE | mysql_clone.so | GPL
group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL
#
MYSQL>SET SQL_LOG_BIN=1;
从库安装插件
MYSQL>INSTALL PLUGIN group_replication SONAME 'group_replication.so';
MYSQL>install plugin clone soname 'mysql_clone.so';
MYSQL>SHOW PLUGINS;
clone | ACTIVE | CLONE | mysql_clone.so | GPL
group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL
4）启动组复制
set global group_replication_bootstrap_group=ON;
start group_replication;
出现以下报错：
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.
检查日志
2024-06-12T21:53:15.633975-07:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Unable to announce tcp port 3306. Port already in use?'
2024-06-12T21:53:15.634298-07:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error initializing the group communication engine.'
2024-06-12T21:53:15.634345-07:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Unable to start XCom Network Provider'
2024-06-12T21:53:15.634565-07:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error joining the group while waiting for the network layer to become ready.'
#检查Selinux配置
cat /etc/selinux/config|grep SELINUX=
# SELINUX= can take one of these three values:
SELINUX=disabled
#关闭防火墙
Systemctl stop firewalld
Systemctl disable firewalld
#再次启动组复制
mysql> set global group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
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.
#查看mos,获得以下信息
Starting Group Replication or Creating InnoDB Cluster Fails: "[ERROR] Plugin group_replication reported: 'Unable to announce tcp port 13306. Port already in use?'" (Doc ID 2309228.1)
主端执行
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 warning (1.11 sec)
mysql> set global group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected, 1 warning (1.11 sec)
备端执行
mysql> set global clone_valid_donor_list='192.168.10.141:3306';
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='1' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.01 sec)
mysql> stop group_replication;
Query OK, 0 rows affected (0.00 sec)
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 | 483d3e2e-29ff-11ef-a9e8-000c2978aed2 | mysql01 | 3306 | ONLINE | PRIMARY | 8.0.36 | XCom |
| group_replication_applier | b3906cb1-29ff-11ef-8a7a-000c290eec14 | mysql02 | 3306 | ONLINE | SECONDARY | 8.0.36 | XCom |
| group_replication_applier | b939b15f-2a14-11ef-badd-000c29133991 | mysql03 | 3306 | ONLINE | SECONDARY | 8.0.36 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
rows in set (0.00 sec)
5）简单测试
主库创建数据库 luwf
mysql> create database luwf;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| luwf |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
备库查询
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| luwf |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
常见问题及处理方案：
启动组复制报错1：
2024-06-14T02:21:31.126952-07:00 24 [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.'
2024-06-14T02:21:31.126969-07:00 24 [ERROR] [MY-011583] [Repl] Plugin group_replication reported: 'For details please check performance_schema.replication_connection_status table and error log messages of Replica I/O for channel group_replication_recovery.'
2024-06-14T02:21:31.127142-07:00 24 [ERROR] [MY-011574] [Repl] Plugin group_replication reported: 'Maximum number of retries when trying to connect to a donor reached. Aborting group replication incremental recovery.'
这个问题困扰很久，问题关键是错误提示不够明确。提示的是DNS无法解析服务名。
经检查防火墙，SELINUX配置，均未发现问题。
随后发现该问题的关键是在备端执行时需要先设置donor_list.然后再更改连接主机的帐号信息，请参考 3.2 启动组复制—备库配置章节。
启动组复制报错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；
本文作者：鲁伟锋(上海新炬中北团队）
本文来源：“IT那活儿”公众号
文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
