- 克隆 MySQL ( MGR ) 05 后的VM & 修改配置(本次為 211節(jié)點(diǎn)，其他212、213同理）
[root@xag200 ~]# service mysqld stop
Shutting down MySQL.... SUCCESS!
[root@xag200 ~]# vim /usr/local/mysql/data/auto.cnf
[root@xag200 ~]# cat /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=2116c7e9-63c9-11eb-a1ba-000c299e2211
[root@xag200 ~]# sed -i 's/127.0.0.1/xag211/' /root/.bashrc
[root@xag200 ~]# source /root/.bash_profile
[root@xag200 ~]# hostname
xag211
[root@xag200 ~]# cat /etc/hostname
xag211
[root@xag200 ~]# cat /etc/sysconfig/network
NETWORKING=yes
NOZEROCONF=yes
HOSTNAME=xag211
[root@xag200 ~]# cat /etc/hosts
...
192.168.40.211 xag211 xag211
192.168.40.212 xag212 xag212
192.168.40.213 xag213 xag213
[root@xag200 ~]# cat /etc/sysconfig/network-scripts/ifcfg-ens33
。。。
IPADDR="192.168.40.211"
。。。
[root@xag200 ~]# reboot
- 修改 /etc/my.cnf 中 #復(fù)制框架設(shè)置 及 #組復(fù)制設(shè)置 如下（xag212、xag213類似）
各個(gè)MGR節(jié)點(diǎn)除了server_id、loose-group_replication_local_address、report_host 三個(gè)參數(shù)不一樣外，其他保持一致。
#復(fù)制框架設(shè)置
server_id = 211
# 表示本機(jī)的序列號為1,如果做主從，或者多實(shí)例,serverid一定不能相同。
log_bin = /usr/local/mysql/binlog/binlog
# logbin數(shù)據(jù)庫的操作日志,例如update、delete、create等都會存儲到binlog日志,通過logbin可以實(shí)現(xiàn)增量恢復(fù)
binlog_format = row
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_slave_updates=ON
master_info_repository =TABLE
relay_log_info_repository=TABLE
#組復(fù)制設(shè)置
#server必須為每個(gè)事務(wù)收集寫集合，并使用XXHASH64哈希算法將其編碼為散列
transaction_write_set_extraction=XXHASH64
#告知插件加入或創(chuàng)建組命名，UUID
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#server啟動時(shí)不自啟組復(fù)制,為了避免每次啟動自動引導(dǎo)具有相同名稱的第二個(gè)組,所以設(shè)置為OFF。
loose-group_replication_start_on_boot=off
#告訴插件使用IP地址，端口24901用于接收組中其他成員轉(zhuǎn)入連接
loose-group_replication_local_address="xag211:24901"
#啟動組server，種子server，加入組應(yīng)該連接這些的ip和端口；其他server要加入組得由組成員同意
loose-group_replication_group_seeds="xag211:24901,xag:212:24901,xag213:24901"
loose-group_replication_bootstrap_group=off
report_host=xag211
report_port=3306
3.安裝MGR（每個(gè)節(jié)點(diǎn)）
配置完成后, 要一次啟動數(shù)據(jù)庫，安裝MGR插件，設(shè)置復(fù)制賬號（所有MGR節(jié)點(diǎn)都要執(zhí)行）
systemctl restart mysqld
mysql.login
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
- 啟動MGR單主模式
4.1 主庫(211)節(jié)點(diǎn)加入MGR集群
1) 啟動MGR，在主庫(211)節(jié)點(diǎn)上上執(zhí)行
root@xag211:(none) [:30: ] 1 SQL->
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
查看MGR組信息
SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+-------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+-------------+
| group_replication_applier | 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+-------------+
4.2 在其他節(jié)點(diǎn)加入MGR集群，在從庫（172.16.60.212）上執(zhí)行
root@xag212:(none) [:36: ] 1 SQL->
START GROUP_REPLICATION;
再次查看MGR組信息 (在三個(gè)MGR節(jié)點(diǎn)上都可以查看)
root@xag212:(none) [:37: ] 4 SQL->
SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211 | 3306 | ONLINE |
| group_replication_applier | 2126c7e9-63c9-11eb-a1ba-000c299e2212 | xag212 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
- 查看master
方法1
root@xag212:(none) [:50: ] 13 SQL->
SELECT ta.* ,tb.MEMBER_HOST,tb.MEMBER_PORT,tb.MEMBER_STATE FROM
performance_schema.global_status ta,performance_schema.replication_group_members tb
WHERE ta.VARIABLE_NAME='group_replication_primary_member' and ta.VARIABLE_VALUE=tb.MEMBER_ID;
+----------------------------------+--------------------------------------+-------------+-------------+--------------+
| VARIABLE_NAME | VARIABLE_VALUE | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+----------------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_primary_member | 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211 | 3306 | ONLINE |
+----------------------------------+--------------------------------------+-------------+-------------+--------------+
方法2
root@xag212:(none) [:52: ] 18 SQL->
SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME='group_replication_primary_member';
+--------------------------------------+
| VARIABLE_VALUE |
+--------------------------------------+
| 2116c7e9-63c9-11eb-a1ba-000c299e2211 |
+--------------------------------------+
方法3
root@xag212:(none) [:53: ] 19 SQL->SHOW STATUS LIKE 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 2116c7e9-63c9-11eb-a1ba-000c299e2211 |
+----------------------------------+--------------------------------------+
- 驗(yàn)證下MGR單主模式下節(jié)點(diǎn)數(shù)據(jù)的同步以及讀寫操作
主庫
root@xag211:(none) [:55: ] 7 SQL->show variables like '%read_only';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF |
| super_read_only | OFF |
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
root@xag211:(none) [:55: ] 8 SQL->use testdb;
root@xag211:testdb [:56: ] 9 SQL->select * from t1;
+----+----------+
| Id | TestName |
+----+----------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+----------+
root@xag211:testdb [:56: ] 10 SQL->insert into t1 values(4,'d');
root@xag211:testdb [:56: ] 11 SQL->select * from t1;
+----+----------+
| Id | TestName |
+----+----------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+----------+
備庫
root@xag212:(none) [:55: ] 22 SQL->show variables like '%read_only';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| super_read_only | ON |
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
root@xag212:(none) [:56: ] 23 SQL->use testdb;
root@xag212:testdb [:57: ] 24 SQL->select * from t1;
+----+----------+
| Id | TestName |
+----+----------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+----------+
root@xag212:testdb [:57: ] 25 SQL->insert into t1 values(5,'e');
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
- 關(guān)閉所有的組成員
關(guān)閉所有的組成員必須先先關(guān)閉slave，最后關(guān)閉master.
[root@xag212 ~]# service mysqld stop
root@xag211:testdb [:56: ] 12 SQL->select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
[root@xag211 ~]# systemctl stop mysqld
8.啟動所有的組成員
開啟組復(fù)制需要先開始primary，接著一個(gè)啟動slave server.
[root@xag211 ~]# service mysqld start
#由于group_replication_bootstrap_group參數(shù) 我們在配置文件中設(shè)為了OFF，所以需要手動打開設(shè)為ON.
root@xag211:(none) [:05: ] 1 SQL->show variables like 'group_replication_bootstrap_group';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| group_replication_bootstrap_group | OFF |
+-----------------------------------+-------+
root@xag211:(none) [:07: ] 3 SQL->set global group_replication_bootstrap_group=on;
root@xag211:(none) [:07: ] 4 SQL->select * from performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+-----------+-------------+-------------+--------------+
| group_replication_applier | | | NULL | OFFLINE |
+---------------------------+-----------+-------------+-------------+--------------+
#在沒有開啟組復(fù)制之前member_state為OFFLINE,開啟組復(fù)制之后記得將group_replication_bootstrap_group再設(shè)為off
root@xag211:(none) [:08: ] 6 SQL->start group_replication;
root@xag211:(none) [:08: ] 7 SQL->set global group_replication_bootstrap_group=off;
root@xag211:(none) [:09: ] 8 SQL->
select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
待primary server準(zhǔn)備好后，其它的slave數(shù)據(jù)庫可以一個(gè)個(gè)打開并開啟組復(fù)制
[root@xag212 ~]# service mysqld start
[root@xag212 ~]# mysql.login
root@xag212:(none) [:13: ] 2 SQL->start group_replication;
#啟動后檢查
root@xag211:(none) [:12: ] 10 SQL->
select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211 | 3306 | ONLINE |
| group_replication_applier | 2126c7e9-63c9-11eb-a1ba-000c299e2212 | xag212 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
- 第3個(gè)節(jié)點(diǎn)加入
root@xag213:(none) [:16: ] 1 SQL->START GROUP_REPLICATION;
root@xag213:(none) [:16: ] 2 SQL->
SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2116c7e9-63c9-11eb-a1ba-000c299e2211 | xag211 | 3306 | ONLINE |
| group_replication_applier | 2126c7e9-63c9-11eb-a1ba-000c299e2212 | xag212 | 3306 | ONLINE |
| group_replication_applier | 2136c7e9-63c9-11eb-a1ba-000c299e2213 | xag213 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
異常問題處理1
#如果報(bào)如下錯(cuò)誤
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
則查看錯(cuò)誤日志：
2019-03-04T09:11:30.683714Z 0 [ERROR] Plugin group_replication reported:
'This member has more executed transactions than those present in the group. Local transactions:
87135ebb-3e51-11e9-8931-005056880888:1-2 >
Group transactions: 851d03bb-3e51-11e9-8f8d-00505688047c:1-2,
8769f936-3e51-11e9-acaa-005056ac6820:1-2,
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-4'
2019-03-04T09:11:30.683817Z 0 [Warning] Plugin group_replication reported:
'The member contains transactions not present in the group.
It is only allowed to join due to group_replication_allow_local_disjoint_gtids_join option'
解決辦法:
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
然后再接著加入MGR集群
mysql> START GROUP_REPLICATION;
異常問題處理2
查看MGR組信息 (在三個(gè)MGR節(jié)點(diǎn)上都可以查看)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| group_replication_applier | 851d03bb-3e51-11e9-8f8d-00505688047c | 172.16.60.212 | 3306 | RECOVERING |
| group_replication_applier | 87135ebb-3e51-11e9-8931-005056880888 | 172.16.60.213 | 3306 | RECOVERING |
| group_replication_applier | 8769f936-3e51-11e9-acaa-005056ac6820 | 172.16.60.211 | 3306 | ONLINE |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
3 rows in set (0.00 sec)
發(fā)現(xiàn)新加入的MGR-node2 , MGR-node3兩個(gè)節(jié)點(diǎn)在集群里的狀態(tài)是RECOVERING!!!
查看日志
[root@MGR-node3 ~]# tail -2000 /var/log/mysqld.log
.....................
.....................
2019-03-04T09:15:35.146740Z 734 [ERROR] Slave I/O for channel 'group_replication_recovery': Got fatal error 1236 from master when
reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has
purged binary logs containing GTIDs that the slave requires.', Error_code: 1236
解決辦法:
登錄主庫172.16.60.211, 查看被purge的GTID：
[root@MGR-node1 ~]# mysql -p123456
....................
mysql> show global variables like 'gtid_purged';
+---------------+------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------+
| gtid_purged | 8769f936-3e51-11e9-acaa-005056ac6820:1-2 |
+---------------+------------------------------------------+
1 row in set (0.00 sec)
接著在兩個(gè)從庫172.16.60.212, 172.16.60.213的數(shù)據(jù)庫上執(zhí)行下面命令，即跳過這個(gè)GTID：
mysql> STOP GROUP_REPLICATION;
Query OK, 0 rows affected (10.14 sec)
mysql> reset master;
Query OK, 0 rows affected (0.06 sec)
mysql> set global gtid_purged = '8769f936-3e51-11e9-acaa-005056ac6820:1-2';
Query OK, 0 rows affected (0.24 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (3.49 sec)
再次查看查看MGR組信息 (在三個(gè)MGR節(jié)點(diǎn)上都可以查看),
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| group_replication_applier | 851d03bb-3e51-11e9-8f8d-00505688047c | 172.16.60.212 | 3306 | ONLINE |
| group_replication_applier | 87135ebb-3e51-11e9-8931-005056880888 | 172.16.60.213 | 3306 | ONLINE |
| group_replication_applier | 8769f936-3e51-11e9-acaa-005056ac6820 | 172.16.60.211 | 3306 | ONLINE |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
3 rows in set (0.00 sec)
通過上面可以看出: 三個(gè)MGR節(jié)點(diǎn)狀態(tài)為online，并且主節(jié)點(diǎn)為172.16.60.211，只有主節(jié)點(diǎn)可以寫入，其他兩個(gè)MGR節(jié)點(diǎn)只讀，MGR單主模式搭建成功。
