Replicating MySQL databases

SECTION 1

Why and how to replicate a MySQL database

Replicating a MySQL database means copying its data to one or more other servers, in order to gain availability and better spread the load. Depending on the goals pursued, several strategies exist, each with its advantages and limits.

Several main methods stand out: master-slave replication, master-master replication, Group Replication, and more integrated architectures such as InnoDB Cluster or sharding with Vitess. The right choice depends on the need to scale reads, writes, automate failover in the event of a failure, or spread the database across several geographic sites.

This choice is not just an isolated technical decision: it shapes how teams operate the database day to day, the monitoring tooling to put in place, and the level of in-house skill needed to diagnose a replication incident in the middle of the night. A topology too sophisticated for the team that has to run it quickly becomes a risk in itself, however robust it looks on paper: the best architecture is the one the organisation is able to understand, monitor and fix under pressure, not necessarily the most technically advanced one.

SECTION 2

Master-slave replication

This is the founding method. A single master server receives all read and write requests, while at least one read-only slave server receives the data asynchronously. Distributing requests between master and slaves improves overall performance, with no performance restriction on the server.

The downside lies in the asynchronous nature of the replication, which reduces reliability: if the master fails, transactions bound for the slave may be lost. Scaling writes also requires increasing the master node resources, and failover remains a manual process.

This pattern nonetheless remains widely used for specific purposes: isolating reporting or analytics queries on a dedicated slave, without impacting the performance of application transactions on the master, or providing a read-only database to services that do not need to write. It is often the first level of replication put in place before more elaborate architectures are considered.

SECTION 3

Master-master replication

This more advanced approach requires at least two master nodes, each handling reads and writes, with asynchronous replication between them. It makes it possible to scale writes by adding masters and strengthens redundancy; having several masters reduces the risk of simultaneous failure and makes failover semi-automatic.

Its limits remain close to those of master-slave: transactions may be lost when a master fails, and backup data may be inconsistent from one node to another. Failover often calls for semi-manual intervention, which may promote a slave node. The risk most specific to master-master remains concurrent writes to the same row from two different masters: without clear application-level discipline, for instance by systematically routing writes for a given key to the same master, these conflicts can produce silent divergence between nodes.

@cite:patterns-haute-disponibilite-postgresql

SECTION 4

GTIDs and semi-synchronous replication: hardening the chain

### The role of GTIDs in transaction traceability

GTIDs (Global Transaction Identifiers) assign a unique identifier to every committed transaction, even across multiple servers and multiple generations of topology. This traceability considerably simplifies incident recovery: instead of manually searching for a precise position in a binlog file and a byte offset, the administrator simply asks the server to catch up on missing transactions by identifier, which greatly reduces the risk of human error during a failover under pressure. GTIDs are now a de facto prerequisite for most topology automation tools, since they allow a consistent replication chain to be rebuilt without knowing the full history of every node.

### Semi-synchronous replication to shrink the loss window

Purely asynchronous replication offers no guarantee that a transaction committed on the master has actually reached a slave before a failure. Semi-synchronous replication closes part of that gap: the master waits for at least one slave to acknowledge the transaction before confirming the write to the client, which shrinks the data-loss window without imposing the latency cost of fully synchronous replication. This trade-off makes it a reasonable default for most critical architectures, which can afford neither the slowness of strict synchronous replication nor the risk of a fully asynchronous setup. In practice, semi-synchronous replication is often paired with GTIDs to get both a minimal durability guarantee and simplified incident recovery, which is why this combination has become the recommended baseline even before considering Group Replication.

SECTION 5

Group Replication and best practices

### The Group Replication plugin

Group Replication is a feature provided as a MySQL Server plugin. Based on a distributed state machine architecture, it creates a fault-tolerant system with automatic conflict resolution: the system stays available despite the failure of a minority of nodes, failover is automatic and a replacement master is elected by the group. It allows scaling of both reads and writes with no performance limitation, but is capped at nine nodes and exclusive to MySQL, so unavailable for forks such as Percona or MariaDB, a limit still in force with MySQL 9.7 LTS, the current long-term support release.

### Operational best practices

A few best practices apply. Large updates should be avoided during replication: batch jobs generate excessive activity that stalls the streams, hence the value of parallel replication. In-memory, non-persistent tables should also be avoided, as they lose their data when MySQL restarts and cause replication errors; the fix is to copy that data and switch to InnoDB. Finally, working with experts prevents costly mistakes.

@cite:database-as-a-service-dbaas

SECTION 6

InnoDB Cluster, ClusterSet and scaling out with Vitess

### InnoDB Cluster and ClusterSet for disaster recovery

InnoDB Cluster assembles Group Replication, the MySQL Router and the MySQL Shell administration tools into an integrated high-availability solution, targeting zero data loss (RPO=0) with a typical recovery time of thirty to sixty seconds. InnoDB ClusterSet goes further by linking a primary cluster to one or more replicas located in other data centres, with dedicated replication between clusters: in the event of a regional disaster affecting the primary site, failing over to a replica cluster becomes possible without rebuilding the whole topology from scratch.

### Vitess and horizontal sharding for scale

When data volume or write throughput exceeds what a single MySQL cluster can absorb, even with multiple masters, horizontal sharding becomes necessary. Vitess, used notably by platforms such as YouTube, Slack or Pinterest at very large scale, spreads data across many MySQL shards while exposing a single interface to applications, hiding the complexity of routing queries to the right shard. This approach addresses a different need from classic high availability: it targets horizontal scalability rather than fault tolerance alone, and is mainly aimed at organisations whose growth outpaces what vertical scaling of hardware can still absorb.

### Orchestrator, ProxySQL and replication observability

Whatever architecture is chosen, its reliability in production depends as much on the operational tooling as on the topology itself. Tools such as Orchestrator for failure detection and automatic promotion, or ProxySQL for transparent routing of read and write queries, usefully complement Group Replication or a classic master-slave topology. MySQL 9.7 LTS has, moreover, enriched the native replication observability capabilities in the Community edition, with more metrics and better visibility into Group Replication behaviour, reducing reliance on third-party tools for day-to-day diagnostics.

SECTION 7

The Adservio approach

At Adservio, we choose a replication method based on what the architecture must genuinely guarantee: read distribution, write scaling, failover automation or horizontal scalability at very large scale. No method is universal; each imposes its own trade-offs between reliability, consistency and operational complexity. We systematically assess the operational maturity of the team that will run the solution day to day before recommending an architecture, because a topology the team cannot master in-house always ends up costing more than the availability gain it promised.

Our conviction: robust replication is designed upstream, taking into account common pitfalls such as batch jobs or non-persistent tables, and building on solid foundations such as GTIDs and semi-synchronous replication rather than patches bolted on after a first incident. We support your teams in choosing and setting up the right strategy, from auditing the existing setup through to running it in production, then transfer them the know-how to run their MySQL databases sustainably and independently.

FAQ

Frequently asked questions