MGR semi synchronous mutual rotation tutorial

Posted by tmayder on Sun, 07 Nov 2021 00:01:32 +0100

This article is written based on MySQL 5.7.32, and the content is the mutual conversion and implementation of MGR architecture and semi synchronous replication architecture.

In order to save time, only two node databases are deployed for testing. In actual production, it is recommended to install at least three node databases. MGR follows most survival concepts, that is, a cluster of three servers, and one of them is allowed to go down.

The following is a brief introduction to the replication architecture involved in this article:

Semi synchronous replication: between asynchronous replication and full synchronous replication. The master library waits for at least one slave library to receive and log events (the required number of slave libraries is configurable), and then commits the transaction. The master database does not wait for all slave databases to acknowledge receipt. It only needs confirmation from one slave database, rather than that the transaction has been fully executed and committed on the slave database side. Therefore, semi synchronous replication can ensure that all transactions committed by the master database have been transferred to at least one slave database when the master database crashes.

MGR: MySQL Group Replication (MGR) is a high availability and high scalability solution introduced in MySQL version 5.7.17. It can be used as a plug-in. MGR is based on distributed paxos protocol to realize group replication and ensure data consistency. Built in fault detection and automatic selection of main functions, you can continue to work normally as long as most nodes in the cluster are down. Single master mode and multi master mode are provided. Multi master mode supports multi-point writing.

1, Environmental inspection

  1. System version

  1. Turn off firewall

  1. server information
10.186.62.92    MGR-node1
10.186.62.35    MGR-node2

  1. MySQL version

  • 5.7.32

2, Hostname binding

3, MGR parameter configuration node1

#Basic parameters
[mysqld]
####: for global
skip_ssl
user        =mysql3318                        
basedir     =/usr/local/mysql
datadir      =/database/mysql/data/3318      
tmpdir	    =/database/mysql/tmp/3318                          
port         =3318                           
log_error    =err.log     
pid_file      =mysqld.pid
socket      =mysqld.sock
disabled_storage_engines=archive,blackhole,example,federated,memory,merge,ndb
plugin_load  = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so;validate_password=validate_password.so"
transaction_isolation	=READ-COMMITTED #RC
#MGR Copy frame
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
 #MGR set up
#Defines the hash algorithm used to generate the identification associated with the transaction, which is used for distributed conflict detection and processing in group replication.
transaction_write_set_extraction=XXHASH64
#Tell the plug-in to join or create a group name, UUID
loose-group_replication_group_name="fb3f2ad0-8d25-11eb-96d3-02000aba3e5c"
#server Do not self start group replication at startup,To avoid booting a second group with the same name every time you start,So set to OFF. 
loose-group_replication_start_on_boot=off
#Tell plug-ins to use IP Address, port 33181 is used to receive incoming connections from other members of the group
loose-group_replication_local_address="10.186.62.92:33181"
#Start the group server and seed server. Join the group and connect these ip and ports; Other servers to join the group must be agreed by the group members
loose-group_replication_group_seeds="10.186.62.92:33181,10.186.62.35:33181,10.186.62.66:33181"
loose-group_replication_bootstrap_group=off
report_host=10.186.62.92
report_port=3318
plugin_load_add='group_replication.so'

4, MGR parameter configuration node2

  1. Copy the parameters of node1 to node2

  2. Modify the following three parameters in the MGR-node2 node / etc/my.cnf file as their own parameters.

  • server_id

  • loose-group_replication_local_address

  • report_host
#[MGR]
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="fb3f2ad0-8d25-11eb-96d3-02000aba3e5c"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="10.186.62.35:33181"
loose-group_replication_group_seeds="10.186.62.92:33181,10.186.62.35:33181,10.186.62.66:33181"
loose-group_replication_bootstrap_group=off
report_host=10.186.62.35
report_port=3318
plugin_load_add='group_replication.so'

5, Restart MySQL to make the parameters take effect

  • systemctl restart mysql3318

6, Install the MGR plug-in and set the copy account

  1. All MGR nodes must execute
  2. The statement is as follows:
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

Verify that the plug-in was loaded successfully

show plugins ;

 

SET SQL_LOG_BIN=0;
CREATE USER rpl_mgr@'10.186.62.%' IDENTIFIED WITH sha256_password BY 'ZZQzzq123###';
GRANT REPLICATION SLAVE ON *.* TO rpl_mgr@' 10.186.62.%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_mgr', MASTER_PASSWORD='ZZQzzq123###' FOR CHANNEL 'group_replication_recovery';

7, Start MGR single main mode

  1. Start MGR and execute it on the main library (10.186.62.92) node

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
SELECT * FROM performance_schema.replication_group_members;

 

Master node read / write

 

  1. Join the cluster from the library (10.186.62.35)

reset master; 
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;

 

Read only from node

 

8, Verify that the synchronization is normal

  1. Master create database

 

  1. Slave view synchronized

 

9, Attempt to start semi synchronization when MGR is turned on

  1. Create a user repl in the Master for semi synchronous operation
CREATE USER repl@'10.186.62.%' IDENTIFIED WITH sha256_password BY 'ZZQzzq123###';
GRANT REPLICATION SLAVE ON *.* TO repl@'10.186.62.%'；
FLUSH PRIVILEGES;

  1. The change master operation is performed from the library

CHANGE MASTER TO MASTER_HOST='10.186.62.92',MASTER_USER='repl',MASTER_PORT=3318,MASTER_PASSWORD='ZZQzzq123###',MASTER_AUTO_POSITION=1;

  1. Execute START SLAVE;

  2. Copy error

 

  1. Conclusion: semi synchronous replication cannot be started when MGR is turned on.

10, MGR switches to semi synchronous

  1. Slave execution
stop it MGR
STOP GROUP_REPLICATION;
uninstall MGR plug-in unit
UNINSTALL PLUGIN group_replication;

  1. Perform step 1 on the Master

  2. Slave view current MGR information

SELECT * FROM performance_schema.replication_group_members;

 

  1. Master view current MGR information

 

  1. Start semi synchronization

  • If executed   Steps 1-2 of step 9 directly execute START SLAVE on the Slave;
  • Otherwise, perform steps 1-3 of step 9 on the Slave
  • View replication status

 

  • Create Library in Master

 

  • Verify synchronization on Slave

 

  1. Permanent annotation MGR plug-in

If necessary, note the following parameters in / etc/my.cnf to prevent MySQL from automatically loading the MGR plug-in

#plugin_load_add='group_replication.so'

11, Semi synchronous switching to MGR

  1. Disable semi synchronization on Slave
STOP SLAVE;
SET GLOBAL SUPER_READ_ONLY = OFF;
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

  1. Start MGR on Master

INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

  1. Slave joins the cluster

START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;

 

Hot Topics

©2025 Programmer Think