Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the method of replication architecture from node automatic failover in MySQL 8.0.23". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the method of replication architecture from node automatic failover in MySQL 8.0.23".

Fault tolerance of the number of MySQL Group Relication members

I believe you will be familiar with the above form. I often ask in the interview: "how many MGR with 4 nodes are bad at most?" Most people replied, "one bad at most, and two bad can't work with a brain fissure."

So let's take a look at the way MGR is handled. Is that the answer?

1) We have a 4-node MGR

Bury a question: this picture looks like Single mode, but the arrow is not one-way, is it wrong?

2) at this point, Second-04 suddenly goes down, so what will the MGR cluster look like?

At this point, the cluster state will be:

Each node exchanges its own information at a fixed time.

When the Second-04 node information is not received, other members will wait 5 seconds.

There must have been no message from the Second-04 during this period, so the health members thought the Second-04 was suspicious and marked the UNREACHABLE status.

Then the healthy member continues to wait according to the parameter: group_replication_member_expel_timeout (while Second-04 is still in UNREACHABLE state).

When the group_replication_member_expel_timeout time is exceeded, the healthy members expel the Second-04 node from the cluster.

So here's the point. Knock on the blackboard.

In Second-04, when not deported:

At this time, the cluster is (4 nodes-3 healthy-1 bad). If one node continues to be bad during this period, the cluster becomes (4 nodes-2 healthy-2 bad), the cluster does not meet the majority principle, and each node cannot be written (unless human intervention is required to specify the cluster member List).

In Second-04, after being expelled:

At this time, the cluster is (3 nodes-3 healthy-0 bad), and the 4-node cluster degenerates into a 3-node healthy cluster. At this time, the cluster can still continue to break one node and become (3 nodes-2 healthy-1 bad).

So whether a 4-node cluster can break one or two depends on which stage of the cluster processing process.

PS:

Let's talk about the problem just buried: this picture looks like Single mode, but the arrow is not one-way, is it wrong?

First of all, in Single mode, the Second node cannot be written by default, but only because the super-read-only of the Second node is turned on.

The Second node super-read-only = 0 second node can be written normally, and other nodes (Primary and other Second) can be synchronized, and the transmission is still based on the Paxos protocol.

Run a train: Second node reverse synchronization of other nodes, will not go through the conflict detection phase (the theoretical efficiency is higher than multi-write mode), there is no verification, we are interested to study.

II. Asynchronous Connection Failover

MySQL 8.0.22, launched asynchronous replication connection failover, many friends have posted articles to make an introduction, here I just briefly describe:

1) one master and one slave in the same computer room, and a separate slave node in the remote computer room

2) Master failure, changing Slave-01 to Master,Slave-02, unable to connect to the original Master

3) if "Asynchronous connection failover configuration" is configured for Slave-02, Slave-02 will automatically attempt to establish a replication relationship with the original Slave-01 (new Master) according to the predefined configuration after identifying the original Slave-01 failure:

This feature is so good that referencing tripartite tools (such as MHA's repairing master-slave relationships) can be replaced by MySQL native features.

But after I finished the test, I had a few doubts:

1. "Asynchronous" replication failover, does not support semi-synchronous architecture? Can not guarantee that the data will not be lost, or can not completely replace MHA ah?

A: it actually supports enhanced semi-synchronization.

two。 If you want to configure the failover Master List in advance, if the architecture of computer room An is changed, do you still have to maintain the nodes of computer room B?

A: yes.

3. If the computer room An is MGR, then the master of the MGR is abnormal, but the service is not off and can be accessed. Isn't node B connected all the time?

A: yes

Then, MySQL 8.0.23 was released, with an enhancement to this feature:

Slave can support MGR clusters, and can dynamically identify MGR members to establish Master-Slave relationships.

Finally, let's run a lap:

1) first of all, we have a 3-node MGR cluster, version 8.0.22 (asynchronous connection failover works on Slave's IO Thread, so Slave is version 8.0.23)

+-- + | now (6) | member_host | member_state | member _ role | VIEW_ID | +-+ | 2021-01-22 13:41:27. | 902251 | mysql-01 | ONLINE | SECONDARY | 16112906030396799pur9 | | 2021-01-22 131414 41vam27.902251 | mysql-02 | ONLINE | PRIMARY | 16112906030396799SECONDARY | | mysql-03 | ONLINE | SECONDARY | 161129060303967999 | + -+

2) then we specify the "failover list for Master connections" on the Slave on a separate Slave node

SELECT asynchronous_connection_failover_add_managed ('ch2',' GroupReplication', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1',' mysql-02', 3306,', 80,60) Briefly explain the parameter: ch2:chanel name GroupReplication: forced write dead parameter. Currently, MGR cluster aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:MGR group name (parameter group_replication_group_name) the priority of one of the mysql-02:MGR members 80:Primary node (0-100). If multiple masters have the same priority, nodes are randomly selected to act as master. The priority of 60:Second nodes (0-100) is basically prepared for Single mode.

3) specify replication channel information for Slave

CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='123456', SOURCE_HOST='mysql-02',SOURCE_PORT=3306,SOURCE_RETRY_COUNT=2,SOURCE_CONNECTION_AUTO_FAILOVER=1,SOURCE_AUTO_POSITION=1 For CHANNEL 'ch2'

4) start Slave and view the "transferable list of connections"

If io thread is not enabled, MGR members will not be automatically recognized. And copy the user

Rpl_user requires select permission to performance_schema on the MGR node

Start slave; SELECT * FROM performance_schema.replication_asynchronous_connection_failover +-+-+ | CHANNEL_NAME | HOST | PORT | NETWORK_NAMESPACE | WEIGHT | MANAGED_NAME | +-+-- + | ch2 | | mysql-01 | 3306 | | 60 | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1 | | ch2 | mysql-02 | 3306 | | 80 | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1 | ch2 | mysql-03 | 3306 | | 60 | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1 | +-| -+-+

5) then we will mysql-02 stop group_replication (not shut down the service)

Slave list automatically eliminates mysql-02 and reestablishes connections with other nodes-- mysql-02 (Primary): stop group_replication;-- Slave: SELECT * FROM performance_schema.replication_asynchronous_connection_failover +-+-+ | CHANNEL_NAME | HOST | PORT | NETWORK_NAMESPACE | WEIGHT | MANAGED_NAME | +-+-- + | ch2 | | mysql-01 | 3306 | | 80 | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1 | | ch2 | mysql-03 | 3306 | | 60 | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1 | +-| -+-- + show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: mysql-01 Master_User: rpl_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mybinlog.000003 Read_Master_Log_Pos: 4904 Relay_Log_File: Mysql-01-relay-bin-ch2.000065 Relay_Log_Pos: 439 Relay_Master_Log_File: mybinlog.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes...

At this point, the configuration is complete. Slave can automatically maintain this list as MGR nodes increase or decrease later. No other use cases will be posted.

PS:

If you want to manually switch the established Master node (Primary) of Slave to another node (Second), simply delete the "transferable list of replication connections" and re-adjust the Second priority back.

-- Delete configuration SELECT asynchronous_connection_failover_delete_managed ('ch2',' aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1') -- re-add, adjust Second priority over Primary SELECT asynchronous_connection_failover_add_managed ('ch2',' GroupReplication', 'aaaaaa) Thank you for reading, this is the content of "what is the method of replicating architecture from node automatic failover in MySQL 8.0.23", after the study of this article I believe you have a deeper understanding of what is the method of replication architecture automatic failover from nodes in MySQL 8.0.23, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.