MySQL Group Replication Introduction

One, the group copy is a what ghost?

Start with MySQL replication technology, first of all, MySQL asynchronous replication

In asynchronous replication mode, the execution transaction on master generates Binlog,slave on the local relaylog by connecting the contents of the master Fetch Binlog, and then apply the corresponding transaction, resulting in Binlog on the slave server itself (by--lo The g-slave-update parameter is determined). The flowchart is as follows:

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M01/08/21/wKiom1ncx66hMuokAACG2H52LDo342.png-wh_500x0-wm_ 3-wmp_4-s_978777143.png "title=" image 1.png "alt=" Wkiom1ncx66hmuokaacg2h52ldo342.png-wh_50 "/>

Next is the semi-synchronous replication, the flowchart is as follows

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/A6/D3/wKioL1ncWOLgYbl3AACdXyu_EjQ091.png-wh_500x0-wm_ 3-wmp_4-s_72836638.png "title=" 2.png "alt=" Wkiol1ncwolgybl3aacdxyu_ejq091.png-wh_50 "/>

In asynchronous replication mode, if slave all goes down, there is a certain data security risk that transactions on master cannot be synchronized to slave.

Semi-synchronous replication solves the problem of data security risk, in the semi-synchronous environment requires at least one slave received the master Binlog and successfully written to the local relaylog, the transaction on Master can be successfully committed, so that the main library transaction commit speed will have a certain impact, Semi-synchronous has a neutralization between data security and database performance.

In the actual use of the process, you can configure the parameters (Rpl_semi_sync_master_timeout unit is milliseconds, the default is 10000, that is, 10s) set if the slave for how long no ACK return, synchronous mode is automatically modified by the semi-synchronous mode of asynchronous synchronization. (MySQL semi-synchronous works the same as the maximum protection mode of Oracle Dataguard)

Group replication single main mode and multi-master mode, MySQL replication technology only solves the problem of data synchronization, if the master outage means that the database administrator needs to intervene, the application system may need to modify the database connection address or restart to be implemented. (You can also use database middleware products to avoid problems with application database connectivity, such as products such as Mycat and Atlas.) Group replication is done at the database level, as long as most hosts in the cluster are available, the service is available, which means that 3 servers are clustered, allowing 1 of them to go down. The flow chart for group replication is as follows:

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M02/08/21/wKiom1ncyDfyt02-AAC85vX37TI026.png-wh_500x0-wm_ 3-wmp_4-s_3121589039.png "style=" Float:none; "title=" 3.png "alt=" Wkiom1ncydfyt02-aac85vx37ti026.png-wh_50 "/>

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M02/A6/D3/wKioL1ncWSqCRYmxAAFVucBImYw529.png-wh_500x0-wm_ 3-wmp_4-s_1027864957.png "style=" Float:none; "title=" 4.png "alt=" Wkiol1ncwsqcrymxaafvucbimyw529.png-wh_50 "/>

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M00/08/21/wKiom1ncyDjgRA8TAAFOIQfZiNg273.png-wh_500x0-wm_ 3-wmp_4-s_1865489381.png "style=" Float:none; "title=" 5.png "alt=" Wkiom1ncydjgra8taafoiqfzing273.png-wh_50 "/>

Features of group replication:

High consistency

Group replication technology based on native replication and Paxos protocol, and provided by plug-in, providing consistent data security assurance;

High level of fault tolerance

As long as not most of the nodes can continue to work, there is automatic detection mechanism, when the different nodes generate contention conflicts, will not appear error, according to first-come priority principle of processing, and built-in automatic brain crack protection mechanism;

High scalability

The addition and removal of the nodes are automatic, and when the new node joins, the state is automatically synchronized from the other nodes until the new node is consistent with the other nodes, and if a node is removed, the other nodes automatically update the group information and automatically maintain the new group information;

High flexibility

There is a single main mode and multi-master mode, the single main mode, will automatically select the master, all the update operations in the Lord;

In multi-master mode, all servers can handle update operations at the same time.

Second, what kind of application scenario is suitable for group replication?

1. Elastic Database Replication Environment

Group replication provides the flexibility to increase and decrease DB instances in a cluster

2. Highly Available database environment

Group replication allows a DB instance to be down, as long as most servers in the cluster are available, the entire database service is available

3. A database environment that replaces the traditional master-slave replication structure

Iii. What are the prerequisites for group replication?

1, only support InnoDB storage engine

2, each table need to have a primary key

3, only support IPv4 network environment

4. Requires high network bandwidth (usually gigabit intranet) and low network latency

The following parameters must be configured on the MySQL DB instance

1 、--log-bin=bin-log #记录mysql的binlog

2 、--log-slave-update #记录slave上进行apply relay-log binlog

3 、--Binlog-format=row #binlog的格式为行模式

4 、--Gtid-mode=on #开启gtid模式

5 、--master-info-repository=table #将master-info information recorded in MySQL table

6 、--relay-log-info-repository=table #将relay-log information recorded in MySQL table

7 、--transaction-write-set-extraction=xxhash64 #每个事务收集的wirte set and encode using xxhash64 hashing algorithm

8. Turn on multi-threaded replication

--slave-parallel-workers=n #N为具体的复制线程个数

--slave-preserve-commit-order=1 #slave上apply Relay-log Transaction Order submission

--slave-parallel-type=logical_clock #使用多线程复制

Iv. What are the limitations of group replication?

1. Replication Event checksums

Because of the code design, the current group replication cannot support Binlog's checksum, and if you want to use group replication, you need to configure Binlog-checksum=none

2, Gap Locks

The process of group replication verification does not support Gap lock, MySQL gap Lock is designed to solve the phantom reading problem

3, Table Locks and Named Locks

The process of group replication validation does not support table-level locks and named locks

4, SERIALIZABLE Isolation Level

Group replication does not support serial transaction levels

5. Concurrent DDL versus DML Operations

Multi-master mode for group replication does not support parallel DDL and DML operations

6. Foreign Keys with cascading Constraints

Multi-master mode for group replication does not support foreign keys with cascading constraint types

7, Very Large transactions

Group replication does not support large transactions

Reference:

Https://dev.mysql.com/doc/refman/5.7/en/group-replication.html

This article from "Chop Month" blog, declined reprint!

MySQL Group Replication Introduction