Choosing the Right MySQL HA Architecture: From Replication to InnoDB Cluster

High‑Availability Architectures

MySQL has evolved from asynchronous replication, semi‑synchronous replication, and Group Replication to the modern InnoDB Cluster and InnoDB Replica Set. The article first outlines each architecture and its typical use cases.

MySQL Replication

Classic master‑slave replication requires many manual steps such as user management, backup/restore, and configuring the replication itself. MySQL provides only the core functionality; the surrounding automation and orchestration must be built by DBAs or engineers.

MySQL InnoDB Cluster

Introduced in 2016, InnoDB Cluster bundles MySQL Group Replication, MySQL Shell, MySQL Router, and MySQL Clone into a single integrated product. Group Replication handles member changes, network partitions and provides cluster‑wide consistency. Shell offers a powerful CLI for automating component deployment, while Router provides transparent routing, load‑balancing and automatic failover.

MySQL Group Replication

Group Replication is a distributed HA engine that guarantees fault‑tolerance, automatic failover, multi‑node updates, conflict detection/resolution and zero‑data‑loss (RPO = 0). It implements a state‑machine replication model, ensuring total‑order writes and eventual consistency. From MySQL 8.0.14 onward it also supports session‑level strong consistency.

Consistency: zero data loss (RPO = 0)

High availability: automatic failover

Read scalability: on‑demand node addition/removal, flow‑control for latency, configurable consistency levels (eventual, full)

Multi‑master: simultaneous writes, ordered writes (XCOM/Paxos), good write performance, optimistic locking

MySQL Router

Router sits between applications and backend MySQL servers, providing transparent client routing, load‑balancing and automatic application‑level failover. It is simple to configure and integrates seamlessly with InnoDB Cluster and Replica Set, using two TCP ports for primary and secondary communication.

Transparent client connection routing

Load‑balancing and failover

Simple configuration

Seamless HA client routing design

Integrates InnoDB Cluster and Replica Set

MySQL Shell

Shell is a multi‑language (JavaScript, Python, SQL) interface that can perform any MySQL task, script execution, document store, relational model, and exposes full development and management APIs. It also supports the classic MySQL protocol and the X Protocol.

Supports JavaScript, Python, SQL

Scriptable

Document store and relational model

Full development and management APIs

Classic MySQL and X protocols

Using Shell, users can quickly create an InnoDB Cluster, add instances, start Router, and check cluster status. The article includes step‑by‑step screenshots for creating a cluster, configuring instances, adding nodes, starting Router, and viewing status.

MySQL InnoDB Replica Set

Released in 2020, Replica Set builds on classic replication but fully integrates Shell and Router. It offers a standard, MySQL‑team‑supported solution that is simple for beginners. Features include full Router integration, easy Shell‑driven deployment (Clone), member add/remove, and automated topology inspection via status().

Full Router integration

Easy‑to‑use Shell

Configure, add, remove members

Automatic member deployment with Clone

Supports classic master‑slave topology

Replica Set eliminates many manual steps required by traditional replication, providing automatic deployment, configuration, and monitoring.

How to Choose a High‑Availability Architecture

Selection depends on business requirements: higher availability incurs higher cost. Key criteria include Recovery Time Objective (RTO), Recovery Point Objective (RPO), failure type (HA, disaster recovery, human error), and scale (single‑region vs multi‑region).

RTO – how quickly service must recover after a failure

RPO – how much data loss is acceptable

Failure type – single‑server fault, network partition, regional outage, etc.

Scale – single‑region or multi‑region deployments

Typical mappings:

Single‑region, modest RTO (hours) and RPO (minutes): a single MySQL instance with backups.

Single‑region, low RTO (seconds) and RPO < 1 s: a single MySQL instance with frequent backups and binary‑log streaming.

Single‑region, sub‑second RTO and RPO = 0: MySQL InnoDB Replica Set.

Multi‑region, RTO of minutes and RPO of seconds: MySQL InnoDB Cluster across regions, possibly combined with asynchronous replication.

Multi‑region, RTO of minutes and RPO = 0: InnoDB Cluster with cross‑region deployment, using AFTER consistency for inter‑region groups.

Choosing the appropriate architecture allows users to balance cost, complexity, and resilience according to their specific needs.