Step‑by‑Step Guide to Building a Multi‑Node MySQL Group Replication (MGR) Cluster

Overview

MySQL Group Replication (MGR) is the official high‑availability solution introduced in MySQL 5.7.17. It implements the Paxos protocol to provide strong data consistency across a cluster of servers, overcoming the limitations of traditional asynchronous and semi‑synchronous replication.

How MGR Works

Each member of the group executes transactions locally, then broadcasts the write‑set to all other members. After conflict detection, the group decides whether the transaction can be committed. All members apply the transaction in the same order, guaranteeing strong consistency.

Replication Modes

Single‑primary mode (default) : only one server accepts writes; the remaining members serve read‑only traffic.

Multi‑primary mode : every server can accept writes, with no master‑slave distinction.

Switch to multi‑primary mode by setting group_replication_single_primary_mode=off in the configuration.

Key Features

True multi‑node read/write clustering.

Strong consistency via native replication and Paxos.

Simplified failover.

Automatic node addition/removal with data synchronization.

Limitations

Does not support the SERIAL isolation level or foreign‑key cascade.

DDL statements cannot be rolled back; conflict detection for DDL is unavailable, which may cause issues when executing concurrent DDL.

Multi‑Primary Cluster Setup (Three Nodes, MySQL 5.7.17)

Prerequisites

Three servers (e.g., 192.168.56.101‑103) each running MySQL 5.7.17.

Host name resolution so that each node can reach the others.

Step‑by‑Step Procedure

Install MySQL on all nodes (installation steps omitted).

Configure host mapping on each node so that the hostnames resolve to the correct IP addresses.

Create a replication user on each node:

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.56.%' IDENTIFIED BY '123456';

Install the Group Replication plugin on each node:

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

Configure my.cnf (add the loose. prefix to each parameter):

server_id = 1013306
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = '1f2cee29-f9a2-11e7-8cbb-08002783b39d'
loose-group_replication_start_on_boot = off
loose-group_replication_local_address = 'node2:33061'
loose-group_replication_group_seeds = 'node2:33061,node3:33062,proxysql:33063'
loose-group_replication_bootstrap_group = off
loose-group_replication_single_primary_mode = off
loose-group_replication_enforce_update_everywhere_checks = true

Start the first node (node 101) :

CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION;

Verify with:

SELECT * FROM performance_schema.replication_group_members;

After the node reports MEMBER_STATE = ONLINE, disable bootstrap:

SET GLOBAL group_replication_bootstrap_group = OFF;

Start the second node (node 102) :

CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

Verify with the same SELECT query.

Start the third node (node 103) using the same commands as node 102 and verify.

When all three members show MEMBER_STATE = ONLINE, the cluster is fully operational and provides true multi‑node read/write capabilities.

Important Parameter Details

GTID must be enabled: gtid_mode = ON and enforce_gtid_consistency = ON.

Binary log settings required for MGR: binlog_checksum = NONE and binlog_format = ROW.

Metadata repositories must be tables: master_info_repository = TABLE and relay_log_info_repository = TABLE.

Write‑set extraction: transaction_write_set_extraction = XXHASH64.

Group name is a UUID generated by SELECT UUID();.

Local address ( group_replication_local_address) defines the node’s IP/port for intra‑group communication.

Seed list ( group_replication_group_seeds) lists all members’ addresses.

Multi‑primary mode requires group_replication_single_primary_mode = off.

Enable update‑everywhere checks: group_replication_enforce_update_everywhere_checks = true.

Auto‑increment increment should be set to a value greater than the number of nodes (default 7) and be identical on all members.

Verification

After starting each node, run:

SELECT * FROM performance_schema.replication_group_members;

All rows should have MEMBER_STATE = ONLINE. This confirms that the group is healthy and ready for read/write traffic.

Illustrative Diagrams