Mastering MySQL Group Replication: Full‑Sync Architecture, Benefits, and Step‑by‑Step Setup

MySQL Replication Types

MySQL offers asynchronous replication, which never waits for a slave to receive or execute the binlog, delivering maximum master performance but risking data loss if the master fails. Semi‑synchronous replication adds a safety layer: the master must confirm that each slave has received the binlog (though not necessarily executed it) before committing, improving data protection and working well with MHA high‑availability setups.

Group Replication Architecture

Group Replication implements true full‑sync replication using a certification‑based approach. When a client issues COMMIT, all changed rows are collected into a writeset, which is broadcast to all nodes. Each node validates the writeset against primary‑key conflicts; if validation fails, the transaction rolls back, otherwise it is committed and applied on the remaining nodes. This ensures that all servers receive transactions in the same order, though the replication is technically "virtual" full‑sync because a node may return success before the data is physically written.

Flow‑control parameters prevent transaction loss by throttling fast nodes when a slow node’s pending queue exceeds thresholds:

group_replication_flow_control_applier_threshold = 25000
group_replication_flow_control_certifier_threshold = 25000

Features and Considerations

Zero‑lag multi‑primary architecture – any node can accept reads and writes.

Automatic conflict detection guarantees consistency across nodes.

Row‑level parallel replication (improved over pre‑5.7 versions).

All nodes hold a complete data copy; GTID binlog ROW format is used.

Recommended production deployment uses three nodes.

No application changes required – standard MySQL client interfaces work.

Advantages include true multi‑master capability, no single point of failure, automatic node removal on failure, and transparent operation for applications.

Drawbacks are high overhead when adding new nodes, inability to scale writes horizontally, data duplication across nodes, slower write performance due to distributed transaction coordination, and strict network reliability requirements (risk of split‑brain). The cluster does not provide a built‑in VIP or client‑side failover; external tools such as HAProxy and custom scripts are needed.

Limitations

Supports only InnoDB tables, each of which must have a primary key.

IPv4‑only networking and a maximum of nine nodes per cluster.

No support for SAVEPOINT, SERIALIZABLE isolation in multi‑primary mode, or foreign keys in multi‑primary mode.

Overall write throughput is limited by the slowest node; uniform hardware is recommended.

Configuration Steps (Three‑Node Single‑Primary Mode)

Host resolution : add the three node IPs to /etc/hosts.

192.168.17.133   node1
192.168.17.134   node2
192.168.17.135   node3

my.cnf settings (identical on all nodes) :

log-bin = /data/mysql57/binlog/mysql-bin
binlog_format = ROW
sync_binlog = 1
binlog_checksum = NONE
log_slave_updates = 1
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE

Install the plugin on each server:

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

Set global Group Replication variables (run on all three nodes) :

SET GLOBAL transaction_write_set_extraction = "XXHASH64";
SET GLOBAL group_replication_start_on_boot = ON;
SET GLOBAL group_replication_bootstrap_group = OFF;
SET GLOBAL group_replication_group_name = "13dd01d4-d69e-11e6-9c80-000c2937cddb";
SET GLOBAL group_replication_local_address = '192.168.17.133:6606';
SET GLOBAL group_replication_group_seeds = '192.168.17.133:6606,192.168.17.134:6606,192.168.17.135:6606';
SET GLOBAL group_replication_single_primary_mode = ON;

Note: generate a UUID for group_replication_group_name via SELECT UUID(); . Adjust group_replication_local_address on node2 and node3 to their own IPs.

Add nodes to the cluster : On the primary node:

SET SQL_LOG_BIN=0;
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;
SET GLOBAL group_replication_bootstrap_group=OFF;

On each secondary node (repeat the same commands without the bootstrap flag):

SET SQL_LOG_BIN=0;
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;
SELECT * FROM performance_schema.replication_group_members;

Verify that the MEMBER_STATE column shows ONLINE before proceeding.

Cluster Maintenance and Recovery

If a secondary node crashes and cannot rejoin, recover by dumping a full backup from a healthy secondary:

mysqldump -uroot -p123456 -q --single-transaction \
    --master-data=2 -B yourDB > /root/yourDB.sql

The dump includes a SET @@GLOBAL.GTID_PURGED='...' statement. Import the dump on the failed node, then execute:

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

Final Remarks

MySQL 5.7.17 introduced Group Replication, but it is still considered early‑stage for production; waiting for a later minor release (e.g., 5.7.40 or newer) is advisable.