MySQL Group Replication 安装

192.168.10.65

192.168.10.66192.168.10.67

OS : CentOS 7.4

mysql soft : 8.0.12

一、安装MySQL，并创建实例

此处参考 mysql8.0.12源码安装

二、mgr几个指定参数，添加到配置文件中，重启生效

-- 配置文件binlog_format = ROWgtid_mode = ONenforce_gtid_consistency = ONlog_slave_updates = ONmaster_info_repository = TABLErelay_log_info_repository = TABLEbinlog_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_logmysql> 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_logmysql> 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;