MySQL Group Replication Introduction

"MySQL Group Replication"

The group replication is an open source plugin developed by MySQL and is a tool for implementing MySQL's highly available clusters. The first GA version is officially released in MySQL5.7.17; To use group replication you only need to download MySQL5.7.17 and later versions from the official website.

After the release of group replication, there are 3 ways to implement MySQL's highly available cluster:

①: Asynchronous replication

②: Semi-synchronous replication

③:group replication

---Note:

Asynchronous replication is the oldest and simplest high-availability method. Semi-synchronous replication improves the reliability of the MySQL cluster compared to asynchronous replication. Group replication is the direction of MySQL replication in the future, compared with the previous two, not only the reliability is better, but also a great improvement in ease of use;

1, the concept of group:

The group replication plugin has a set of concepts, the MySQL server that is connected by the group replication plugin is a highly available group, and the MySQL server within the group is called a member. The concept of a group runs through the use and internal implementation of replication. Group replication is integrated with the team management Service, which realizes the automation management function of many members, which makes the use and management of group replication very simple.

The user has three operations for managing group replication groups, as follows:

①: Creating groups: When the first member of a group starts, the group needs to be initialized

②: Join the group: Add the MySQL server to the group replication in a village

③: Leave group: Removes a MySQL server from a group replication;

---When a group is initialized, the first member of the group automatically becomes master, and the newly joined member automatically copies the data from master on the group. These used channels are automatically controlled by the group replication plug-in and do not require user intervention. Especially when the MySQL server fails to switch, select the new master, the whole switching process will be completed automatically, do not have to use the command as master-slave to complete the switch;

2. Multi-master replication:

Group replication supports a master-and-slave replication like asynchronous, semi-synchronous replication, and group replication is called single-master replication. In addition, group replication provides a more advanced replication technology called Multi-master replication. In multi-master mode, all members provide both read and write services as master, and data replication occurs automatically between them. This is a really meaningful multi-master concurrent replication, where users can update data on multiple members concurrently, just as the data is updated on a MySQL. The group replication plug-in is able to synchronize the update operations of these concurrent transactions to each member, keeping their data consistent;

2.1. Advantages of multi-master replication:

①: When a member fails, it will only cause a part of the connection to fail, the impact on the application will be smaller;

②: When you need to close a MySQL server, you can smoothly transfer the connection on the other members after the closure of this member, will not cause an instantaneous interruption of the application;

③: The performance of multi-master mode is very good, and it has good carrying capacity for instantaneous high concurrency.

3, group replication in the transmission of data, the use of the Paxos protocol.

The Paxos protocol guarantees the consistency and atomicity of data transmission. Group replication constructs a distributed state replication mechanism based on the Paxos protocol, which is the core technology for multi-master replication. This technique brings 3 main benefits to group replication, as follows:

There is no ①:group replication in the brain.

The redundancy of the ②:group replication is good enough to ensure that the Binlog event is replicated to more than half of the members, as long as there are not more than half of the members at the same time, the data loss will not occur;

③:group replication also guarantees that as long as Binlog event is not transferred to more than half of the members, local members do not write the Binlog event of the transaction to the Binlog file and commit the transaction. This ensures that there is no data on the server in the group that does not exist on the online member. Therefore, after the outage server restarts, no special processing is required to join the group;

4, Group Replication Service mode:

Group replication when providing services to the outside, there are 2 modes of service: Single main mode multi-master mode

4.1. Single main mode:

Only one member of the single-master mode provides update services, and other members only provide query services. The member that provides the update service is called the primary member, and only the query service is called from member. The single main mode of the group replication is an alternative to asynchronous replication and semi-synchronous replication. The features of the single master copy mode are as follows:

①: Automatic selection and switching of Master members:

In single-master mode, the members of the group are automatically elected to the primary member. When initialized, the initialized member is automatically elected as the primary member, and the other member is called from the member. When the primary member fails, a new primary member is selected from the other members in the group. The chosen method is to sort all the online members ' UUID, then select the least-UUID member as the primary member;

You can use commands to view the UUID of the primary member on any one of the members ' servers:

Show global status Like "Group_replication_primary_member"; or select * from Performance_schema.global_status where variable_name= ' group_replication_primary_member ';

②: Automatic switching between read and write modes:

When a member joins a group, the group replication plugin automatically turns MySQL into read-only mode and automatically switches back to read-write mode only after it is selected as the primary member. The control of MySQL read-only mode is done through the following SQL statement:

Set global Super_read_only=1;

Set global super_read_only=0;

Note: When the primary member fails, the new primary member is automatically selected within the group, and replication is performed normally. Therefore, the failover in the group is fully automated and does not require user intervention;

4.2. Multi-Master mode :

In multi-master mode, all members of a group provide query and update services at the same time, and there are no master-slave points, and the members are completely equivalent. The client connects to any member and reads and writes as if it were operating on the same MySQL server;

①: The processing of the self-increment section:

When using multi-master mode, you need to set AutoIncrement related parameters to ensure that the self-increment field produces different values on each member. Group replication provides two configuration options, as follows:

"System variables for direct configuration of MySQL": Set global auto_increment_offset=n; Set global auto_increment_increment=n;

"Configuration via the Group replication plugin": Set group_replication_auto_increment_increment=n; (The default value is 7, usually not modified)

Note: In practice, it is best to use server_id, such as the self-increment, if not, you need to manually configure the MySQL self-increment variable, (auto_increment_increment represents the size of the segment, the size of the self-increment field depends on the group The number of members in the replication group. The minimum auto_increment_increment is equal to the number of members within the group replication. If the size of the segment equals the number of members in the group, all self-increment will be used)

②: Limitations of Multi-master mode:

Serial isolation levels are not supported. In a single MySQL server, the serialization isolation level is achieved by means of a lock. In multi-master mode, concurrent operations between multiple members cannot achieve serial isolation levels through locks;

Cascade Operations for foreign keys are not supported;

Parameters: Group_replication_enforce_update_everywhere_checks=true is used to control whether to do the above limitations of the detection, if this parameter is turned on, when these conditions are found to error;

③:DDL statement concurrency execution problem:

The DDL on the MySQL5.7 is not an atomic operation that cannot be rolled back, so group replication does not conflict detection for DDL. In other words, the DDL statement does not conflict with any other statement (including DML and DDL). If the DDL and conflicting statements are executed concurrently on different members, errors or inconsistent data may result;

④: Conditions for using multi-master mode:

Application or middleware to be able to distribute write requests to multiple members

To be able to control the use of DDL, when the DDL to execute, you can transfer all write requests to the same MySQL to execute;

Note: Group replication sets the single main mode to the default mode. If you want to use multi-master mode, you need to set this variable to off before joining the group. Service mode cannot be switched online, you must leave all members of the group out of the group, then reinitialize the group to use the service mode, and then add the other members in;

Set global Group_replication_single_primary_mode=off;

5, Binlog event multi-threaded execution:

5.1. Group_replication_applier Channel:

The group replication plugin automatically creates a channel to perform the received Binlog event, and the channel name is Group_replication_applier. When joined to a group, the group replication plugin automatically launches the execution thread of the Group_replication_applier channel. If the user needs to adjust the parameters of the Group_replication_applier execution thread,

You can also manually stop and start the execution thread for this channel, with the following command:

Start slave sql_thread for channel ' Group_replication_applier ';

Stop slave sql_thread for channel ' Group_replication_applier ';

5.2. Parallel execution based on primary key:

The execution of the Binlog event in group replication also supports multi-threaded parallel execution, configuration methods:

Set global slave_parallel_type= ' Logical_clock ';

Set global slave_parallel_workers=n;

Set global slave_preserve_commit_order=on;

---Note:

The parallel replication algorithm for group replication and the Logical_clock algorithm in asynchronous replication are not the same. The logical time in the group replication concurrency policy is calculated based on the primary key, which is much better than the concurrency of asynchronous replication based on the logic time of the lock calculation.

Concurrent replication based on primary keys has the following two features:

①: If two things update the same row of data, it is executed sequentially, otherwise, it can be executed concurrently;

②:DDL cannot be executed concurrently with anything, it must wait until all the transactions in its front door have been completed before execution can begin. The subsequent transaction must also wait for the DDL to execute before it can begin execution;

Note: In order to ensure that transactions in the same session are committed in the same order, group replication requires that the value of Slave_preserve_commit_order be set to ON when parallel replication is turned on;

This article is from the "stupid Child's DBA path" blog, please be sure to keep this source http://fengfeng688.blog.51cto.com/4896812/1941341

MySQL Group Replication Introduction