How to Turn a Single MySQL Server into a High‑Availability Cluster

Why scale a single MySQL instance?

When data volume, read/write concurrency, and availability requirements grow, a single MySQL server hits capacity limits, experiences high read pressure, and becomes a single point of failure. Typical remedies are:

Horizontal sharding (split databases/tables)

Read/write separation via master‑slave replication

Automatic failover and disaster‑recovery mechanisms (MHA, MySQL Group Replication, Orchestrator, etc.)

Read/Write Separation

In high‑concurrency workloads, directing all writes to a master and distributing reads across one or more slaves reduces read latency, increases QPS, and improves data safety. If any node fails, the remaining nodes still hold a full copy of the data, preventing loss.

MySQL Master‑Slave Replication Basics

The master writes all data‑changing statements to the binary log (binlog). Each slave runs an I/O thread that streams the master’s binlog to a local relay log, then a SQL thread replays the events from the relay log, keeping the slave’s data identical to the master.

Historical milestones

2000 – MySQL 3.23.15 introduced basic replication.

2002 – MySQL 4.0.2 split I/O and SQL threads and added the relay log.

2010 – MySQL 5.5 added semi‑synchronous replication.

2016 – MySQL 5.7.17 introduced InnoDB Group Replication.

Binlog formats

ROW – logs each row change; most detailed but produces larger logs.

STATEMENT – logs only the SQL statement; smaller logs but can cause inconsistencies with non‑deterministic statements.

MIXED – MySQL chooses ROW or STATEMENT per statement.

Replication modes

Asynchronous replication – the master commits locally and sends the transaction to slaves afterwards. Simple to configure but vulnerable to data loss if the master crashes before slaves receive the events.

Semi‑synchronous replication – introduced in MySQL 5.5. The master waits for at least one slave to acknowledge receipt of the transaction before committing. Requires the semisync_master and semisync_slave plugins.

# Enable plugins (run on master and each slave)
INSTALL PLUGIN semisync_master SONAME 'semisync_master.so';
INSTALL PLUGIN semisync_slave  SONAME 'semisync_slave.so';
# Turn on semi‑sync on master
SET GLOBAL rpl_semi_sync_master_enabled = 1;
# Turn on semi‑sync on slaves
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

Group Replication (MGR) – built on the Paxos consensus algorithm. All members hold a full copy of the data, transactions are broadcast, globally ordered, and certified. Conflicts are resolved by “first‑committed wins” and the loser is rolled back. Supports single‑master and multi‑master modes, automatic member management, and fault tolerance.

# Example: create a group replication group (MySQL 5.7+)
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

Drawbacks of classic master‑slave

Replication lag – reads from a slave may not see the most recent writes.

Potential data loss if the master crashes before the transaction is persisted on a slave.

Common mitigations:

Sharding to reduce per‑node write load.

Parallel (library‑level) replication – multiple SQL threads replay different databases in parallel (MySQL 5.6+).

Refactor application code to avoid read‑after‑write patterns or route such reads to the master.

Application‑side read/write splitting

Two typical implementation styles:

Configure multiple data sources (e.g., master and slave) in Spring or Spring Boot and inject the appropriate one based on whether the service method performs a write.

Use a library that parses SQL at runtime and routes reads/writes automatically. ShardingSphere‑JDBC provides a master‑slave plugin that:

Analyzes each SQL statement.

Ensures that any transaction containing a write forces all subsequent reads to the master, eliminating the “write‑then‑read” inconsistency.

Supports multiple slaves with load‑balancing and failover.

Database middleware options

MyCat / ShardingSphere‑Proxy – act as a MySQL proxy, handling routing without code changes. They require a separate deployment and configuration but keep the application unchanged.

High‑availability and failover solutions

Various strategies to achieve automatic failover, reduce RTO (recovery time objective) and RPO (recovery point objective):

Manual master‑slave switch – promote a slave to master and reconfigure others. Simple but requires human intervention and risks inconsistency.

LVS + Keepalived – use a virtual IP (VIP) or DNS to hide the master’s address. Scripts monitor health and move the VIP on failure. Still semi‑automatic and script‑heavy.

MHA (Master High Availability) – a Perl‑based tool that detects master failure, promotes the most up‑to‑date slave, and copies missing binlog entries via SSH. Typical promotion time < 30 seconds. Requires at least three nodes and SSH key configuration.

# Example MHA configuration (mha.cnf)
[server default]
    manager_log=/var/log/masterha/manager.log
    repl_user=mha
    repl_password=******
[server1]
    hostname=10.0.0.1
    port=3306
[server2]
    hostname=10.0.0.2
    port=3306
[server3]
    hostname=10.0.0.3
    port=3306

MySQL Group Replication (MGR) – built‑in automatic failover. When the primary crashes, a secondary is elected as the new primary without external tools. Requires MySQL 5.7+ and the group_replication plugin.

# Enable group replication on each instance
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SET GLOBAL group_replication_bootstrap_group=ON;   -- only on the first node
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

MySQL InnoDB Cluster – combines Group Replication, MySQL Router (lightweight load‑balancer/failover), and MySQL Shell for provisioning and management.

# Create an InnoDB Cluster via MySQL Shell (JS mode)
var c = dba.createCluster('myCluster');
c.addInstance('[email protected]');
c.addInstance('[email protected]');
c.status();

Orchestrator – a Go‑based topology manager with a web UI and HTTP API. It discovers replication topology, allows manual or automatic failover, and can be deployed with its own Raft consensus for high availability.

# Example API call to check the current primary
curl http://orchestrator-host:3000/api/leader-check

Orchestrator deployment modes

Orchestrator/raft – data consistency is guaranteed by the internal Raft protocol.

Orchestrator + external cluster (Galera, XtraDB, InnoDB Cluster) – consistency is delegated to the underlying database cluster.

Choosing a solution

If the workload is read‑heavy and occasional stale reads are acceptable, enable parallel replication (MySQL 5.7+) and route reads to slaves.

For strict consistency, use semi‑synchronous replication or Group Replication.

When zero‑downtime failover is required, prefer InnoDB Cluster or Orchestrator with Raft.

For legacy applications that cannot be modified, deploy a proxy such as ShardingSphere‑Proxy or MyCat.

Key commands and configuration snippets

# View a binlog file (useful for debugging)
mysqlbinlog -vv mysql-bin.000005

# Enable semi‑sync on master and slaves (MySQL 5.5+)
INSTALL PLUGIN semisync_master SONAME 'semisync_master.so';
INSTALL PLUGIN semisync_slave  SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

# Configure a simple master‑slave topology (my.cnf excerpts)
[mysqld]
server-id=1               # master
log_bin=mysql-bin
binlog_format=row

[mysqld]
server-id=2               # slave
relay_log=relay-bin
log_slave_updates=1
read_only=1

# Change master on the slave
CHANGE MASTER TO MASTER_HOST='10.0.0.1', MASTER_USER='repl', MASTER_PASSWORD='******', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=4;
START SLAVE;

Conclusion

Scaling MySQL from a single instance to a robust cluster involves three intertwined layers:

Data distribution – sharding or partitioning to keep per‑node write volume manageable.

Read/write separation – master‑slave or Group Replication to increase read throughput while preserving data safety.

High‑availability – automatic failover tools (MHA, InnoDB Cluster, Orchestrator) that minimize downtime and data loss.

Choosing the right combination depends on consistency requirements, operational complexity, and existing application architecture.