MGR 5.7 多主集群搭建
参考文档：
https://dev.mysql.com/doc/refman/5.7/en/group-replication-configuring-instances.html
https://www.cnblogs.com/manger/p/7211932.html
1、搭建三个MYSQL 5.7，暂时不需要配置为主从复制，
192.168.150.101
192.168.150.102
192.168.150.103
安装方法如下：
https://www.cnblogs.com/nanxiang/p/12888222.html
三个实例，server_id分别配置为1，2，3
2、三个实例安装组复制插件
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.03 sec)
3、参考组复制要求，修改配置文件，重启数据库
https://dev.mysql.com/doc/refman/5.7/en/group-replication-requirements.html
my.cnf，各个节点，要做对应的修改，然后重启数据库生效
#MGR基础配置 disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" server_id=1 #每个节点要不一样 log-bin=mysql-bin log-slave-updates binlog-format=row binlog-checksum=NONE gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE transaction-write-set-extraction=XXHASH64 ##lower-case-table-names=1 这个参数在初始化时默认就是1，可以不用修改。 slave_parallel_type=LOGICAL_CLOCK slave_parallel_workers=4 slave_preserve_commit_order=1 #MGR参数配置 transaction_write_set_extraction = XXHASH64 #以便在server收集写集合的同时将其记录到二进制日志。写集合基于每行的主键，并且是行更改后的唯一标识此标识将用于检测冲突。 loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212889f856' #组的名字可以随便起,但不能用主机的GTID，select uuid()可随机获取一个UUID loose-group_replication_start_on_boot = off #为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF。 loose-group_replication_bootstrap_group = off #同上 loose-group_replication_local_address = '192.168.150.101:33061' #写自己主机所在IP loose-group_replication_group_seeds ='192.168.150.101:33061,192.168.150.102:33061,192.168.150.103:33061' loose-group_replication_single_primary_mode = off #关闭单主模式的参数 loose-group_replication_enforce_update_everywhere_checks = on #开启多主模式的参数
4、在 101 节点创建repl用户，并授权
mysql>SET GLOBAL group_replication_bootstrap_group=ON;
mysql> create user repl identified by '123456'; Query OK, 0 rows affected (10.01 sec) mysql> grant all privileges on *.* to repl; Query OK, 0 rows affected (0.00 sec)
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 | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 9fef2262-97b1-11ea-92b5-000c29cd3ff3 | host101 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+
5、102和103节点分别加入到组复制中
SET SQL_LOG_BIN=0; create user repl identified by '123456'; grant all privileges on *.* to repl; SET SQL_LOG_BIN=1; CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery'; START GROUP_REPLICATION;
6、查询101节点的replication_group_members表
mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 83f050f5-a871-11ea-9161-000c29fa663f | host103 | 3306 | ONLINE | | group_replication_applier | 9fef2262-97b1-11ea-92b5-000c29cd3ff3 | host101 | 3306 | ONLINE | | group_replication_applier | adc4403d-97b2-11ea-b803-000c298076e0 | host102 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+
7、测试多主MGR
101节点
mysql> create database ceshi; Query OK, 1 row affected (10.01 sec)
102节点
mysql> create table t1(id int primary key); Query OK, 0 rows affected (0.02 sec)
103节点
mysql> insert into t1 select 1; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0
101节点
mysql> select * from ceshi.t1; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)
踩坑：
【2020-11-15T14:29:40.846054Z 31 [ERROR] Slave I/O for channel 'group_replication_recovery': error connecting to master 'repl@host101:3306' - retry-time: 60 retries: 1, Error_code: 2005】
连接超时，我没有配置/etc/hosts文件，配置如下后，解决问题。
192.168.150.101 host101 192.168.150.102 host102 192.168.150.103 host103
【2020-11-15T14:40:53.714400Z 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: adc4403d-97b2-11ea-b803-000c298076e0:1,
ce9be252-2b71-11e6-b8f4-00212889f856:1 > Group transactions: ce9be252-2b71-11e6-b8f4-00212889f856:1-6'
2020-11-15T14:40:53.714449Z 0 [ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'】
我用的已有环境，一直在做测试，本地事务比组内事还要多，所以报错了。
解决方法：
reset master; ---初始化一下位点，重新启动组复制即可。
