Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 40 ยท Replication, Backup and Security

MariaDB Replication and Galera Cluster

Set up MariaDB primary-replica replication with the binary log and GTIDs, check lag with SHOW REPLICA STATUS, understand semi-sync and Galera multi-primary clusters and failover.

One database server is a single point of failure and a ceiling on read capacity. Replication copies changes from one server to others, so you get a hot standby, extra servers for read-heavy work (reports, search), a place to take backups without load on the primary, and a way to move between versions with little downtime.

MariaDB offers two families: classic primary-replica replication (asynchronous, built on the binary log) and Galera Cluster (synchronous-style, multi-primary). This lesson explains how each works, the commands you need, and the failure modes. Replication needs several servers, so all commands here are read-only; the stand-ins at the end model the core idea in SQL.

Primary-replica replication

flowchart LR
  A["Application"] -->|"writes"| P["Primary"]
  P -->|"binary log"| R1["Replica 1"]
  P -->|"binary log"| R2["Replica 2"]
  A -->|"reads"| R1
  A -->|"reads"| R2

Writes go to the primary and are recorded in its binary log; replicas fetch and replay them.

The pieces:

  1. The primary writes every data change to its binary log (log_bin), in a format set by binlog_format.
  2. Each replica's IO thread connects to the primary, downloads the binlog events and writes them to its relay log.
  3. The replica's SQL thread (or several worker threads) replays the relay log on its own copy of the data.

Because the copy happens after the primary commits, replication is asynchronous: a replica can be seconds (or more) behind, and if the primary dies right after a commit, the last transactions may not have reached any replica.

Binary log formats

binlog_format Logs Notes
STATEMENT the SQL text small, but non-deterministic functions (UUID(), NOW() in some cases, LIMIT without ORDER BY) can diverge
ROW the changed rows safest and the default in current releases; larger logs, readable with mariadb-binlog -vv
MIXED statement when safe, row otherwise compromise

Use ROW unless you have a reason not to.

GTIDs

A global transaction ID such as 0-1-2345 (domain, server id, sequence) names a transaction uniquely across the topology. With GTIDs a replica says "I have everything up to this ID" and the new primary can continue from exactly that point, so failover does not need binlog file names and positions.

Setting up a replica

On the primary (my.cnf):

INI
[mysqld]
server_id     = 1
log_bin       = mariadb-bin
binlog_format = ROW

Create a replication account and take a consistent copy (see backup and restore). On the replica (server_id = 2, restore the copy first):

SQL
CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY '...';
GRANT REPLICATION REPLICA ON *.* TO 'repl'@'10.0.0.%';     -- 10.5+; older: REPLICATION SLAVE

-- on the replica
CHANGE MASTER TO
  MASTER_HOST = '10.0.0.10',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = '...',
  MASTER_USE_GTID = slave_pos,
  MASTER_SSL = 1;
START REPLICA;                       -- 10.5.1+; older: START SLAVE
SET GLOBAL read_only = ON;           -- protect the replica from stray writes

Check health:

SQL
SHOW REPLICA STATUS\G         -- SHOW SLAVE STATUS in older versions

Look at Replica_IO_Running and Replica_SQL_Running (both must be Yes), Seconds_Behind_Master (lag; NULL means a thread is not running), Last_IO_Error, Last_SQL_Error and Gtid_IO_Pos. On the primary, SHOW BINARY LOG STATUS (or SHOW MASTER STATUS in older versions) shows the current binlog position.

Typical problems:

  • Lag: one long transaction, a replica with slower disks, or single-threaded apply. Fix with slave_parallel_threads (parallel replication), better hardware, smaller transactions.
  • Errors: a duplicate key or missing row on the replica means the data diverged (someone wrote to the replica, or statement-based replication hit non-determinism). Resync from a backup; skipping errors is a last resort.
  • Read-your-writes: right after a write, a read from a lagging replica may not see it. Route such reads to the primary, or wait with MASTER_GTID_WAIT('0-1-2345', 1) (and BINLOG_GTID_POS()) on the replica.

Semi-synchronous replication

With the semi-sync plugin the primary waits until at least one replica has received (not applied) the event before acknowledging the commit. It narrows the window of lost transactions at a small latency cost, and falls back to asynchronous on timeout (rpl_semi_sync_master_timeout). In MariaDB 10.3+ semi-sync is built in: SET GLOBAL rpl_semi_sync_master_enabled = ON;.

Delayed and multi-source replication

MASTER_DELAY = 3600 makes a replica deliberately one hour behind, a cheap safety net against an accidental DROP TABLE (you can stop it before the bad statement applies). Multi-source replication lets one replica pull from several primaries, which is handy for consolidating data for reporting: CHANGE MASTER 'shop' TO ....

Galera Cluster

Galera (MariaDB Galera Cluster, built into the server) uses virtually synchronous replication: every node holds a full copy and any node accepts writes.

flowchart TB
  C["Clients via load balancer"] --> N1["Node 1"]
  C --> N2["Node 2"]
  C --> N3["Node 3"]
  N1 <-->|"certification-based replication"| N2
  N2 <-->|"certification-based replication"| N3
  N1 <-->|"certification-based replication"| N3

Every node is writable; a commit is certified by the whole cluster before it succeeds.

How a write works: when a transaction commits, its write set is broadcast to all nodes. Every node runs the same deterministic certification test: does this transaction conflict with another one that committed in the meantime? If not, it is applied everywhere; if it conflicts, the later one is rolled back and the client receives a deadlock error (1213) it should retry.

Consequences:

  • No replica lag for committed data and no data loss when a node dies; any node can serve reads and writes.
  • Writes are as slow as the slowest node plus network latency, so keep nodes in one datacenter or region.
  • Needs an odd number of nodes (3 or more) or an arbitrator (garbd) so a quorum (majority) exists; a minority partition stops serving to avoid split brain.
  • Only InnoDB tables are replicated reliably; tables need primary keys.
  • Hot rows (the same row updated from different nodes) cause certification conflicts: route writes for one data set to one node through the load balancer (a "single writer" setup) for most applications.
  • New or returning nodes join using SST (full snapshot transfer, with mariabackup) or IST (incremental, from the gcache).

Key settings (galera.cnf):

INI
[galera]
wsrep_on                 = ON
wsrep_provider           = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_address    = gcomm://10.0.0.11,10.0.0.12,10.0.0.13
wsrep_cluster_name       = shop_cluster
wsrep_sst_method         = mariabackup
binlog_format            = ROW
innodb_autoinc_lock_mode = 2

Check health with SHOW STATUS LIKE 'wsrep_%';: wsrep_cluster_size, wsrep_cluster_status (should be Primary), wsrep_local_state_comment (Synced), wsrep_ready.

Choosing

Need Choice
Read scaling, backups, simple standby primary-replica
No-loss failover, any-node writes, same site Galera
Cross-region disaster recovery async replica (a Galera cluster can also be a primary for an async replica)
Proxy / automatic failover MaxScale (read-write split, failover, monitoring)

Replication is not a backup: a bad DELETE replicates instantly to every node. Keep real backups and perhaps a delayed replica.

Modelling replication in SQL

You cannot run two servers in the browser, but the mechanism is easy to model: the primary appends changes to a log, and a replica replays them in order and remembers how far it got. This is exactly what the binlog position or GTID does.

SQL A change log replayed on a replica

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

(A real replica applies every event strictly in log order; this model groups the operations for brevity. Because it records applied_pos, running it again does nothing: that idempotence is what GTID-based failover relies on.)

Checking that two copies match, as pt-table-checksum or CHECKSUM TABLE do, compares an aggregate of both sides:

SQL Compare two copies with a checksum

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

Common mistakes

  • Treating a replica as a backup.
  • Letting applications write to a replica (read_only does not stop accounts with the SUPER privilege, so never give applications that privilege).
  • Reading from a lagging replica right after a write.
  • Running Galera with two nodes and no arbitrator.
  • Large single transactions: they delay replication and increase Galera conflicts.
  • Tables without a primary key: slow and unsafe with row-based replication.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which thread on a replica downloads events from the primary into the relay log?

    The IO thread fetches events; the SQL thread (or workers) applies them from the relay log.
  2. 2. What does a GTID give you in practice?

    GTIDs identify transactions across servers, which simplifies failover and re-pointing replicas.
  3. 3. Why does Galera recommend an odd number of nodes?

    With an even split, neither half has a majority; a quorum rule keeps the cluster consistent.
  4. 4. A transaction conflicts with another one during Galera certification. What happens?

    Certification is deterministic on every node; the losing transaction receives error 1213.

Summary

  • Primary-replica replication ships the binary log to replicas: asynchronous, easy, great for read scaling and standby; use ROW format and GTIDs.
  • Monitor with SHOW REPLICA STATUS; watch lag and errors; semi-sync reduces loss risk.
  • Galera gives multi-primary, no-lag replication with certification and quorum, best within one site, with retry logic in the application.
  • Replication is not a backup.

Next: Backup and Restore