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
#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作为主成员
1 2018-07-07T10:25:14.889679Z 7 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"' 2 2018-07-07T10:25:14.889959Z 7 [Note] Plugin group_replication reported: '[GCS] Added automatically IP ranges 10.0.2.15/24,127.0.0.1/8,192.168.122.1/24,192.168.20.201/24 to the whitelist' 3 2018-07-07T10:25:14.890320Z 7 [Warning] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.' 4 2018-07-07T10:25:14.890421Z 7 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled' 5 2018-07-07T10:25:14.890465Z 7 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: "f27d825f-792f-11e8-b745-0800272bcfc4"; group_replication_local_address: "192.168.20.201:24901"; group_replication_group_seeds: "192.168.20.201:24901,192.168.20.202:24902,192.168.20.203:24903"; group_replication_bootstrap_group: true; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "AUTOMATIC"' 6 2018-07-07T10:25:14.890603Z 7 [Note] Plugin group_replication reported: '[GCS] Configured number of attempts to join: 0' 7 2018-07-07T10:25:14.890785Z 7 [Note] Plugin group_replication reported: '[GCS] Configured time between attempts to join: 5 seconds' 8 2018-07-07T10:25:14.890816Z 7 [Note] Plugin group_replication reported: 'Member configuration: member_id: 1; member_uuid: "3281175c-7a55-11e8-9cc7-080027c8ffe9"; single-primary mode: "true"; group_replication_auto_increment_increment: 7; ' 9 2018-07-07T10:25:14.922055Z 10 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''. 10 2018-07-07T10:25:14.954193Z 13 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './redis01-relay-bin-group_replication_applier.000001' position: 4 11 2018-07-07T10:25:14.954214Z 7 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!' 12 2018-07-07T10:25:14.954233Z 7 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7' 13 2018-07-07T10:25:14.954240Z 7 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 1' 14 2018-07-07T10:25:14.974409Z 0 [Note] Plugin group_replication reported: 'XCom protocol version: 3' 15 2018-07-07T10:25:14.974512Z 0 [Note] Plugin group_replication reported: 'XCom initialized and ready to accept incoming connections on port 24901' 16 2018-07-07T10:25:15.977185Z 16 [Note] Plugin group_replication reported: 'Only one server alive. Declaring this server as online within the replication group' 17 2018-07-07T10:25:15.978257Z 0 [Note] Plugin group_replication reported: 'Group membership changed to redis01:3306 on view 15309591159762880:1.' 18 2018-07-07T10:25:15.983526Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group' 19 2018-07-07T10:25:15.983793Z 0 [Note] Plugin group_replication reported: 'A new primary with address redis01:3306 was elected, enabling conflict detection until the new primary applies all relay logs.' 20 2018-07-07T10:25:15.983927Z 18 [Note] Plugin group_replication reported: 'This server is working as primary member.'
在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)
扫一扫
649
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
