MySQL Replication Types: Pros and Cons

MySQL Replication Types: Pros and Cons

You manage a critical MySQL database. Your infrastructure needs high availability. But which replication strategy should you choose?

There are four main approaches:

• Master-Slave (simple, unidirectional replication)
• Master-Master (bidirectional replication)
• MySQL Group Replication (native synchronous replication)
• Galera Cluster (distributed clustering)

Each has strengths and weaknesses. Each has different operational costs.

This article compares these four options and shows how to deploy them on Kubernetes with Hikube.

Master-Slave: Simple but Risky

Principle: One primary server (master) receives writes. Secondary servers (slaves) replicate data asynchronously.

Advantages

• Simplicity: Easy to set up. One master, N slaves. Straightforward configuration.
• Read scalability: Distribute reads across multiple slaves. If you have 100 reads/sec, distribute them across 3-5 slaves.
• Low cost: Minimal overhead. Asynchronous replication doesn't consume many resources.
• Flexibility: You can stop a slave for backup without impacting others.

Disadvantages

• Asynchronous replication: Between the moment the master writes and the slave replicates, there's a delay (milliseconds to seconds). If the master crashes during this delay, unreplicated writes are lost.
• No automatic failover: If the master crashes, someone must manually promote a slave to master. This takes time and can create confusion (which slave was most up-to-date?).
• No write scalability: All writes go to the master. You can't distribute writes across multiple servers.
• Single point of failure: Master down = no writes possible, even if slaves are alive.

Appropriate use cases

• Applications with few critical writes
• High tolerance for minor data loss (a few seconds)
• Teams without database HA expertise
• Very tight budgets

Master-Master: Bidirectional Replication

Principle: Two masters replicate to each other. Writes can go to either one.

Advantages

• Semi-automatic failover: If master-1 crashes, applications can redirect to master-2 without manual intervention.
• Horizontal write scalability: You can write to master-1 OR master-2. Potentially doubles write capacity.
• Active-active high availability: Both servers are active. Neither is standby.

Disadvantages

• Replication conflicts: If you write the same record to both masters simultaneously (same ID), what happens? Asynchronous replication = potential chaos. You need conflict resolution strategies (last write wins, custom handlers, etc.).
• Temporary inconsistency: Because replication is asynchronous, master-1 and master-2 can temporarily have different data.
• Increased complexity: Debugging becomes harder. Are the data inconsistencies due to an application bug or a replication problem?
• Complicated backups: Creating a consistent backup of two servers replicating to each other is very difficult. You risk backing up an inconsistent state.

Appropriate use cases

• Applications tolerant of eventual consistency
• Multi-region scenarios (write locally, replicate globally)
• Cache systems where data loss is acceptable
• Architectures where manual failover is unacceptable

MySQL Group Replication: Synchronous and Automatic

Principle: MySQL Group Replication (MGR) is a native MySQL plugin that manages synchronous replication. Multiple servers in a group. Writes are replicated synchronously before being committed.

Advantages

• Synchronous replication: Writes are only committed when the majority of the group confirms. Zero data loss.
• Automatic failover: If a node crashes, the group expels it and elects a new coordinator. Applications see near-instant failover.
• Minimal nodes required: You need only 2 nodes for a quorum (minimum 3 for resilience, but technically 2 suffice).
• Built into MySQL: No external software. No external dependencies to manage.

Disadvantages

• MySQL only: MGR only works on MySQL. If you use MariaDB or Percona, it's not available.
• Limited to 9 nodes: Beyond 9 nodes, performance degrades significantly because every write must be synchronized by all 9 nodes.
• Synchronous replication overhead: Writes are slower because they wait for group confirmation. Expect 10-50ms additional latency per write.
• No horizontal read scaling advantage: Unlike Master-Slave, you don't get significant benefit from adding read-only nodes. All nodes are equal.

Appropriate use cases

• Applications requiring complete consistency
• Environments where automatic failover is critical
• Kubernetes deployments (MGR adapts well to StatefulSets)
• Organizations using MySQL (not MariaDB)

Galera Cluster: Distributed Clustering

Principle: Galera Cluster uses the Galera protocol for synchronous replication. Compatible with MariaDB and MySQL (via Percona XtraDB Cluster).

Advantages

• True synchronous replication: Like MGR, but implemented differently. Zero data loss.
• True clustering: All nodes are equal. You can write to any of them. Failover is default.
• Automatic quorum: If you lose the majority of nodes, the cluster stops to prevent divergences (split-brain).
• MariaDB compatible: Works with MariaDB AND MySQL (via Percona).

Disadvantages

• Performance overhead: Galera is more demanding than Master-Slave. Expect 20-40% performance degradation compared to non-clustered MySQL.
• Long transaction limitations: Transactions lasting longer than 1-2 seconds can block the clustering mechanism.
• Not with native MySQL: Galera works with MariaDB and Percona XtraDB Cluster (MySQL fork). Not with vanilla MySQL.
• Licensing costs: Percona XtraDB Cluster has commercial versions with support.

Appropriate use cases

• Organizations using MariaDB
• Absolute need for automatic failover AND write scalability
• Small clusters (3-5 nodes)
• Highly resilient infrastructure requirements

Summary Comparison

MySQL HA on Kubernetes: The Modern Approach

The traditional approach (managing baremetal HA) is increasingly replaced by Kubernetes deployment. Why?

Kubernetes + MySQL = Operational Simplicity

Deploying MySQL HA on Kubernetes (via Hikube) offers several advantages:

1. Automatic Orchestration

MySQL Operators (like Oracle MySQL Operator or Percona Operator) automate:

• Deploying MySQL nodes
• Configuring replication (automatically)
• Automatic failover on crash
• Scaling (adding nodes = one Kubernetes patch)

apiVersion: mysql.oracle.com/v1
kind: MySQLCluster
metadata:
  name: my-mysql-ha
spec:
  instances: 3
  replication:
    type: Group Replication
  storage:
    size: 100Gi
  backup:
    enabled: true
    schedule: "0 2 * * *"

2. StatefulSets and Persistent Storage

Kubernetes StatefulSets manage deployment ordering and stable naming. Combined with persistent volumes, MySQL HA becomes as simple as a stateless application.

3. Built-in Backups

Operators often include automatic backups and recovery strategies.

4. Integrated Monitoring

Prometheus scrapes MySQL metrics. Alertmanager notifies you. Everything integrated into your observability stack.

Hikube and MySQL HA

On Hikube (Kubernetes managed by Hidora), deploying MySQL HA is simplified:

• Swiss infrastructure: Your data stays in Switzerland
• Managed persistent storage: Resilient disks, automatic snapshots
• Backups and DR: Included in Hikube management
• Native monitoring: Prometheus/Grafana integration
• Easy scaling: Increase nodes with kubectl

Example: Creating a 3-node MySQL cluster with MGR on Hikube takes ~15 minutes. The same setup on baremetal can take 2-3 days of configuration.

Which Strategy to Choose?

For a standard web application (reads > writes)

Recommendation: Master-Slave

• Deploy 1 master + 2-3 slaves
• Use a load balancer routing reads to slaves and writes to master
• ProxySQL in front for intelligent routing
• Cheaper, less complex, perfect for 80% of applications

For an application with distributed writes (multi-region)

Recommendation: Master-Master or MGR

• If you use vanilla MySQL: MGR on Hikube
• If you use MariaDB: Master-Master (simpler)
• Automatic failover at zone-level

For mission-critical zero-downtime absolute requirement

Recommendation: MySQL Group Replication on Hikube

• 3+ nodes across 3 different availability zones
• Quorum = zero data loss
• Automatic failover = RTO of seconds
• Operationally: Orchestrator + Hikube monitoring

For MariaDB with extreme resilience needs

Recommendation: Galera Cluster

• More complex to operate, but truly distributed
• True multi-master write scaling
• Best choice for critical MariaDB

ProxySQL: The Intelligent Router

Whatever your strategy, you'll need a MySQL connection router.

ProxySQL:

• Routes reads to slaves, writes to master (for Master-Slave)
• Load balances connections
• Handles graceful failovers
• Compatible with all replication types

On Hikube, deploying ProxySQL in front of your MySQL cluster takes 2-3 minutes.

Orchestrator: HA Management

For complex deployments, Orchestrator automates:

• Crash detection
• Automatic slave promotion
• Topology rebalancing

Included in Hidora managed services offerings.

In Summary

MySQL replication is no longer a purely technical decision. It's an operational and cost decision.

Master-Slave costs less but requires manual intervention in case of incidents. MGR and Galera cost more but eliminate most manual interventions.

On Kubernetes via Hikube, the cost difference decreases because orchestration is already managed.

Managing a critical database? Hidora offers MySQL HA services on Hikube: Managed Services · Database Consulting · Kubernetes Infrastructure