MySQL Group Replication Basics

Manual failover with async replication is the part of my job I like least: work out which replica is most current, repoint the others, change the application's connection string, all while someone waits. Group Replication (a plugin since 5.7.17, much more mature in 8.0) makes the servers agree on every transaction through a Paxos-based protocol, and elects a new primary by itself when one dies.

I built a three-node single-primary group on 8.0 by hand, without MySQL Shell's dba.createCluster(). I like to know what a wrapper does before I rely on it. You need three servers (I used 8.0.15) that can reach each other on TCP 3306 and 33061, InnoDB only, a primary key on every table, row-format binary logging, and hostnames that resolve everywhere (or just use IPs).

Node configuration

On every node, in /etc/my.cnf, with a different server_id and its own address in group_replication_local_address:

[mysqld]
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
transaction_write_set_extraction = XXHASH64

plugin_load_add = group_replication.so
group_replication_group_name = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_start_on_boot = OFF
group_replication_local_address = "10.0.5.11:33061"
group_replication_group_seeds = "10.0.5.11:33061,10.0.5.12:33061,10.0.5.13:33061"
group_replication_bootstrap_group = OFF
group_replication_ip_whitelist = "10.0.5.0/24"

group_replication_group_name must be a valid UUID; generate one with uuidgen and use the same value on all nodes. Restart MySQL after editing.

The recovery user

Nodes joining the group pull missing transactions from a member over ordinary replication, so each node needs a replication account. Create it on every node with binary logging disabled so it is not replicated as a conflicting transaction:

SET SQL_LOG_BIN = 0;
CREATE USER 'rpl_user'@'10.0.5.%' IDENTIFIED BY 'rpl-password';
GRANT REPLICATION SLAVE ON *.* TO 'rpl_user'@'10.0.5.%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN = 1;

CHANGE MASTER TO MASTER_USER = 'rpl_user', MASTER_PASSWORD = 'rpl-password'
  FOR CHANNEL 'group_replication_recovery';

If your clients are old, create the user with IDENTIFIED WITH mysql_native_password; the recovery channel itself supports caching_sha2_password over TLS.

Bootstrapping the first node

Exactly one node bootstraps, exactly once. Bootstrapping twice gives you two groups, and I'd rather not repeat that afternoon:

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

You should see one row with MEMBER_STATE: ONLINE and MEMBER_ROLE: PRIMARY.

Joining the others

On nodes two and three:

START GROUP_REPLICATION;

Each goes through RECOVERING while it fetches transactions, then ONLINE. From any member:

SELECT member_host, member_port, member_state, member_role
FROM performance_schema.replication_group_members;

Once everything is ONLINE, set group_replication_start_on_boot = ON in my.cnf on all nodes so a restarted server rejoins automatically.

Single-primary mode

By default only the primary accepts writes; the others have super_read_only = ON, enforced by the plugin. Find the current primary:

SELECT member_host FROM performance_schema.replication_group_members WHERE member_role = 'PRIMARY';

On 8.0.13 and later you can move the primary deliberately:

SELECT group_replication_set_as_primary('MEMBER-UUID-HERE');

Applications still need to find the primary. That is the job of MySQL Router or a load balancer with a health check that reads the query above; Group Replication itself does not provide a virtual IP.

Break it on purpose

Write on the primary and read on a secondary:

-- on the primary
CREATE DATABASE grtest; CREATE TABLE grtest.t (id INT PRIMARY KEY, v VARCHAR(20)); INSERT INTO grtest.t VALUES (1,'hello');
-- on a secondary
SELECT * FROM grtest.t;
INSERT INTO grtest.t VALUES (2,'nope');
-- ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

Then stop mysqld on the primary and watch the remaining two elect a new one within a few seconds. Bring the old primary back; it rejoins as a secondary.

Errors

• [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: ... > Group transactions: ...' – The joining node has GTIDs the group does not know about, usually from creating the recovery user with binlog enabled. Fix with RESET MASTER on that node (only if it has no data of its own) and retry.
• [ERROR] Plugin group_replication reported: 'Table t does not have any PRIMARY KEY. This is not compatible with Group Replication.' – Every table needs a primary key or a non-null unique key. Add one before the write.
• [ERROR] Plugin group_replication reported: 'Timeout on wait for view after joining group' – Port 33061 is blocked between nodes, or group_replication_ip_whitelist excludes the joiner. Check firewall-cmd --list-ports and open 33061/tcp.
• Member stuck in RECOVERING with [ERROR] Slave I/O for channel 'group_replication_recovery': error connecting to master – The recovery user or password is wrong on the node it chose as donor, or the donor requires TLS. Check SHOW SLAVE STATUS FOR CHANNEL 'group_replication_recovery'\G.
• ERROR 3092 (HY000): The server is not configured properly to be an active member of the group – One of the node settings above is missing; the error log states exactly which (binlog_checksum, transaction_write_set_extraction and enforce_gtid_consistency are the usual suspects).

Learn the manual steps once, then let MySQL Shell and Router handle the day-to-day. That combination is what Oracle calls InnoDB Cluster.