MySQL 5.7 Group Replication

MySQL group replication implements a multi-master update based on the replication protocol (single master mode).

A replication group consists of multiple server members, and each server member in the group can perform transactions 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.

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.

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.

They are composed of 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.

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 that the group replicates to solve, can use Mysql-router or other middleware, such as Mycat, Altas and so on.

The architecture diagram is as follows

Limit

Table requires a primary key
Replicate in a gtid+binlog way
Replication Event Checksum Binlog_checksum=none
Gap Locks Not Available
Limit in multi-master mode
Isolation level
The official website recommends using the READ committed level, unless the application relies on repleatable READ,RC mode without Gap LOCK, which is better to support the conflict detection mechanism of INNODB itself. The internal distributed detection mechanism of the group replication works together. Serializable isolation level is not supported
FOREIGN key
It is not recommended to use a cascading foreign key if you must configure Group_replication_enforce_update_everywhere_checks=on
DDL operations
Multi-master does not support concurrent DDL+DML blending operations for different instances of the same object, DDL on MySQL5.7 cannot be rolled back, so group replication does not conflict detection of DDL and may result in inconsistent data , the application or middleware should be able to transfer all DDL statements to the same MySQL to execute.

This test environment

192.168.20.201 redis01
192.168.20.202 REDIS02
192.168.20.203 Redis03

Edit MY.CNF

MySQL 5.7 Group Replication