Skip to content
Tutorials Center
My learning

Topics

Lesson 63 of 75 ยท Security and Administration

High Availability: Group Replication, InnoDB Cluster and Managed Services

Understand MySQL Group Replication, InnoDB Cluster, MySQL Router, ProxySQL, Galera and managed services such as RDS and Aurora, plus a short sharding overview.

Classic replication copies data, but it does not decide who is in charge when a server dies. High availability (HA) means the database keeps serving requests after a machine fails, with little or no manual work and little or no lost data. MySQL offers built-in building blocks for this (Group Replication, InnoDB Cluster, MySQL Router), third-party tools (ProxySQL, Galera-based clusters), and managed cloud services that hide the work.

Everything with a real cluster is read-only here. The live examples model two ideas you must understand to run any of these systems: quorum (how many servers must agree) and sharding (how rows are routed to servers).

Three layers of an HA setup

Data copies Several servers hold the same data.
Agreement Members decide who is primary and who is alive.
Routing A router sends clients to the right member.

Missing any one layer means manual work during a failure.

1 / 2

Group Replication

Group Replication is a MySQL plugin in which a set of servers form a group and keep each other's data in sync. A transaction is committed only after a majority of the members agree, using a Paxos-style protocol, so a failed member never leaves the group with a different history. Groups have 1 to 9 members; 3 or 5 are typical.

There are two modes:

  • Single-primary (default): one member accepts writes, the rest are read-only secondaries. If the primary fails, the group elects a new one automatically.
  • Multi-primary: every member accepts writes. Conflicts are detected at commit time and one transaction is rolled back, so applications must be written to retry. Most teams stay with single-primary.

Group Replication needs InnoDB tables with primary keys, GTIDs, binary logging with ROW format, and a reliable low-latency network. Basic setup on each member looks like this, shown as a sketch rather than a full recipe:

INI
[mysqld]
server_id                          = 1
gtid_mode                          = ON
enforce_gtid_consistency           = ON
plugin_load_add                    = group_replication.so
loose-group_replication_group_name = "aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_local_address = "10.0.0.1:33061"
loose-group_replication_group_seeds   = "10.0.0.1:33061,10.0.0.2:33061,10.0.0.3:33061"

Setting this up by hand is fiddly, which is why InnoDB Cluster exists.

Quorum: why three is the smallest useful number

A group continues to work only while a majority of its members can talk to each other. With n members, the majority is floor(n / 2) + 1. The number of members you can lose is n - majority. Two members tolerate no failure at all, because if the link between them breaks, neither side has a majority. That is why two-node clusters are a trap, and why odd sizes are best.

SQL Stand-in: quorum size and tolerated failures

Press Run (or Ctrl + Enter) to see the result here.

The integer division gives 2 for three members and tolerates 1 failure, 3 for five members and tolerates 2. Notice that 4 members tolerate only 1 failure, the same as 3, so the extra server buys nothing for availability. In MySQL itself, use DIV for integer division; SQLite's / already does integer division on integers.

InnoDB Cluster and MySQL Router

InnoDB Cluster packages three things together:

  1. Group Replication for data and agreement.
  2. MySQL Shell's AdminAPI for setup and management.
  3. MySQL Router for connection routing.

With the AdminAPI, building a cluster is a few commands in MySQL Shell:

JavaScript
dba.checkInstanceConfiguration("[email protected]:3306")
dba.configureInstance("[email protected]:3306")
var cluster = dba.createCluster("prod")
cluster.addInstance("[email protected]:3306")
cluster.addInstance("[email protected]:3306")
cluster.status()

When a new member is added, the AdminAPI uses the Clone plugin to copy data to it, so no manual seeding is needed. On each application server, you then bootstrap Router against the cluster:

Terminal
mysqlrouter --bootstrap clusteradmin@10.0.0.1:3306 --user=mysqlrouter

Router reads the cluster's metadata and then listens on local ports. By default, 6446 sends read-write traffic to the primary and 6447 spreads read-only traffic over secondaries (the X Protocol uses 6448 and 6449). The application simply connects to 127.0.0.1:6446; during a failover Router redirects new connections to the new primary, and the application only needs to reconnect and retry. Older connections to the dead server fail, so applications must handle dropped connections.

Related tools: InnoDB ClusterSet links clusters in different data centers for disaster recovery, and InnoDB ReplicaSet is a simpler, asynchronous arrangement that does not guarantee zero data loss.

ProxySQL and other approaches

ProxySQL is a popular third-party proxy. It sits between the application and MySQL, and can route by rules (for example, send SELECT to replicas), pool connections, cache results and shift traffic away from unhealthy servers. It is often paired with plain asynchronous replication plus an orchestration tool that promotes a replica when the source fails, such as Orchestrator. That combination is flexible, but you are responsible for avoiding split-brain and for fencing the old source.

A Galera-based cluster (MariaDB Galera Cluster, Percona XtraDB Cluster) offers synchronous multi-primary replication through a different protocol. It is not part of MySQL Community Server, but you will meet it in the wild, and many concepts (quorum, a bootstrap node, flow control) look the same.

Managed services

Cloud providers will run all of this for you, and each one does it differently:

Service How HA works Things to know
Amazon RDS for MySQL Multi-AZ: a synchronous standby in another zone, automatic failover The standby is not readable in the classic Multi-AZ mode. Read replicas are separate and asynchronous.
Amazon Aurora MySQL Storage is shared and replicated across zones; readers attach to the same storage MySQL-compatible, not the same engine internals. Replica lag is usually very small.
Google Cloud SQL A regional HA instance with a standby in another zone Failover changes which zone serves traffic.
Azure Database for MySQL Zone-redundant or same-zone HA options Check the exact tier before relying on it.

On any managed service, the connection endpoint stays the same during failover, but connections drop for tens of seconds, so use short timeouts and retries in your driver. You also give up some control: restricted privileges, a limited choice of plugins, and server settings changed through parameter groups. Keep logical dumps as an exit plan, as described in Backup and Restore.

Sharding overview

Replication and HA make copies. They do not make the data smaller. When one primary can no longer hold the data or the write rate, you must shard: split rows across independent clusters by a shard key, such as the customer id.

The costs are real. Queries and transactions across shards are hard or impossible, unique ids must be coordinated, re-balancing means moving data, and every query needs the shard key. Options include application-level routing, a proxy such as Vitess (which powers very large MySQL deployments) and hosted products built on it. Before sharding, try every cheaper fix first: indexes, read replicas, caching, archiving old rows, and a bigger machine. The multi-tenant project later in this track, Project: Multi-Tenant SaaS Schema, designs a schema that can be sharded by tenant.

A live stand-in for routing: the shard is id % 3.

SQL Stand-in: route rows to shards with a key

Press Run (or Ctrl + Enter) to see the result here.

Each shard holds two or three customers. Modulo routing is simple, but changing the number of shards moves almost every row, which is why real systems use lookup tables, consistent hashing or ranges that can be split.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A Group Replication group has 5 members. How many can fail while it still has a majority?

    The majority of 5 is 3, so the group tolerates 5 - 3 = 2 failed members.
  2. 2. What does MySQL Router do in an InnoDB Cluster?

    Group Replication elects the primary. Router reads the cluster metadata and sends read-write and read-only traffic to the right members.
  3. 3. Why is a two-member group a poor design?

    A majority of 2 is 2, so losing either member loses the quorum. Three members tolerate one failure.
  4. 4. When is sharding the right step?

    Replicas scale reads. Sharding scales data size and writes, but costs cross-shard queries and complexity, so use it last.

Summary

  • HA needs data copies, agreement on who is primary, and routing for clients.
  • Group Replication commits by majority; single-primary mode is the default and the safest choice.
  • InnoDB Cluster is Group Replication plus the Shell AdminAPI plus MySQL Router (ports 6446 and 6447 by default).
  • Use odd group sizes; 3 tolerates one failure, 5 tolerates two.
  • ProxySQL with orchestrated replication, Galera clusters and managed services (RDS Multi-AZ, Aurora, Cloud SQL) are the alternatives. Applications must always retry after a failover.
  • Sharding splits data by a key and should be a last resort.

Next: Upgrading and Monitoring MySQL.