Mgr--mysql Multi-Master mode for group replication

group replication can run in two modes.

1. In single-master mode, group replication has the auto-select Master feature, and only one server member accepts updates at a time.
2. In multi-master mode, all server members can accept updates at the same time.

group replication differs from asynchronous master-slave replication.

1. traditional MySQL master-slave replication is the SQL statement that executes and commits transactions on the primary node and then sends them asynchronously to the copy from the node, the row replicates, which is a shared-nothing system, by default all servers Members have a complete copy of the data.

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/8F/8C/wKioL1jktgWx5ScuAAEGdpm4tMo523.png "title=" Screenshot from 2017-04-05 15-29-17.png "style=" Float:none; "alt=" Wkiol1jktgwx5scuaaegdpm4tmo523.png "/>

2. Semi-synchronous replication , which adds a synchronization step to the protocol. This means that the primary node needs to wait from the node to confirm that it has received the transaction at the time of submission. Only then can the master node continue with the commit operation. 

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/8F/8E/wKiom1jktgWDoZH8AAEfDzCqi7k234.png "title=" Screenshot from 2017-04-05 15-30-01.png "style=" Float:none; "alt=" Wkiom1jktgwdozh8aaefdzcqi7k234.png "/>

3.MySQL group replication implements a multi-master update based on the replication protocol.
1) The replication group consists of multiple server members, and each server member in the group can perform the transaction independently. However, all read-write (RW) transactions are committed only after the conflict detection is successful. Read-only (RO) transactions do not need to be detected in conflict and can be submitted immediately.

2) In other words, for any RW transaction, the commit operation is not determined by the originating server one-way, but is determined by the group to commit. To be precise, on the originating server, when the transaction is ready to commit, the server broadcasts the write value (the changed row) and the corresponding write set (the unique identifier of the updated row). A global order is then established for the transaction. Ultimately, this means that all server members receive the same set of transactions in the same order. Therefore, all server members apply the same changes in the same order to ensure consistency within the group.

3) Group replication enables you to create a fault-tolerant system with redundancy based on the state of the replication system in a group of servers. Therefore, as long as it is not all or most servers fail, even if there are some server failures, the system is still available, up to only performance and scalability is reduced, but it is still available. Server failures are isolated and independent. They are monitored by group member Services, which rely on distributed fault detection systems to signal when any server leaves the group voluntarily or due to an unexpected stop.

4) They are a distributed recovery program that ensures that when a server joins a group, they automatically update the group information to the latest. and a multi-master update ensures that updates are not blocked even in the event of a single server failure, without server failover. Therefore, MySQL group replication ensures that the database service is continuously available.

5) It is important to note that, although the database service is available, when there is a server crash, the client that connects to it must be directed or failed over to a different server.
This is not a problem for group replication to resolve. Connectors, load balancers, routers or other forms of middleware are better suited to handle this problem.
In summary, MySQL group replication provides high availability, high resiliency, and reliable MySQL services. 

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/8F/8C/wKioL1jktgaB4I5sAAFeq364XR8050.png "title=" Screenshot from 2017-04-05 15-50-58.png "style=" Float:none; "alt=" Wkiol1jktgab4i5saafeq364xr8050.png "/>

as shown below, the MySQL group replicates the multi-master mode

server* (33,44,55)

Vim/etc/my.cnf

24server_id=3
Gtid_mode=on
Enforce_gtid_consistency=on
master_info_repository=TABLE 
relay_log_info_repository=TABLE
#此选项可以写FILE (plaintext stored in relay-log.info unsafe, recommended to write table, stored in mysql.slave_relay_log_info)
Binlog_checksum=none #关闭binlog校验
Log_slave_updates=on
#当server为slave时, to record data changes into its own binary log, in other words, whether to let his slave synchronize their data.
Log_bin=binlog
Binlog_format=row #组复制依赖基于行的复制格式
33
Transaction_write_set_extraction = XXHASH64
#以便在server收集写集合的同时将其记录到二进制日志. The Write collection is based on the primary key for each row, and is the unique identity after the row is changed to detect the conflict.
35group_replication_start_on_boot = OFF#同下
36Group_replication_bootstrap_group = OFF
#为了避免每次启动自动引导具有相同名称的第二个组, so set to off.
PNS Group_replication_group_name = "b6ddfda0-d8bc-4272-a58f-4ea75acbbc79" #组的名字可以随便起, but cannot use the host's Gtid
group_replication_local_address = ' 172.25.88.33:23306 ' #写自己主机所在IP
group_replication_group_seeds = ' 172.25.88.33:23306,172.25.88.44:23306,172.25.88.55:2330 '
#41, 42 lines is the parameter that turns on multi-master mode
Group_replication_single_primary_mode=false
Group_replication_enforce_update_everywhere_checks=true

server*:

Each host in the group needs to install the group replication plug-in first.
Mysql>install PLUGIN group_replication SONAME ' group_replication.so ';

Server33:

Mysql>set GLOBAL Group_replication_bootstrap_group=on;
#这句只有server33, executes the first time the boot group is executed.
mysql> CREATE USER [email protected] '% ';
Mysql> GRANT REPLICATION SLAVE on * * to [email protected] '% ' identified by ' repl ';
Query OK, 0 rows affected, 1 Warning (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.27 sec)

Mysql> set 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,172.25.88.0/24 ";
Query OK, 0 rows Affected (0.00 sec)

Mysql> START group_replication; Query OK, 0 rows affected (1.32 sec)

Mysql> SELECT * from Performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------+-------------+------- -------+
| Channel_name | member_id | Member_host | Member_port | Member_state |
+---------------------------+--------------------------------------+---------------------+-------------+------- -------+
| Group_replication_applier | eb724b27-19b2-11e7-8c21-525400cef621 |        server33.lalala.com | 3306 | ONLINE |
+---------------------------+--------------------------------------+---------------------+-------------+------- -------+
1 row in Set (0.00 sec)

Configure recovery credentials for a group_replication_recovery channel
server44,55:

mysql> change MASTER to master_user= ' repl ', master_password= ' repl ' for CHANNEL ' group_replication_recovery ';
Query OK, 0 rows affected, 2 warnings (0.56 sec)

Mysql> set 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,172.25.88.0/24 ";
Query OK, 0 rows affected (0.01 sec)

Mysql> START group_replication;
Query OK, 0 rows affected (4.02 sec)

Mysql> SELECT * from Performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------+-------------+------- -------+
| Channel_name | member_id | Member_host | Member_port | Member_state |
+---------------------------+--------------------------------------+---------------------+-------------+------- -------+
| Group_replication_applier | 8d5b8fa6-19a1-11e7-9641-52540042c9d3 |        server44.lalala.com | 3306 | ONLINE |
| Group_replication_applier | c5070c7c-19a3-11e7-9c1d-5254008cd713 |        server55.lalala.com | 3306 | ONLINE |
| Group_replication_applier | eb724b27-19b2-11e7-8c21-525400cef621 |        server33.lalala.com | 3306 | ONLINE |
+---------------------------+--------------------------------------+---------------------+-------------+------- -------+
3 Rows in Set (0.00 sec)

InspectionServer33:

mysql> create database test; 
 query ok, 1 row affected  (0.15 sec) 
 
 Mysql> use test; 
 Database changed 
 mysql>  create table t1  (c1 int primary  Key, c2 text not null); 
 query ok, 0 rows affected  (0.94 sec) 
 
 Mysql>  insert  INTO t1 VALUES  (1,  ' Lalala '); 
 query ok, 1 row affected  (0.46 sec) 
 
 mysql> select *  from t1; 
 +----+--------+ 
 | c1 | c2     | 
 +----+--------+ 
 |  1 | lalala | 
 +----+--------+ 
 1 row in set  (0.00 sec) 
 

In server44,55 can see the group copy, synchronized data

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/8F/8C/wKioL1jktgPhjNjmAADFMsG7IYM718.png "style=" float : none; "title=" screenshot from 2017-04-05 14-52-17.png "alt=" Wkiol1jktgphjnjmaadfmsg7iym718.png "/>

Group members can be seen in Performance_schema

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/8F/8E/wKiom1jktgSC_WujAACS_6Fw0Yw206.png "style=" float : none; "title=" screenshot from 2017-04-05 15-11-03.png "alt=" Wkiom1jktgsc_wujaacs_6fw0yw206.png "/>

Wrong Row

no whitelist segment is set: You need to add your own network segment.

Error: ' [GCS] the member is leaving a group without being on one. '

FIX: Set 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,172.25.88.0/ 24 "; START group_replication;

This article from "12049878" blog, declined reprint!

Mgr--mysql Multi-Master mode for group replication