Shulou(Shulou.com)06/01 Report--

This article mainly explains the advantages of MySQL multi-group replication and the difference between single-master mode and multi-master mode. Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the advantages of MySQL multi-group replication and the difference between single-master mode and multi-master mode.

"MySQL group replication"

Group replication is an open source plug-in officially developed by MySQL and a tool for implementing MySQL highly available clusters. The first version of GA is officially released in MySQL5.7.17; to use group replication, simply download MySQL5.7.17 and later versions from the official website.

After the release of group replication, there are three ways to implement a highly available cluster for MySQL:

①: asynchronous replication

②: semi-synchronous replication

③: group replication

-Note:

Asynchronous replication is the earliest and easiest highly available method to implement. Compared with asynchronous replication, semi-synchronous replication improves the reliability of MySQL cluster. Group replication is the development direction of MySQL replication in the future. Compared with the former two, it is not only more reliable, but also greatly improved in ease of use.

1. The concept of group:

There is a concept of group in the group replication plug-in. The MySQL server connected by the group replication plug-in is a highly available group, and the MySQL server in the group is called a member. The concept of groups runs through the use and internal implementation of group replication. Group replication integrates the group management service and realizes the automatic management of many members of the group, which makes the use and management of group replication very simple.

Users can manage group replication groups in three ways, as follows:

①: create a group: initialize the group when the first member of the group starts

②: join the group: add the MySQL server to the group replication group in a village

③: leave the group: remove a MySQL server from a group replication group

-when the group is initialized, the first member of the group automatically becomes a master, and the new member automatically copies data from the master in 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 and needs to be switched, after selecting a new master, the whole switching process will be completed automatically, so you don't have to use the command to complete the handover manually like the master and slave.

2. Multi-master replication:

Group replication supports one-master and multi-slave replication like asynchronous and semi-synchronous replication. Group replication is called single master replication. In addition, group replication provides a more advanced replication technology called multi-master replication. In the multi-master mode, the read and write services provided by all members at the same time are master, and the data will be replicated automatically among each other. This is a true sense of multi-master concurrent replication, where users can update data on a single MySQL, just as on multiple concurrent members. The group replication plug-in can synchronize the update operations of these concurrent transactions to each member and keep their data consistent.

2.1. Advantages of multi-master replication:

①: when a member fails, only part of the connection will fail, and the impact on the application will be less.

②: when you need to shut down a MySQL server, you can smoothly transfer the connection on it to other members and then close the member without causing instant disruption to the application.

③: multi-master mode has good performance and good carrying capacity for instantaneous high concurrency

3. Group replication uses paxos protocol when transmitting data.

Paxos protocol ensures the consistency and atomicity of data transmission. Group replication constructs a distributed stateful replication mechanism based on paxos protocol, which is the core technology to realize multi-master replication. This technology brings three main advantages to group replication, as follows:

①: there is no brain fissure in group replication.

②: group replication has good redundancy, which ensures that binlog event is copied to at least half of the members. As long as no more than half of the members are down at the same time, it will not cause data loss.

③: group replication also ensures that as long as the binlog event is not transferred to more than half of the members, the local members will not write the binlog event of the transaction to the binlog file and commit the transaction, thus ensuring that there will be no data that does not exist on the online members of the group on the server that is down. Therefore, after the down server is restarted, it no longer needs special processing to join the group.

4. Group replication service mode:

When providing services to the outside world, the group replication group has two service modes: single master mode and multi-master mode.

4.1. Single main mode:

In single main mode, only one member provides update service, and the other members only provide query service. Members that provide update services are called master members, and those who only provide query services are called slave members. Group replication's single-master mode is an alternative to asynchronous and semi-synchronous replication. The characteristics of the single master replication mode are as follows:

①: automatic selection and switching of master members:

In single master mode, the members of the group will automatically elect the principal member. During initialization, the initialized member automatically elects the master member, and the other members are called slave members. When the primary member fails, a new primary member is selected from the other members of the group. The method of selection is to sort the UUID of all online members, and then select the smallest member of UUID as the primary member.

You can use the command to view the UUID of the primary member on any member's server:

Show global status like "group_replication_primary_member"; or select * from performance_schema.global_status where variable_name='group_replication_primary_member'

②: automatic switching of read-write mode:

When a member joins a group, the group replication plug-in automatically changes MySQL to read-only mode, and automatically switches back to read-write mode only when the primary member is selected. 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 master member fails, the new master member is automatically selected in the group, and the replication can be carried out normally. Therefore, the failover within the group is fully automated and does not require user intervention.

4.2. Multi-master mode:

In multi-master mode, all members of the group provide query and update services at the same time, and there is no distinction between master and slave, and the members are completely equal. The client connects to any member and can read and write as if it were operating the same MySQL server

①: processing of self-increment segment:

When using multi-master mode, you need to set autoincrement-related parameters to ensure that the self-increment field produces a different value on each member. Group replication provides two configuration methods, which are as follows:

"directly configure the system variable of MySQL": set global auto_increment_offset=N; set global auto_increment_increment=N

"configure through the group replication plug-in": set group_replication_auto_increment_increment=N; (default is 7, which is generally not modified)

Note: in practice, it is best to use self-increment such as 1 auto_increment_increment 2 and 3, if not, you need to configure the self-increment variable of MySQL manually; (auto_increment_increment represents the size of the segment, and the size of the self-increment field depends on the number of members in the group replication group. The minimum auto_increment_increment should be equal to the number of members in the group replication group. If the size of the segment is equal to 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 isolation level of serialization is achieved by locking. In multi-master mode, the concurrent operation between multiple members cannot achieve the serial isolation level through locks.

Cascading operations of foreign keys are not supported

Parameter: group_replication_enforce_update_everywhere_checks=TRUE is used to control whether the above limit is detected. If this parameter is enabled, an error will be reported when these conditions are found.

③: concurrent execution of DDL statements:

DDL on MySQL5.7 is not an atomic operation that cannot be rolled back, so group replication does not do conflict detection for DDL. In other words, the DDL statement does not conflict with any other statement (including DML and DDL). If DDL and conflicting statements are executed on different members at the same time, it may cause errors or data inconsistencies

④: conditions for using multi-master mode:

Applications or middleware should be able to distribute write requests to multiple members

To be able to control the use of DDL, when DDL is to be executed, all write requests can be transferred to the same MySQL for execution.

Note: group replication sets the single main mode as the default mode. If you want to use multi-master mode, you need to set this variable to OFF before joining the group. The service mode cannot be switched online. You must make all the members of the group exit the group, then reinitialize the group to the service mode to be used, and then add other members.

Set global group_replication_single_primary_mode=OFF

5. Multithreaded execution of binlog event:

5.1. Group_replication_applier channel:

The group replication plug-in automatically creates a channel to execute the received binlog event, whose name is group_replication_applier. When joining the group, the group replication plug-in automatically starts the thread of execution 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 of this channel, as follows:

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 binlog event in group replication also supports multi-thread parallel execution. Configuration method:

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 of group replication is different from the logical_clock algorithm of asynchronous replication. The logical time in the group replication concurrency strategy is calculated based on the primary key, which is much better than that calculated by asynchronous replication based on locks.

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

①: if two things update the same row of data, they should be executed sequentially. Otherwise, they can be executed concurrently.

②: DDL cannot be executed concurrently with anything. It must wait for all transactions at its front door to finish before it can start execution. Later transactions must also wait for the execution of DDL to finish before they can start execution.

Note: in order to ensure that transactions in the same session are committed in the same order, when group replication starts parallel replication, the value of slave_preserve_commit_order must be set to ON.

At this point, I believe you have a deeper understanding of the advantages of MySQL multi-group replication and the difference between single-master mode and multi-master mode. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.