MySQL Group Replication Data Security Assurance

This article comes from the Database kernel column

In the previous article, we introduced the implementation of MGR for data reliability, availability, and consistency. Simply put, MGR uses multiple replicas based on the Paxos protocol to achieve the reliability of the data, using the majority mechanism on multiple replicas to achieve availability. For consistency, the main point is that multi-master mode is based on the transaction version authentication mechanism to ensure the correctness of multi-node concurrency update.

This paper introduces the protection of data security in Mgr, the security refers to the MGR in the data will not be affected by foreign operations, resulting in different nodes of the data in an inconsistent state, because the cause of the inconsistency is external, unplanned, so classified as security category.

First, talk about the security of secondary node in single main mode. In this mode, Mgr sets the super_read_only of the secondary node to true to ensure that the secondary node cannot handle the write transaction. This is because, although the MGR external rendering is a single main mode, but the xcom layer of Paxos or more points can be written, that is secondary node Paxos can still as proposer. In this case, if super_read_only is not set to true, then transactions of different nodes may cause data state errors due to conflicts without the transaction authentication mechanism participating. Therefore, the secondary node super_read_only is set to True when the start group_replication operation is performed.

Shown in the figure is a node as a secondary to join MGR before and after the super_read_only change situation.

Second, in addition to the (Start Group_replication) Mgr Cluster will be set super_read_only, when exiting the MGR cluster will also set it, regardless of the active exit (stop Group_ replication) or an abnormal exit (such as a network partition). This is a preventative measure to prevent the data from being accidentally modified after the node exits the cluster. When a node joins a cluster, the cluster checks the data version information of the node, and if there is data in the data version of the node that the cluster does not have, the cluster rejects the node because it means that the node's data is inconsistent with the cluster data. As a result, MGR actively set its super_read_only to true when the node exits, and the user needs to manually set it to false for write operations, minimizing the node data being accidentally changed because of a false operation.

Figure shows the primary node exit Mgr before and after the super_read_only changes, you can find the exit read-only open, manually shut down read-only new database after re-join the cluster, prompting 3092 error. The error log is as follows:

Third, in addition to the above mentioned node to join or exit MGR need to set the correct super_read_only value, but also to ensure that the MGR node is not affected by the asynchronous copy channel. This can be divided into 4 different situations.

1. To prevent the single main mode secondary node from writing to the asynchronous copy channel data. This means that a node cannot be a slave node for both the secondary node of the MGR Cluster and Master-slave replication (generalized asynchronous replication, including semi-synchronous replication). This is because the secondary node plays back the transaction of the primary node, and if it simultaneously receives and replays the master node transaction of the other replication channel, this means that the node's data is inconsistent with other MGR nodes, which can cause a number of serious problems such as data collisions, which is obviously unacceptable.

Therefore, neither the slave node of the asynchronous replication can be used as a single main mode secondary node, nor can it start the asynchronous replication slave node on a single main mode secondary node;

2, different from the 1th, the single main mode of the primary node and the multi-master mode, the node can be asynchronous replication of the slave node. But before the node writes to the asynchronous copy channel data, it needs to check whether the data conforms to the MGR constraint, including must be the InnoDB table, need to explicitly define the primary key, no cascade foreign key, etc.

3, the above has mentioned that the node exits Mgr when the super_read_only is set to true, but this does not block the playback of transactions in Relay-log, if there is an asynchronous replication channel on that node, then there are reasons for continuous data updates. The updated data after exiting the cluster is not visible to the other nodes of the cluster, which is obviously unacceptable, so the asynchronous replication channel on the node will be stopped when it exits the MGR cluster.

4. Finally, if the MGR and asynchronous replication channels are automatically started after configuring mysqld restart, you need to ensure that the boot sequence is consistent. The principle is to ensure that the MGR should start before the asynchronous replication channel, the MGR node becomes the online state, and then the asynchronous replication channel is started. The main consideration is three points: A, first ensure MGR online, and then start asynchronous replication; B, if Mgr fails to start, then asynchronous replication must also return a failure; C, if automatic startup is configured on a single-master-mode secondary node, the asynchronous replication return failure is required to ensure that the MGR starts successfully.

Finally, the following innosql is introduced in this respect. Innosql adds a parameter group_replication_readonly_after_reconfig, which is used in conjunction with the group_replication_force_members provided by the official. When the MGR cluster causes only one node to be online because of an exception, the majority causes the node to block the commit of the write transaction, and the Group_replication_force_members parameter can be reconfigured to make the MGR cluster a new cluster with only that node. So that the write transaction can be performed normally. But because there is only one node in the cluster at this point, the data written is extremely unreliable.

For the 3-node Mgr Financial Edition instance of the RDS cloud service, the surviving nodes should only provide read services when the 2 nodes are offline. This is obviously inappropriate if the system becomes writable due to configuration group_replication_force_members. Group_replication_readonly_after_reconfig is to control the read and write behavior of the node after group_replication_force_members, if enabled, then the node becomes read-only after reconfiguration, The implementation is also achieved by setting Super_read_only to True.

netease Cloud Database rds is a stable and reliable, scalable online relational database service, currently supports MySQL engine, provides the basic version, high-availability version, financial version for different business scenarios of high-availability solutions, while providing multiple security measures, performance monitoring system, Professional database backup, recovery, and optimization solutions that enable you to focus on application development and business development.

This article comes from the Database kernel column. Issued by author Wen Zheng authorized

Original: MySQL Group replication Data Security Guarantee