mysql组复制总结

1 安装mysql5.7
2 配置hosts映射
cat /etc/hosts
172.19.84.44 mgr1
172.19.84.45 mgr2
172.19.84.43 mgr3
重启服务器
3 my.cnf [mysqld]下增加如下配置
#基本配置
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
# 组复制设置
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="172.19.84.44:36901"
loose-group_replication_group_seeds= "172.19.84.44:36901,172.19.84.45:36901,172.19.84.43:36901"
loose-group_replication_bootstrap_group=off
如果使用多主模式增加
[group_replication_single_primary_mode=OFF](https://dev.mysql.com/doc/refman/5.7/en/group-replication- options.html#sysvar_group_replication_single_primary_mode) #关闭指示组自动选择单个服务器作为处理读/写工作负载的服务器
group_replication_enforce_update_everywhere_checks=ON #在所有位置启用多主数据更新的严格一致性检查,在单主模式下，这不是问题，因为它不允许并发写入组的多个成员，因此不存在未检测到的冲突的风险。
组中其他服务器的配置类似。server_id，loose-group_replication_local_address等需要根据当前服务器的情况设置.

执行：
service mysqld restart

6
mysql> create user repl@'%' identified by 'mysql';
grant replication slave on . to repl@'%';
FLUSH PRIVILEGES;

默认不允许公网的互相连接，如果未明确指定白名单，则服务器会自动将白名单设置为服务器具有接口的专用网络。这意味着服务器即使在公共IP上具有接口，也不会默认允许来自外部主机的连接。
需要为所有成员指定相同的白名单。
SET GLOBAL group_replication_ip_whitelist="192.0.2.21/24,198.51.100.44,203.0.113.0/24,example.org,www.example.com/24";

7 CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='mysql' FOR CHANNEL 'group_replication_recovery';
change master to master_user='repl', master_password='mysql' for channel 'group_replication_recovery';

8 INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SHOW PLUGINS;

9 启动组
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

10 检查组是否创建成功
SELECT * FROM performance_schema.replication_group_members;

11 检测服务器确实在一个组中并且它能够处理负载：
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');

SELECT * FROM t1;
SHOW BINLOG EVENTS;

12 在第二个服务器

SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY 'mysql';
GRANT REPLICATION SLAVE ON . TO repl@'%';
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='mysql' FOR CHANNEL 'group_replication_recovery';

INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SHOW PLUGINS;

set global group_replication_allow_local_disjoint_gtids_join=ON;
将服务器s2添加到组中: START GROUP_REPLICATION;

SELECT * FROM performance_schema.replication_group_members;

验证：
SHOW DATABASES LIKE 'test';
SELECT * FROM test.t1;
SHOW BINLOG EVENTS;
===========================查找主服务器========================================
SHOW STATUS LIKE 'group_replication_primary_member'
=============================主要问题解决==================================
从服务器启动之后一直报：
RECOVERING :

方法1 ：重启服务器之后。
主服务器mysql执行reset master.
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

从服务器；
reset master
直接
set global group_replication_allow_local_disjoint_gtids_join=ON;
START GROUP_REPLICATION;
reset master。

方法2：
1 在正常节点：
select * from performance_schema.replication_group_member_stats \G ;
2 获取正常节点的TRANSACTIONS_COMMITTED_ALL_MEMBERS
值: eg: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-151,b376058d-5762-11e7-baa0-000c29e6b568:1-47678
3 在非正常节点执行：
set global gtid_purged='740ab1ab-d818-11e8-a6bc-00163e0aecff:1-7,
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-37';
4 start group_replication;

5 如果还不行，第一步加上reset master;
6 如果第3步提示提示ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. ，先执行reset master;
=============================================================

1：没有设置白名单网段:需要添加自己所在网段.

报错:'[GCS] The member is leaving a group without being on one.'

解决:set global group_replication_ip_whitelist="127.0.0.1/32,10.0.0.0/8,172.16.0.0/12,192.168.0.0/16,192.168.1.0/24"; START GROUP_REPLICATION;

从服务启动复制如果报错：
2：ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.1
解决方案：
根据提示打开group_replication_allow_local_disjoint_gtids_join选项，mysql命令行执行:
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
再次启动组复制
mysql> START GROUP_REPLICATION;