? 介紹
MGR? 是MySQL 是數(shù)據(jù)庫未來發(fā)展的一個重要方向
引擎
2.3?? 設(shè)置hostname和ip映射
1? ）?
設(shè)置? /etc/hosts?
三臺同樣的配置?
2) 登錄數(shù)據(jù)庫開始授權(quán)
grant replication slave on *.* to repl@'192.168.24.%' identified by '123456';
3? ）? 打開/ etc/my.cnf? 的配置文件
Group Replication
server_id = 100? #服務(wù)ID
gtid_mode = ON? #全局事務(wù)
enforce_gtid_consistency = ON? #強制GTID的一致性
master_info_repository = TABLE? #將master.info元數(shù)據(jù)保存在系統(tǒng)表中
relay_log_info_repository = TABLE? #將relay.info元數(shù)據(jù)保存在系統(tǒng)表中
binlog_checksum = NONE? #禁用二進制日志事件校驗
log_slave_updates = ON? #級聯(lián)復(fù)制
log_bin = binlog? #開啟二進制日志記錄
binlog_format= ROW? #以行的格式記錄
transaction_write_set_extraction = XXHASH64 #使用哈希算法將其編碼為散列
loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856' #加入的組名
loose-group_replication_start_on_boot = off #不自動啟用組復(fù)制集群
loose-group_replication_local_address = 'xuegod1:33061' #以本機端口33061接受來自組中成員的傳入連接
loose-group_replication_group_seeds = 'xuegod1:33061, xuegod2:33062, xuegod1:33063' #組中成員訪問表
loose-group_replication_bootstrap_group = off #不啟用引導(dǎo)組??
注釋掉第一行否則會報錯
? 4? ） 重啟mysql 服務(wù)器
5? ）? 登入mysql?
?? 修改master???
??change master to master_user='repl',master_password='123456'??for channel 'group_replication_recovery';
6? )?? 安裝 插件?
??install PLUGIN group_replication SONAME 'group_replication.so';
7? ） 再次使用show? 查看一下??? 就會看到?
??? show? flugins??
?
8? )? 啟動第一個節(jié)點? 手工開機引導(dǎo)組? 進入mysql 集群以后
? ?set global group_replication_bootstrap_group=ON;
?
9? )? 開啟引導(dǎo)
4. 下面分別記錄下 MGR 基于單主模式和多主模式的集群環(huán)境部署過程
4.1 準備環(huán)境
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
三臺服務(wù)器
172.16.60.211???? MGR-node1??? server_id=1
172.16.60.212???? MGR-node2??? server_id=2
172.16.60.213???? MGR-node3??? server_id=3
[root@MGR-node1 ~]# cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)
為了方便實驗，關(guān)閉所有節(jié)點的防火墻
[root@MGR-node1 ~]# systemctl stop firewalld
[root@MGR-node1 ~]# firewall-cmd --state
not running
[root@MGR-node1 ~]# cat /etc/sysconfig/selinux |grep "SELINUX=disabled"
SELINUX=disabled
[root@MGR-node1 ~]# setenforce 0???????????
setenforce: SELinux is disabled
[root@MGR-node1 ~]# getenforce?????????????
Disabled
特別要注意一個關(guān)鍵點: 必須保證各個mysql節(jié)點的主機名不一致，并且能通過主機名找到各成員！
則必須要在每個節(jié)點的/etc/hosts里面做主機名綁定，否則后續(xù)將節(jié)點加入group組會失敗！報錯RECOVERING?。?/p>
[root@MGR-node1 ~]# cat /etc/hosts
........
172.16.60.211??? MGR-node1
172.16.60.212??? MGR-node2
172.16.60.213??? MGR-node3
4.2? 在三個節(jié)點上安裝Mysql5.7
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
在三個mysql節(jié)點機上使用yum方式安裝Mysql5.7，參考：https://www.cnblogs.com/kevingrace/p/8340690.html
安裝MySQL yum資源庫
[root@MGR-node1 ~]# yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
安裝MySQL 5.7
[root@MGR-node1 ~]# yum install -y mysql-community-server
啟動MySQL服務(wù)器和MySQL的自動啟動
[root@MGR-node1 ~]# systemctl start mysqld.service
[root@MGR-node1 ~]# systemctl enable mysqld.service
設(shè)置登錄密碼
由于MySQL從5.7開始不允許首次安裝后使用空密碼進行登錄！為了加強安全性，系統(tǒng)會隨機生成一個密碼以供管理員首次登錄使用，
這個密碼記錄在/var/log/mysqld.log文件中，使用下面的命令可以查看此密碼：
[root@MGR-node1 ~]# cat /var/log/mysqld.log|grep 'A temporary password'
2019-01-11T05:53:17.824073Z 1 [Note] A temporary password is generated?for?root@localhost: TaN.k:*Qw2xs
使用上面查看的密碼TaN.k:*Qw2xs 登錄mysql，并重置密碼為123456
[root@MGR-node1 ~]# mysql -p???????????????? #輸入默認的密碼：TaN.k:*Qw2xs
.............
mysql>?set?global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql>?set?global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql>?set?password=password("123456");
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
查看mysql版本
[root@MGR-node1 ~]# mysql -p123456
........
mysql>?select?version();
+-----------+
| version() |
+-----------+
| 5.7.24??? |
+-----------+
1 row?in?set?(0.00 sec)
=====================================================================
溫馨提示
mysql5.7通過上面默認安裝后，執(zhí)行語句可能會報錯：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
這個報錯與Mysql 密碼安全策略validate_password_policy的值有關(guān)，validate_password_policy可以取0、1、2三個值：
解決辦法：
set?global validate_password_policy=0;
set?global validate_password_length=1;
4.3? 安裝和配置MGR信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
1) 配置所有節(jié)點的組復(fù)制信息
MGR-node01節(jié)點
[root@MGR-node1 ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@MGR-node1 ~]# >/etc/my.cnf
[root@MGR-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir =?/var/lib/mysql
socket =?/var/lib/mysql/mysql.sock
symbolic-links = 0
log-error =?/var/log/mysqld.log
pid-file?=?/var/run/mysqld/mysqld.pid
#復(fù)制框架
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
#組復(fù)制設(shè)置
#server必須為每個事務(wù)收集寫集合，并使用XXHASH64哈希算法將其編碼為散列
transaction_write_set_extraction=XXHASH64
#告知插件加入或創(chuàng)建組命名，UUID
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#server啟動時不自啟組復(fù)制,為了避免每次啟動自動引導(dǎo)具有相同名稱的第二個組,所以設(shè)置為OFF。
loose-group_replication_start_on_boot=off
#告訴插件使用IP地址，端口24901用于接收組中其他成員轉(zhuǎn)入連接
loose-group_replication_local_address="172.16.60.211:24901"
#啟動組server，種子server，加入組應(yīng)該連接這些的ip和端口；其他server要加入組得由組成員同意
loose-group_replication_group_seeds="172.16.60.211:24901,172.16.60.212:24901,172.16.60.213:24901"
loose-group_replication_bootstrap_group=off
report_host=172.16.60.211
report_port=3306
如上配置完成后, 將MGR-node1節(jié)點的/etc/my.cnf文件拷貝到其他兩個節(jié)點
[root@MGR-node1 ~]# rsync -e "ssh -p22" -avpgolr /etc/my.cnf root@172.16.60.212:/etc/
[root@MGR-node1 ~]# rsync -e "ssh -p22" -avpgolr /etc/my.cnf root@172.16.60.213:/etc/
3個MGR節(jié)點除了server_id、loose-group_replication_local_address、report_host 三個參數(shù)不一樣外，其他保持一致。
所以待拷貝完成后, 分別修改MGR-node2和MGR-node3節(jié)點/etc/my.cnf文件的server_id、loose-group_replication_local_address、report_host 三個參數(shù)
2) 配置完成后, 要一次啟動數(shù)據(jù)庫，安裝MGR插件，設(shè)置復(fù)制賬號（所有MGR節(jié)點都要執(zhí)行）
[root@MGR-node1 ~]# systemctl restart mysqld
[root@MGR-node1 ~]# mysql -p123456
.............
mysql> INSTALL PLUGIN group_replication SONAME?'group_replication.so';
Query OK, 0 rows affected (0.13 sec)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER repl@'%'?IDENTIFIED BY?'repl';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl'?FOR CHANNEL?'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.21 sec)
4.4? 啟動MGR單主模式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
1) 啟動MGR，在主庫(172.16.60.11)節(jié)點上上執(zhí)行
[root@MGR-node1 ~]# mysql -p123456
...............
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.31 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
查看MGR組信息
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| CHANNEL_NAME????????????? | MEMBER_ID??????????????????????????? | MEMBER_HOST?? | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
| group_replication_applier | 8769f936-3e51-11e9-acaa-005056ac6820 | 172.16.60.211 |??????? 3306 | ONLINE?????? |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
1 row?in?set?(0.01 sec)
2) 在其他節(jié)點加入MGR集群，在從庫（172.16.60.212，172.16.60.213）上執(zhí)行
[root@MGR-node2 ~]#? mysql -p123456
................
mysql> START GROUP_REPLICATION;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see?more?details on error log.
查看日志:
[root@MGR-node2 ~]# tail -2000 /var/log/mysqld.log
.....................
.....................
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;
Query OK, 0 rows affected, 1 warning (0.00 sec)
然后再接著加入MGR集群
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (5.14 sec)
3) 再次查看MGR組信息 (在三個MGR節(jié)點上都可以查看)
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兩個節(jié)點在集群里的狀態(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)
接著在兩個從庫172.16.60.212, 172.16.60.213的數(shù)據(jù)庫上執(zhí)行下面命令，即跳過這個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組信息 (在三個MGR節(jié)點上都可以查看),
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)
通過上面可以看出:? 三個MGR節(jié)點狀態(tài)為online，并且主節(jié)點為172.16.60.211，只有主節(jié)點可以寫入，其他兩個MGR節(jié)點只讀，MGR單主模式搭建成功。
==============================================================================
驗證下MGR單主模式下節(jié)點數(shù)據(jù)的同步以及讀寫操作:
先在主庫節(jié)點172.16.60.211上創(chuàng)建測試數(shù)據(jù)庫
[root@MGR-node1 ~]# mysql -p123456
..............
mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci;??
Query OK, 1 row affected (0.06 sec)
mysql> use kevin;
Database changed
mysql> create table?if?not exists haha (id?int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.24 sec)
mysql> insert into kevin.haha values(1,"wangshibo"),(2,"guohuihui"),(3,"yangyang"),(4,"shikui");
Query OK, 4 rows affected (0.13 sec)
Records: 4? Duplicates: 0? Warnings: 0
mysql>?select?* from kevin.haha;
+----+-----------+
|?id?| name????? |
+----+-----------+
|? 1 | wangshibo |
|? 2 | guohuihui |
|? 3 | yangyang? |
|? 4 | shikui??? |
+----+-----------+
4 rows?in?set?(0.00 sec)
接著在其他的兩個從節(jié)點172.16.60.212和172.16.60.213上查看數(shù)據(jù), 發(fā)現(xiàn)主庫數(shù)據(jù)已經(jīng)同步到兩個從庫上了
[root@MGR-node2 ~]# mysql -p123456
..................
mysql>?select?* from kevin.haha;
+----+-----------+
|?id?| name????? |
+----+-----------+
|? 1 | wangshibo |
|? 2 | guohuihui |
|? 3 | yangyang? |
|? 4 | shikui??? |
+----+-----------+
4 rows?in?set?(0.00 sec)
然后嘗試在兩個從庫上更新數(shù)據(jù), 發(fā)現(xiàn)更新失敗! 因為這是MGR單主模式, 從庫只能進行讀操作, 不能進行寫操作!
[root@MGR-node3 ~]# mysql -p123456
.................
mysql>?select?* from kevin.haha;
+----+-----------+
|?id?| name????? |
+----+-----------+
|? 1 | wangshibo |
|? 2 | guohuihui |
|? 3 | yangyang? |
|? 4 | shikui??? |
+----+-----------+
4 rows?in?set?(0.00 sec)
mysql> delete from kevin.haha where?id>3;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql> insert into kevin.haha values(11,"beijing"),(12,"shanghai"),(13,"anhui");
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
只有在主庫上才能進行寫操作
[root@MGR-node1 ~]# mysql -p123456
..............
mysql> insert into kevin.haha values(11,"beijing"),(12,"shanghai"),(13,"anhui");
Query OK, 3 rows affected (0.15 sec)
Records: 3? Duplicates: 0? Warnings: 0
mysql>?select?* from kevin.haha;
+----+-----------+
|?id?| name????? |
+----+-----------+
|? 1 | wangshibo |
|? 2 | guohuihui |
|? 3 | yangyang? |
|? 4 | shikui??? |
| 11 | beijing?? |
| 12 | shanghai? |
| 13 | anhui???? |
+----+-----------+
7 rows?in?set?(0.00 sec)
4.5 切換到多主模式
MGR切換模式需要重新啟動組復(fù)制，因些需要在所有節(jié)點上先關(guān)閉組復(fù)制，設(shè)置 group_replication_single_primary_mode=OFF 等參數(shù)，再啟動組復(fù)制。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
1) 停止組復(fù)制(在所有MGR節(jié)點上執(zhí)行)：
mysql> stop group_replication;
Query OK, 0 rows affected (9.08 sec)
mysql>?set?global group_replication_single_primary_mode=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql>?set?global group_replication_enforce_update_everywhere_checks=ON;
Query OK, 0 rows affected (0.00 sec)
2) 隨便選擇某個MGR節(jié)點執(zhí)行 (比如這里選擇在MGR-node1節(jié)點):
[root@MGR-node1 ~]# mysql -p123456
...............
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.20 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
3) 然后在其他的MGR節(jié)點執(zhí)行 (這里指MGR-node2和MGR-node3節(jié)點上執(zhí)行):
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (5.89 sec)
4) 查看MGR組信息 (在任意一個MGR節(jié)點上都可以查看)
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)
可以看到所有MGR節(jié)點狀態(tài)都是online，角色都是PRIMARY，MGR多主模式搭建成功。
=========================================
驗證下MGR多主模式的節(jié)點數(shù)據(jù)同步:
在MGR-node1節(jié)點更新數(shù)據(jù):
[root@MGR-node1 ~]# mysql -p123456
.................
mysql>?select?* from kevin.haha;
+----+-----------+
|?id?| name????? |
+----+-----------+
|? 1 | wangshibo |
|? 2 | guohuihui |
|? 3 | yangyang? |
|? 4 | shikui??? |
| 11 | beijing?? |
| 12 | shanghai? |
| 13 | anhui???? |
+----+-----------+
7 rows?in?set?(0.00 sec)
mysql> delete from kevin.haha where?id>10;
Query OK, 3 rows affected (0.08 sec)
在MGR-node2節(jié)點更新數(shù)據(jù)
[root@MGR-node2 ~]# mysql -p123456
...............
mysql>?select?* from kevin.haha;
+----+-----------+
|?id?| name????? |
+----+-----------+
|? 1 | wangshibo |
|? 2 | guohuihui |
|? 3 | yangyang? |
|? 4 | shikui??? |
+----+-----------+
4 rows?in?set?(0.00 sec)
mysql> insert into kevin.haha values(11,"beijing"),(12,"shanghai"),(13,"anhui");
Query OK, 3 rows affected (0.08 sec)
Records: 3? Duplicates: 0? Warnings: 0
在MGR-node3節(jié)點更新數(shù)據(jù)
[root@MGR-node3 ~]# mysql -p123456
.............
mysql>?select?* from kevin.haha;
+----+-----------+
|?id?| name????? |
+----+-----------+
|? 1 | wangshibo |
|? 2 | guohuihui |
|? 3 | yangyang? |
|? 4 | shikui??? |
| 11 | beijing?? |
| 12 | shanghai? |
| 13 | anhui???? |
+----+-----------+
7 rows?in?set?(0.00 sec)
mysql> delete from kevin.haha where?id>11;
Query OK, 2 rows affected (0.14 sec)
mysql>?select?* from kevin.haha;
+----+-----------+
|?id?| name????? |
+----+-----------+
|? 1 | wangshibo |
|? 2 | guohuihui |
|? 3 | yangyang? |
|? 4 | shikui??? |
| 11 | beijing?? |
+----+-----------+
5 rows?in?set?(0.00 sec)
如上, MGR多主模式下, 所有節(jié)點都可以進行讀寫操作.
4.6? 切回單主模式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
1) 停止組復(fù)制(在所有MGR節(jié)點上執(zhí)行)：
mysql> stop group_replication;
Query OK, 0 rows affected (9.29 sec)
mysql>?set?global group_replication_enforce_update_everywhere_checks=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql>?set?global group_replication_single_primary_mode=ON;
Query OK, 0 rows affected (0.00 sec)
2) 選擇一個節(jié)點作為主節(jié)點, 在主節(jié)點上執(zhí)行 (這里選擇MGR-node1節(jié)點作為主節(jié)點)
[root@MGR-node1 ~]# mysql -p123456
................
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.12 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
3) 在其他剩余的節(jié)點, 也就是從庫節(jié)點上執(zhí)行 (這里從庫節(jié)點指的就是MGR-node2和MGR-node3):
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (6.16 sec)
4) 查看MGR組信息 (在任意一個MGR節(jié)點上都可以查看)
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)
這樣就又切回到MGR單主模式了, 其中172.16.60.211是主節(jié)點,具有寫權(quán)限. 另外兩個節(jié)點172.16.60.212和172.16.60.213是從庫節(jié)點, 只能讀不能寫.
4.7? 故障切換
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
1) 單主模式
如果主節(jié)點掛掉了, 通過選舉程序會從從庫節(jié)點中選擇一個作為主庫節(jié)點.? 如下模擬故障:
關(guān)閉主庫MGR-node1的mysqld服務(wù)
[root@MGR-node1 ~]# systemctl stop mysqld
接著在其他節(jié)點上查看MGR組信息. 比如在MGR-node2節(jié)點查看
[root@MGR-node2 ~]# mysql -p123456
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?????? |
+---------------------------+--------------------------------------+---------------+-------------+--------------+
2 rows?in?set?(0.00 sec)
嘗試在MGR-node2節(jié)點更新數(shù)據(jù)
mysql>?select?* from kevin.haha;
+----+-----------+
|?id?| name????? |
+----+-----------+
|? 1 | wangshibo |
|? 2 | guohuihui |
|? 3 | yangyang? |
|? 4 | shikui??? |
| 11 | beijing?? |
| 12 | shanghai? |
| 13 | anhui???? |
+----+-----------+
7 rows?in?set?(0.00 sec)
mysql> delete from kevin.haha where?id>10;
Query OK, 3 rows affected (0.06 sec)
如上, 發(fā)現(xiàn)在之前的主庫MGR-node1節(jié)點掛掉后, MGR-node2節(jié)點可以進行寫操作了, 說明此時已經(jīng)選舉MGR-node2節(jié)點為新的主節(jié)點了
那么,MGR-node3節(jié)點還是從節(jié)點, 只能讀不能寫
[root@MGR-node3 ~]# mysql -p123456
..............
mysql>?select?* from kevin.haha;
+----+-----------+
|?id?| name????? |
+----+-----------+
|? 1 | wangshibo |
|? 2 | guohuihui |
|? 3 | yangyang? |
|? 4 | shikui??? |
+----+-----------+
4 rows?in?set?(0.00 sec)
mysql> insert into kevin.haha values(11,"beijing"),(12,"shanghai"),(13,"anhui");
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
然后再恢復(fù)MGR-node1節(jié)點, 恢復(fù)后, 需要手動激活下該節(jié)點的組復(fù)制功能
[root@MGR-node1 ~]# systemctl start mysqld
[root@MGR-node1 ~]# mysql -p123456
...............
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.15 sec)
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)
mysql>?select?* from kevin.haha;
+----+-----------+
|?id?| name????? |
+----+-----------+
|? 1 | wangshibo |
|? 2 | guohuihui |
|? 3 | yangyang? |
|? 4 | shikui??? |
+----+-----------+
4 rows?in?set?(0.00 sec)
mysql> insert into kevin.haha values(11,"beijing"),(12,"shanghai"),(13,"anhui");
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
發(fā)現(xiàn)MGR-node1節(jié)點恢復(fù)后, 則變?yōu)榱藦膸旃?jié)點, 只能讀不能寫.
如果從節(jié)點掛了, 恢復(fù)后, 只需要手動激活下該節(jié)點的組復(fù)制功能("START GROUP_REPLICATION;"),
即可正常加入到MGR組復(fù)制集群內(nèi)并自動同步其他節(jié)點數(shù)據(jù).
=============================================================
2)? 多主模式
?如果某個節(jié)點掛了, 則其他的節(jié)點繼續(xù)進行同步.
?當(dāng)故障節(jié)點恢復(fù)后, 只需要手動激活下該節(jié)點的組復(fù)制功能("START GROUP_REPLICATION;"),
?即可正常加入到MGR組復(fù)制集群內(nèi)并自動同步其他節(jié)點數(shù)據(jù).
