MySQL 组复制实现了基于复制协议的多主更新(单主模式)。
复制组由多个 server成员构成，并且组中的每个 server 成员可以独立地执行事务。但所有读写（RW）事务只有在冲突检测成功后才会提交。只读（RO）事务不需要在冲突检测，可以立即提交。
对于任何 RW 事务，提交操作并不是由始发 server 单向决定的，而是由组来决定是否提交。准确地说，在始发 server 上，当事务准备好提交时，该 server 会广播写入值（已改变的行）和对应的写入集（已更新的行的唯一标识符）。然后会为该事务建立一个全局的顺序。最终，这意味着所有 server 成员以相同的顺序接收同一组事务。因此，所有 server 成员以相同的顺序应用相同的更改，以确保组内一致。
组复制使您能够根据在一组 server 中复制系统的状态来创建具有冗余的容错系统。因此，只要它不是全部或多数 server 发生故障，即使有一些 server 故障，系统仍然可用，最多只是性能和可伸缩性降低，但它仍然可用。server 故障是孤立并且独立的。它们由组成员服务来监控，组成员服务依赖于分布式故障检测系统，其能够在任何 server 自愿地或由于意外停止而离开组时发出信号。
他们是由一个分布式恢复程序来确保当有 server 加入组时，它们会自动更新组信息到最新。并且多主更新确保了即使在单个服务器故障的情况下也不会阻止更新，不必进行 server故障转移。因此，MySQL 组复制保证数据库服务持续可用。
值得注意的一点是，尽管数据库服务可用，但当有一个 server 崩溃时，连接到它的客户端必须定向或故障转移到不同的 server。 这不是组复制要解决的问题；可以用mysql-router 或者其他中间件,比如mycat、altas等。
架构图如下
限制
表需要有主键
采用GTID+binlog的方式进行复制
复制事件校验 binlog_checksum=none
Gap Locks不可用
多主模式下限制
隔离级别
官网建议使用READ COMMITTED级别，除非应用程序依赖于REPLEATABLE READ，RC模式下没有GAP LOCK，比较好支持Innodb本身的冲突检测机制何组复制的内部分布式检测机制一起协同工作。不支持SERIALIZABLE隔离级别
外键
不建议使用级联外键，如果使用必须配置group_replication_enforce_update_everywhere_checks=ON
DDL操作
多主不支持同一对象不同实例的并发的DDL+DML混合操作 ，MySQL5.7上的DDL不是原子操作无法回滚，因此group replication没有对DDL做冲突检测，可能导致数据不一致；应用或中间件要能够把所有的DDL语句转到同一台MySQL上去执行。
本次测试环境
192.168.20.201 redis01
192.168.20.202 redis02
192.168.20.203 redis03
编辑my.cnf
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
#REP
server-id=1
log-bin=mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository =TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
binlog_format=ROW
#Group Replication Settings
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name=
"f27d825f-792f-11e8-b745-0800272bcfc4"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address=
"192.168.20.201:24901"
loose-group_replication_group_seeds=
"192.168.20.201:24901,192.168.20.202:24902,192.168.20.203:24903"
loose-group_replication_bootstrap_group=off
每个mysql的service-id不一样 地址也需要更改下，其他全部都一样
|
重启redis01主机上的mysql让配置生效
[root@redis01 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
备份redis01
innobackupex -uxbackup -u root -p ocm123 --no-timestamp /data/backup/
在redis02、redis03上恢复数据
innobackupex --copy-back /data/backup
redis01当作主机点 first node
set global validate_password_policy=LOW;
set global validate_password_length=4;
create user 'repl'@'192.%' identified by 'ocm123';
GRANT replication slave ON *.* to repl@'192.%' IDENTIFIED BY 'ocm123';
flush privileges;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='ocm123' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
reset master;
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=off;
查看日志，redis01作为主成员
View Code
|
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
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
|
在redis02上加组之前创建表
CREATE TABLE ht.tb(id
int
primary key, name varchar(16));
INSERT INTO ht.tb VALUES(1,
'zhou'
),(2,
'430'
),(3,
'YYF'
),(4,
'ChuaN'
),(5,
'Faith'
);
在redis02、redis03上
set
global validate_password_policy=LOW;
set
global validate_password_length=4;
create user
'repl'
@
'192.%'
identified
by
'ocm123'
;
GRANT replication slave ON *.* to repl@
'192.%'
IDENTIFIED BY
'ocm123'
;
flush privileges;
CHANGE MASTER TO MASTER_USER=
'repl'
, MASTER_PASSWORD=
'ocm123'
FOR CHANNEL
'group_replication_recovery'
;
INSTALL PLUGIN group_replication SONAME
'group_replication.so'
;
set
global group_replication_allow_local_disjoint_gtids_join=ON;
reset slave;
START GROUP_REPLICATION;
用xtrabackup做备份恢复，即使first节点没有新数据插入也会报This member has more executed transactions than those present
in
the
group
错误加不进去组；测试加组前在redis01上创建表及插入数据也同步过去；
在redis03上加组之前在redis01上继续插入数据
INSERT INTO ht.tb VALUES(6,
'zhou'
),(7,
'430'
),(8,
'YYF'
),(9,
'ChuaN'
),(10,
'Faith'
);
加组前在redis01上创建表及插入数据也同步过去
mysql>
select
*
from
ht.tb;
+----+-------+
| id | name |
+----+-------+
| 1 | zhou |
| 2 | 430 |
| 3 | YYF |
| 4 | ChuaN |
| 5 | Faith |
| 6 | zhou |
| 7 | 430 |
| 8 | YYF |
| 9 | ChuaN |
| 10 | Faith |
+----+-------+
10 rows
in
set
(0.00 sec)
查看组成员
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 11c530de-81e0-11e8-9fbe-0800276bf78a | redis03 | 3306 | ONLINE |
| group_replication_applier | 13339747-81e0-11e8-8ef2-080027e68d01 | redis02 | 3306 | ONLINE |
| group_replication_applier | 1384a263-81e0-11e8-b88c-080027c8ffe9 | redis01 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows
in
set
(0.00 sec)
查看那个可以是primary
mysql> SHOW STATUS LIKE
'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 1384a263-81e0-11e8-b88c-080027c8ffe9 |
+----------------------------------+--------------------------------------+
1 row
in
set
(0.00 sec)
mysql> SELECT a.member_host FROM performance_schema.replication_group_members a INNER JOIN performance_schema.global_status b
where a.member_id=b.variable_value;
+-------------+
| member_host |
+-------------+
| redis01 |
+-------------+
1 row
in
set
(0.00 sec)
查看恢复状态
mysql> show slave status
for channel 'group_replication_recovery'
\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: <NULL>
Master_User: repl
Master_Port: 0
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: redis02-relay-bin-group_replication_recovery.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 497
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 1
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 13339747-81e0-11e8-8ef2-080027e68d01:1-3,
3281175c-7a55-11e8-9cc7-080027c8ffe9:1-90,
8b99bac3-81d2-11e8-b85d-080027c8ffe9:1-7
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: group_replication_recovery
Master_TLS_Version:
1 row
in
set
(0.00 sec)
查看事件
mysql> SHOW BINLOG EVENTS;
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.22-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 1 | 150 | |
| mysql-bin.000001 | 150 | Gtid | 1 | 211 | SET @@SESSION.GTID_NEXT=
'8b99bac3-81d2-11e8-b85d-080027c8ffe9:1'
|
| mysql-bin.000001 | 211 | Query | 1 | 270 | BEGIN |
| mysql-bin.000001 | 270 | View_change | 1 | 369 | view_id=15309661790389352:1 |
| mysql-bin.000001 | 369 | Query | 1 | 434 | COMMIT |
| mysql-bin.000001 | 434 | Gtid | 1 | 495 | SET @@SESSION.GTID_NEXT=
'8b99bac3-81d2-11e8-b85d-080027c8ffe9:2'
|
| mysql-bin.000001 | 495 | Query | 1 | 554 | BEGIN |
| mysql-bin.000001 | 554 | View_change | 1 | 693 | view_id=15309661790389352:2 |
| mysql-bin.000001 | 693 | Query | 1 | 758 | COMMIT |
| mysql-bin.000001 | 758 | Gtid | 1 | 819 | SET @@SESSION.GTID_NEXT=
'8b99bac3-81d2-11e8-b85d-080027c8ffe9:3'
|
| mysql-bin.000001 | 819 | Query | 1 | 941 | use `ht`; CREATE TABLE ht.tb(id
int
primary key, name varchar(16)) |
| mysql-bin.000001 | 941 | Gtid | 1 | 1002 | SET @@SESSION.GTID_NEXT=
'8b99bac3-81d2-11e8-b85d-080027c8ffe9:4'
|
| mysql-bin.000001 | 1002 | Query | 1 | 1068 | BEGIN |
| mysql-bin.000001 | 1068 | Table_map | 1 | 1110 | table_id: 111 (ht.tb) |
| mysql-bin.000001 | 1110 | Write_rows | 1 | 1191 | table_id: 111 flags: STMT_END_F |
| mysql-bin.000001 | 1191 | Xid | 1 | 1218 | COMMIT
/* xid=54 */
|
| mysql-bin.000001 | 1218 | Gtid | 1 | 1279 | SET @@SESSION.GTID_NEXT=
'8b99bac3-81d2-11e8-b85d-080027c8ffe9:5'
|
| mysql-bin.000001 | 1279 | Query | 1 | 1338 | BEGIN |
| mysql-bin.000001 | 1338 | View_change | 1 | 1477 | view_id=15309661790389352:4 |
| mysql-bin.000001 | 1477 | Query | 1 | 1542 | COMMIT |
| mysql-bin.000001 | 1542 | Gtid | 1 | 1603 | SET @@SESSION.GTID_NEXT=
'8b99bac3-81d2-11e8-b85d-080027c8ffe9:6'
|
| mysql-bin.000001 | 1603 | Query | 1 | 1669 | BEGIN |
| mysql-bin.000001 | 1669 | Table_map | 1 | 1711 | table_id: 111 (ht.tb) |
| mysql-bin.000001 | 1711 | Write_rows | 1 | 1792 | table_id: 111 flags: STMT_END_F |
| mysql-bin.000001 | 1792 | Xid | 1 | 1819 | COMMIT
/* xid=88 */
|
| mysql-bin.000001 | 1819 | Gtid | 1 | 1880 | SET @@SESSION.GTID_NEXT=
'8b99bac3-81d2-11e8-b85d-080027c8ffe9:7'
|
| mysql-bin.000001 | 1880 | Query | 1 | 1939 | BEGIN |
| mysql-bin.000001 | 1939 | View_change | 1 | 2078 | view_id=15309661790389352:5 |
| mysql-bin.000001 | 2078 | Query | 1 | 2143 | COMMIT |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
到此1主2从搭建成功
多主模式
redis02 redis03 停止组复制
STOP GROUP_REPLICATION;
redis01上执行
STOP GROUP_REPLICATION;
SET GLOBAL group_replication_single_primary_mode=FALSE;
SET GLOBAL group_replication_enforce_update_everywhere_checks=TRUE;
SET GLOBAL group_replication_bootstrap_group=
on
;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=off;
在redis02 redis03 启动组复制
SET GLOBAL group_replication_single_primary_mode=FALSE;
SET GLOBAL group_replication_enforce_update_everywhere_checks=TRUE;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=off;
检查组成员及primary
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 11c530de-81e0-11e8-9fbe-0800276bf78a | redis03 | 3306 | ONLINE |
| group_replication_applier | 13339747-81e0-11e8-8ef2-080027e68d01 | redis02 | 3306 | ONLINE |
| group_replication_applier | 1384a263-81e0-11e8-b88c-080027c8ffe9 | redis01 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows
in
set
(0.00 sec)
mysql> SHOW STATUS LIKE
'group_replication_primary_member'
;
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| group_replication_primary_member | |
+----------------------------------+-------+
1 row
in
set
(0.00 sec)
3个节点都可以读写，多主模式搭建完
mysql> show global variables like
'super%'
;
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| super_read_only | OFF |
+-----------------+-------+
1 row
in
set
(0.00 sec)
在redis02上继续插入数据
INSERT INTO ht.tb VALUES(11,
'zhou'
),(12,
'430'
),(13,
'YYF'
),(14,
'ChuaN'
),(15,
'Faith'
);
在redis01上查询
root@redis01>
select
*
from
ht.tb;
+----+-------+
| id | name |
+----+-------+
| 1 | zhou |
| 2 | 430 |
| 3 | YYF |
| 4 | ChuaN |
| 5 | Faith |
| 6 | zhou |
| 7 | 430 |
| 8 | YYF |
| 9 | ChuaN |
| 10 | Faith |
| 11 | zhou |
| 12 | 430 |
| 13 | YYF |
| 14 | ChuaN |
| 15 | Faith |
+----+-------+
15 rows
in
set
(0.00 sec)
多主转为单注
redis02 redis03 停止组复制
STOP GROUP_REPLICATION;
redis01上执行
STOP GROUP_REPLICATION;
SET GLOBAL group_replication_enforce_update_everywhere_checks=TRUE;
SET GLOBAL group_replication_bootstrap_group=on;
SET GLOBAL group_replication_single_primary_mode=TRUE;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=off;
在redis02 redis03 启动组复制
set global group_replication_allow_local_disjoint_gtids_join=ON;
SET GLOBAL group_replication_single_primary_mode=off;
SET GLOBAL group_replication_enforce_update_everywhere_checks=TRUE;
START GROUP_REPLICATION;
SET GLOBAL super_read_only=on;
|
在安装过程当中遇到的错误
1 2018-07-05T10:08:31.700840Z 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: a7ca1853-7040-11e8-856a-08002771e31b:1-12, 2 f0019921-6d42-11e8-bd74-0800272bcfc4:1-3 > Group transactions: 0d01442d-8038-11e8-9d23-0800272bcfc4:1-3, 3 aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-3, 4 f0019921-6d42-11e8-bd74-0800272bcfc4:1-16, 5 f27d825f-792f-11e8-b745-0800272bcfc4:1-7' 6 2018-07-05T10:08:31.700922Z 0 [ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.' 7 2018-07-05T10:08:31.700928Z 0 [Note] Plugin group_replication reported: 'To force this member into the group you can use the group_replication_allow_local_disjoint_gtids_join option'
设置参数强制跳过
set global group_replication_allow_local_disjoint_gtids_join=ON;
Allow the current server to join the group even if it has transactions not present in the group.
错误日志
1 2018-07-05T10:48:19.726647Z 17 [ERROR] Slave SQL for channel 'group_replication_applier': Slave failed to initialize relay log info structure from the repository, Error_code: 1872
2 2018-07-05T10:48:19.726672Z 17 [ERROR] Plugin group_replication reported: 'Error while starting the group replication applier thread'
3 2018-07-05T10:48:19.726736Z 17 [Note] Plugin group_replication reported: 'The group replication applier thread was killed'
参考metalink解决
Cannot Configure Replication and RESET SLAVE ALL Does Not Work: "ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository" (文档 ID 2203190.1)
MySQL Innodb Cluster Node Failed To Start With Error: "[ERROR] Slave SQL for channel 'group_replication_applier': Slave failed to initialize relay log info structure from the repository, Error_code: 1872" (文档 ID 2386403.1)
2128
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
