Architecture

MySQL Group Replication and InnoDB Cluster: A Production Guide

Group Replication gives MySQL automatic failover without an external orchestrator, but it has hard limits on transaction size, network latency and table design. Here is what it really guarantees, which defaults to change, and what breaks first on large databases.

· 14 min read · AG Data Team

MySQL Group Replication is the built-in way to run MySQL as a self-healing cluster: members agree on every transaction through a Paxos-based group protocol, a failed primary is replaced automatically, and no external tool decides who is in charge. InnoDB Cluster is the same engine packaged with MySQL Shell for administration and MySQL Router for client routing.

It works well, but it is less forgiving than classic asynchronous replication. Big transactions, slow networks and tables without primary keys that were harmless before now cause rollbacks, expelled members or a throttled primary. This guide covers what you need to know before you put it in front of production traffic.

What you are actually getting

Requirements that bite on existing databases

A fresh install meets most requirements out of the box. An application database that grew for ten years usually does not (Group Replication requirements):

[mysqld]
server_id = 1                     # unique per member
gtid_mode = ON                    # not the default
enforce_gtid_consistency = ON     # not the default
disabled_storage_engines = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
# binlog, log_replica_updates and ROW format are already defaults in 8.4

In MySQL 8.4 binary logging, log_replica_updates and row-based logging are already on by default. The multithreaded applier is also on by default with 4 workers, and it requires replica_preserve_commit_order=ON.

Choose the consistency level on purpose

In 8.4 the default group_replication_consistency is BEFORE_ON_PRIMARY_FAILOVER: after a failover, new transactions on the new primary wait until it has applied the backlog from the old one, so clients do not read old values right after a switch (consistency guarantees). The cost is a pause during failover that grows with the backlog. Other levels:

The variable can be set per session, so keep the global default and raise it only for the code paths that need it.

Defaults worth changing

Out of the box, a member that ends up in a minority after a network partition waits forever: group_replication_unreachable_majority_timeout defaults to 0. It cannot commit anything, so your data is safe, but clients still connected to it hang. With a finite timeout the failure becomes visible: when it elapses, pending transactions on the minority are rolled back, the member moves to ERROR, tries to rejoin in super read only mode, and if that fails follows group_replication_exit_state_action (default OFFLINE_MODE):

-- A member cut off in a minority gives up after 30 s
SET PERSIST group_replication_unreachable_majority_timeout = 30;
-- after failed rejoin attempts it follows this action (already the default)
SET PERSIST group_replication_exit_state_action = 'OFFLINE_MODE';

A suspected member is expelled 5 seconds after the initial 5-second detection period by default (group_replication_member_expel_timeout), and it tries to rejoin 3 times (group_replication_autorejoin_tries). On networks that hiccup, raising the expel timeout reduces pointless expulsions at the cost of slower failure handling. Pick the value from your network, not from a blog post, ours included.

InnoDB Cluster does the wiring for you. MySQL Shell's AdminAPI checks and configures instances (dba.checkInstanceConfiguration(), dba.configureInstance()), creates the group and adds members. MySQL Router, bootstrapped against the cluster, sends classic protocol read-write traffic to port 6446 and read-only traffic to 6447 by default (Router bootstrapping), and follows the primary after a failover. Use them unless you have a reason not to; hand-built groups are where most of the odd incidents we see come from.

Monitoring what matters

SELECT member_host, member_state, member_role
FROM performance_schema.replication_group_members;

SELECT member_id,
       count_transactions_in_queue,                -- waiting for certification
       count_transactions_remote_in_applier_queue, -- certified, waiting to apply
       count_conflicts_detected
FROM performance_schema.replication_group_member_stats;

On large databases

Group Replication behaves the same on 10 GB and 10 TB, but the margins disappear. These are the things that hurt at scale:

Key takeaways

MySQL Group Replication InnoDB Cluster High availability
Work with us

Planning MySQL high availability?

We design, migrate and run MySQL Group Replication and InnoDB Cluster, from the first config check to failover drills on production-sized data.

Get in touch More from the blog