Implementing Conversion Between MySQL Group Replication (MGR) and Semi‑Synchronous Replication

Background

This article, based on MySQL 5.7.32, explains how to convert between MySQL Group Replication (MGR) and semi‑synchronous replication. For testing, a two‑node cluster is used, though production should have at least three nodes to satisfy MGR’s majority‑alive requirement.

Semi‑synchronous replication lies between asynchronous and fully synchronous modes: the primary waits for at least one replica to acknowledge receipt of events before committing, ensuring durability if the primary fails.

MGR provides high‑availability group replication using a Paxos‑based protocol, supporting single‑primary or multi‑primary modes and automatic failover.

1. Environment Check

System version (see screenshot).

Disable firewall (see screenshot).

Server information: 10.186.62.92 MGR-node1<br/>10.186.62.35 MGR-node2 MySQL version: 5.7.32.

2. Hostname Binding

Configure /etc/hosts so each node can resolve the other’s hostname (see image).

3. MGR Parameter Configuration – Node1

# Basic parameters<br/>[mysqld]<br/>skip_ssl<br/>user = mysql3318<br/>basedir = /usr/local/mysql<br/>datadir = /database/mysql/data/3318<br/>tmpdir = /database/mysql/tmp/3318<br/>port = 3318<br/>log_error = err.log<br/>pid_file = mysqld.pid<br/>socket = mysqld.sock<br/>disabled_storage_engines = archive,blackhole,example,federated,memory,merge,ndb<br/>plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so;validate_password=validate_password.so"<br/>transaction_isolation = READ-COMMITTED #RC<br/># MGR settings<br/>server_id = 1<br/>gtid_mode = ON<br/>enforce_gtid_consistency = ON<br/>binlog_checksum = NONE<br/>log_bin = binlog<br/>log_slave_updates = ON<br/>binlog_format = ROW<br/>master_info_repository = TABLE<br/>relay_log_info_repository = TABLE<br/>transaction_write_set_extraction = XXHASH64<br/>loose-group_replication_group_name = "fb3f2ad0-8d25-11eb-96d3-02000aba3e5c"<br/>loose-group_replication_start_on_boot = off<br/>loose-group_replication_local_address = "10.186.62.92:33181"<br/>loose-group_replication_group_seeds = "10.186.62.92:33181,10.186.62.35:33181,10.186.62.66:33181"<br/>loose-group_replication_bootstrap_group = off<br/>report_host = 10.186.62.92<br/>report_port = 3318<br/>plugin_load_add = 'group_replication.so'

4. MGR Parameter Configuration – Node2

Copy Node1’s my.cnf to Node2.

Modify three parameters to match Node2’s environment: server_id, loose-group_replication_local_address, and report_host.

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

5. Restart MySQL

Run systemctl restart mysql3318 on each node.

6. Install MGR Plugin and Create Replication User

Execute on all nodes:

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

Verify with SHOW PLUGINS; Create replication user (example):

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

7. Start MGR Single‑Primary Mode

On the primary (10.186.62.92):

SET GLOBAL group_replication_bootstrap_group=ON;<br/>START GROUP_REPLICATION;<br/>SET GLOBAL group_replication_bootstrap_group=OFF;<br/>SELECT * FROM performance_schema.replication_group_members;

On the secondary (10.186.62.35):

RESET MASTER;<br/>START GROUP_REPLICATION;<br/>SELECT * FROM performance_schema.replication_group_members;

8. Verify Synchronization

Create a database on the primary and confirm it appears on the secondary.

9. Attempt to Enable Semi‑Synchronous While MGR Is Running

Create user repl on the primary for semi‑sync.

On the replica, run

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

Start slave with START SLAVE; Replication fails – conclusion: semi‑synchronous replication cannot be started while MGR is active.

10. Switch MGR to Semi‑Synchronous

Stop MGR on the primary:

STOP GROUP_REPLICATION;<br/>UNINSTALL PLUGIN group_replication;

Configure semi‑sync on both nodes (as in step 9) and start the slave.

11. Switch Back to MGR

On the replica, stop semi‑sync and reinstall MGR plugin:

STOP SLAVE;<br/>SET GLOBAL SUPER_READ_ONLY = OFF;<br/>INSTALL PLUGIN group_replication SONAME 'group_replication.so';

On the primary, start MGR again:

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

Allow the replica to join the group with START GROUP_REPLICATION; Optionally comment out #plugin_load_add='group_replication.so' in /etc/my.cnf to prevent automatic loading.