Understanding MySQL Group Replication Consistency Levels (group_replication_consistency)

MySQL Group Replication is a plugin that extends traditional master‑slave replication with features such as data‑consistency monitoring, automatic conflict detection, fault detection, and automatic node scaling. The group_replication_consistency system variable controls the consistency guarantee of the group, offering five selectable values.

The five values are:

EVENTUAL – guarantees eventual consistency; reads return immediately without waiting for other nodes.

BEFORE – guarantees strong consistency on the local node; the node waits for its relay log to be fully applied before returning.

AFTER – guarantees global strong consistency; the node waits for all members to apply the transaction.

BEFORE_AND_AFTER – combines the guarantees of BEFORE and AFTER.

BEFORE_ON_PRIMARY_FAILOVER – ensures local consistency after a primary failover.

The article focuses on the first three modes, illustrating each with a three‑node cluster (debian‑ytt1, debian‑ytt2, debian‑ytt3). The initial cluster status is shown as JSON:

{
  "clusterName": "ytt_mgr",
  "defaultReplicaSet": {
    "name": "default",
    "primary": "debian-ytt1:3306",
    "ssl": "REQUIRED",
    "status": "OK",
    "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
    "topology": {
      "debian-ytt1:3306": {"address": "debian-ytt1:3306", "mode": "R/W", "role": "HA", "status": "ONLINE", "version": "8.0.20"},
      "debian-ytt2:3306": {"address": "debian-ytt2:3306", "mode": "R/O", "role": "HA", "status": "ONLINE", "version": "8.0.20"},
      "debian-ytt3:3306": {"address": "debian-ytt3:3306", "mode": "R/O", "role": "HA", "status": "ONLINE", "version": "8.0.20"}
    },
    "topologyMode": "Single-Primary"
  },
  "groupInformationSourceMember": "debian-ytt1:3306"
}

EVENTUAL mode (default) – the example creates a test table, inserts a row on node 1, and shows that node 2 can read the row immediately. After a read‑lock is placed on node 2, a second insert on node 1 is not visible on node 2 until the lock is released, demonstrating that reads may return stale data.

<debian-ytt1|mysql> create table t1 (id serial primary key, r1 int, r2 int, r3 char(36));
Query OK, 0 rows affected (0.07 sec)

<debian-ytt1|mysql> insert into t1 (r1,r2,r3) select 10,20,uuid();
Query OK, 1 row affected (0.02 sec)

<debian-ytt2|mysql> lock table t1 read;
Query OK, 0 rows affected (0.00 sec)

<debian-ytt1|mysql> insert into t1 (r1,r2,r3) select 20,20,uuid();
Query OK, 1 row affected (0.02 sec)

<debian-ytt2|mysql> select * from t1;
+----+------+------+--------------------------------------+
| id | r1   | r2   | r3                                   |
+----+------+------+--------------------------------------+
|  1 |   10 |   20 | e878289e-89c4-11ea-861d-08002753f58d |
+----+------+------+--------------------------------------+

BEFORE mode – after clearing the table and placing a read‑lock on node 2, a new insert on node 1 is performed. A session on node 2 that sets @@group_replication_consistency='before' blocks for several minutes until the lock is released and the relay log is applied, guaranteeing that the query sees the latest data.

truncate t1;
Query OK, 0 rows affected (0.17 sec)

<debian-ytt2|mysql> lock table t1 read;
Query OK, 0 rows affected (0.01 sec)

<debian-ytt1|mysql> insert into t1 select 1,1,1,uuid();
Query OK, 1 row affected (0.02 sec)

<debian-ytt2|mysql> set @@group_replication_consistency='before';
Query OK, 0 rows affected (0.00 sec)

<debian-ytt2|mysql> select * from t1;   # hangs until lock released

When the lock on node 2 is released, the blocked query returns after about 3 minutes, confirming that BEFORE enforces local strong consistency at the cost of latency.

<debian-ytt2|mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

<debian-ytt2|mysql> select * from t1;
+----+------+------+--------------------------------------+
| id | r1   | r2   | r3                                   |
+----+------+------+--------------------------------------+
|  1 |    1 |    1 | ee83837e-89ce-11ea-861d-08002753f58d |
+----+------+------+--------------------------------------+

AFTER mode – the parameter is set to after on the primary node, a read‑lock is placed on node 2, and an insert on node 1 blocks until node 2’s lock is released. All nodes must wait for the slowest member, resulting in a transaction commit time of several minutes, illustrating the performance penalty of global strong consistency.

set @@group_replication_consistency='after';
Query OK, 0 rows affected (0.00 sec)

<debian-ytt2|mysql> lock table t1 read;
Query OK, 0 rows affected (0.01 sec)

<debian-ytt1|mysql> insert into t1 select 1,1,1,uuid();   # blocks

<debian-ytt2|mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

<debian-ytt1|mysql> insert into t1 select 1,1,1,uuid();
Query OK, 1 row affected (6 min 47.14 sec)

In summary, EVENTUAL provides low latency but may return stale data, BEFORE guarantees the latest data on the local node but can suffer long waits if the relay log is heavy, and AFTER ensures full cluster consistency at the expense of significant performance overhead, especially when the slowest node delays the commit.