MySQL Group Replication 安装

创新互联建站是一家以重庆网站建设公司、网页设计、品牌设计、软件运维、成都网站营销、小程序App开发等移动开发为一体互联网公司。已累计为成都卫生间隔断等众行业中小客户提供优质的互联网建站和软件开发服务。

192.168.10.65
192.168.10.66
192.168.10.67

OS : CentOS 7.4
mysql soft : 8.0.12

一、安装MySQL，并创建实例
此处参考 mysql8.0.12源码安装

二、mgr几个指定参数，添加到配置文件中，重启生效
-- 配置文件
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE

transaction_write_set_extraction =XXHASH64 ###开启主键信息采集功能，8.0.2开始默认值为XXHASH64
loose-group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" ###设置组名，随便起，但是不能与UUID重复
loose-group_replication_start_on_boot =OFF ###为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF。
loose-group_replication_bootstrap_group =OFF ###同上
loose-group_replication_local_address ="192.168.10.65:24901" ###设置成员的本地地址，不同节点此处要修改为相应的IP地址
loose-group_replication_group_seeds ="192.168.10.65:24901,192.168.10.66:24902,192.168.10.67:24903" ###设置种子成员的地址
loose-group_replication_single_primary_mode =FALSE ###搭建多主模式
loose-group_replication_enforce_update_everywhere_checks =ON ###避免未检测到的外键冲突

-- 设置白名单，选做
loose-global group_replication_ip_whitelist="192.168.10.65,192.168.10.66,192.168.10.67";
loose-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";

三、操作第一个节点
-- 安装插件
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

-- 创建用户，要注意不能记录到binlog文件中
mysql> SET SQL_LOG_BIN=0; ###创建授权用户不写入bin_log
mysql> CREATE USER 'repl'@'192.168.%' identified by 'repl';
mysql> GRANT REPLICATION SLAVE ON . TO 'repl'@'192.168.%';
mysql> flush privileges;
mysql> SET SQL_LOG_BIN=1;

-- 这句只有第一个节点，在第一次执行引导组的时候执行。重启也需要。启动group_replication后关闭。
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
-- 启动
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

-- 查看当前mgr成员，判断第一个节点是否成功，member_state状态必须是ONLINE
mysql> SELECT * FROM performance_schema.replication_group_members;

四、操作第二、第三节点
-- 安装插件
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

-- 创建用户，要注意不能记录到binlog文件中
mysql> SET SQL_LOG_BIN=0; ###创建授权用户不写入bin_log
mysql> CREATE USER 'repl'@'192.168.%' identified by 'repl';
mysql> GRANT REPLICATION SLAVE ON . TO 'repl'@'192.168.%';
mysql> flush privileges;
mysql> SET SQL_LOG_BIN=1;

mysql> CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
-- 启动
mysql> START GROUP_REPLICATION;

常见错误：
一、无法连接端口
2018-09-18T16:31:04.579403+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Unable to announce tcp port 3306. Port already in use?'
2018-09-18T16:31:04.579607+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error joining the group while waiting for the network layer to become ready.'
2018-09-18T16:31:04.579741+08:00 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 3306'
2018-09-18T16:32:04.532459+08:00 8 [ERROR] [MY-011640] [Repl] Plugin group_replication reported: 'Timeout on wait for view after joining group'
2018-09-18T16:32:04.532736+08:00 8 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member is leaving a group without being on one.'

原因：
loose-group_replication_local_address ="192.168.10.65:3306"
loose-group_replication_group_seeds ="192.168.10.65:3306,192.168.10.66:3306,192.168.10.67:3306"
参数设置有问题，IP后面不是port，按照官网的例子，24901,24902,24903顺序填写即可

解决办法：
loose-group_replication_local_address ="192.168.10.65:24901"
loose-group_replication_group_seeds ="192.168.10.65:24901,192.168.10.66:24902,192.168.10.67:24903"

二、binlog导致的错误
2018-09-18T16:45:44.394139+08:00 0 [ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than t hose present in the group. Local transactions: 82ab7fe2-bb1c-11e8-a4ec-00505687bb25:1-11 > Group transactions: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa :1-2'
2018-09-18T16:45:44.394256+08:00 0 [ERROR] [MY-011522] [Repl] Plugin group_replication reported: 'The member contains transactions not present in t he group. The member will now exit the group.'

原因：
在创建用户的时候，记录到binlog中，这也就是为什么在创建用户时要设置不让该操作记录到binlog中

解决办法：
简单粗暴的方法，就是每个节点都先停止复制，重置master，然后按顺序启动复制。
stop group_replication;
reset master;
start group_replication;

网页名称：MySQLGroupReplication（MGR）安装
文章路径：http://azwzsj.com/article/ggcdip.html