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

This article will explain in detail the example analysis of MGR single-host and multi-master mode switching in mysql. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Main library execution

CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci;use test;create table if not exists H2 (id int (10) PRIMARY KEY AUTO_INCREMENT,name varchar (50) NOT NULL); insert into test.h2 values (1, "wang"), (2, "guo"), (3, "yang"), (4, "he"); select * from test.h2

Slave library test

Delete from test.h2 where id > 3 error 1290 (HY000): The MySQL server is running with the-- super-read-only option so it cannot execute this statement

1. Switch from single master to multi-master mode

Stop group replication (performed on all MGR nodes):

Stop group_replication;set global group_replication_single_primary_mode=OFF;set global group_replication_enforce_update_everywhere_checks=ON

1.2. any mgr node executes: 186

SET GLOBAL group_replication_bootstrap_group=ON;START GROUP_REPLICATION;SET GLOBAL group_replication_bootstrap_group=OFF

1.3.Then execute on other mgr nodes: 194 195

START GROUP_REPLICATION

1.4.View mgr group information (viewed by any MGR node)

SELECT * FROM performance_schema.replication_group_members

You can see that the status of all MGR nodes is online, and the roles are all PRIMARY,MGR multi-master modes built successfully.

Verify the node data synchronization of MGR multi-master mode:

Update the data on the MGR-node1 node:

Update data at the MGR-node2 node

Update data at the MGR-node3 node

In MGR multi-master mode, all nodes can read and write.

2. Switch back to single main mode

Stop group replication (performed on all MGR nodes):

Stop group_replication;set global group_replication_enforce_update_everywhere_checks=OFF;set global group_replication_single_primary_mode=ON

2.2. Select a node as the primary node and execute (186) on the primary node:

SET GLOBAL group_replication_bootstrap_group=ON;START GROUP_REPLICATION;SET GLOBAL group_replication_bootstrap_group=OFF

2.3. Execute on the remaining nodes, that is, from the library node (194195):

START GROUP_REPLICATION

2.4.View MGR group information (available on any MGR node):

SELECT * FROM performance_schema.replication_group_members

Switch back to single master mode, the master has read and write access, and the other two slave nodes are read-only and not writable.

Set the whitelist network segment of the MGR group cluster: add the network segment where the node resides

Stop group_replication;set global group_replication_ip_whitelist= "127.0.0.1 start group_replication;show variables like" 32172.16.60.0; 24172.16.50.0; 24172.16.51.0 "; group_replication_ip_whitelist"

Knowledge point expansion

MySQL Group Replication (MGR) is a database solution with high availability and high expansion introduced by MySQL officially in version 5.7.17. It is provided as a plug-in to achieve the ultimate consistency of distributed data. The characteristics of MGR are summarized as follows:

High consistency: group replication based on distributed paxos protocol to ensure data consistency

High fault tolerance: automatic detection mechanism, as long as not most of the nodes are down, can continue to work, built-in anti-brain fissure protection mechanism

High scalability: the addition and removal of nodes automatically updates the group member information, and after the new node is added, the incremental data is automatically synchronized from other nodes until it is consistent with the data of other nodes.

High flexibility: provides single-master mode and multi-master mode. Single-master mode can automatically select master after the main database is down. All writes are carried out on the master node, and multi-master mode supports multi-node writes.

This is the end of the article on "example analysis of MGR single-master and multi-master mode switching in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

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.