MySQL Group Replication （MGR） 安裝

MySQL Group Replication 安裝

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;