Detailed explanation of mysql MGR single-master and multi-master mode switching knowledge points

Main library execution

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

Test from library

delete from test.h1 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

1.1. Stop group replication (execute 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. Execute any mgr node: 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 (view from any MGR node)

SELECT * FROM performance_schema.replication_group_members;

It can be seen that the status of all MGR nodes is online, the role is PRIMARY, and the MGR multi-master mode is successfully established.

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

Update data on MGR-node1:

Update data on MGR-node2

Update data on MGR-node3

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

2. Switch back to single-master mode

2.1. Stop group replication (execute 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 master node and execute (186) on the master 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 nodes (194 195):

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 permissions, and the other two slave nodes are read-only and cannot write.

Set the whitelist network segment of the MGR group cluster: Add the network segment where the node is located

stop group_replication;
set global group_replication_ip_whitelist="127.0.0.1/32,172.16.60.0/24,172.16.50.0/24,172.16.51.0/24";
start group_replication;
show variables like "group_replication_ip_whitelist";

Knowledge point expansion

MySQL Group Replication (MGR) is a database high availability and high scalability solution introduced by MySQL in version 5.7.17. It is provided in the form of a plug-in and achieves the final consistency of distributed data. The features of MGR are summarized as follows:

• High consistency: group replication is implemented based on the distributed Paxos protocol to ensure data consistency;
• High fault tolerance: automatic detection mechanism, as long as the majority of nodes are not down, the system can continue to work, and built-in anti-brain split protection mechanism;
• High scalability: The addition and removal of nodes will automatically update group member information. After a new node joins, it will automatically synchronize incremental data from other nodes until the data is consistent with other nodes.
• High flexibility: It provides single-master mode and multi-master mode. In the single-master mode, the master can be automatically selected after the main database fails. All writes are performed on the main node. The multi-master mode supports multi-node writing.

The above is the detailed content of the detailed explanation of the knowledge points of MySQL MGR single-master and multi-master mode switching. For more information about MySQL MGR single-master and multi-master mode switching, please pay attention to other related articles on 123WORDPRESS.COM!