๐️ MySQL / MariaDB Active-Active High Availability: Complete Guide to Three-Node Galera Cluster with Multi-Master Sync + Automatic Failover
Traditional Master/Slave MySQL replication often suffers from slow failover and data inconsistency. For modern high-availability requirements, enterprises need a database solution that ensures continuous service, zero data loss, and multi-node write capability.
This guide walks you through building a true Active-Active Multi-Master database architecture using MariaDB Galera Cluster — a synchronous replication cluster that ensures real-time consistency and automatic failover.
๐ 1. Why Galera Cluster? (True Multi-Master)
Galera provides real synchronous (no lag) multi-master replication with:
- ✔ Active-Active multi-node write support
- ✔ Zero replication lag with synchronous writes
- ✔ Built-in quorum to prevent split-brain
- ✔ Automatic node healing with IST/SST
- ✔ Perfect for high-availability workloads
We use the recommended 3-node setup:
node1: 192.168.1.10
node2: 192.168.1.11
node3: 192.168.1.12
๐ก 2. Three-Node Cluster Architecture
┌────────────────────────┐
│ HAProxy + VIP │
│ (Read/Write LB Tier) │
└──────────┬──────────────┘
│
┌──────────────┴──────────────┐
│ │
┌────────┴────────┐ ┌────────┴────────┐
│ MariaDB Node 1 │ │ MariaDB Node 2 │
│ 192.168.1.10 │ │ 192.168.1.11 │
└────────┬─────────┘ └────────┬────────┘
│ │
└──────────────┬──────────────┬────┘
│ │
┌──────┴──────┐
│ MariaDB Node 3 │
│ 192.168.1.12 │
└──────────────┘
๐ 3. Install MariaDB + Galera (All Nodes)
sudo dnf install mariadb mariadb-server galera -y
sudo systemctl enable mariadb
⚙️ 4. Galera Configuration (All Nodes)
Edit /etc/my.cnf.d/galera.cnf:
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name="galera_cluster"
wsrep_cluster_address="gcomm://192.168.1.10,192.168.1.11,192.168.1.12"
wsrep_node_address="192.168.1.X"
wsrep_node_name="nodeX"
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
Each node must have a unique wsrep_node_address and wsrep_node_name.
๐ 5. Start the Cluster (Correct Sequence)
1️⃣ Bootstrap the first node:
galera_new_cluster
2️⃣ Start the remaining nodes:
sudo systemctl start mariadb
Verify cluster size:
mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size';"
3
๐ 6. Replication Type: SST vs IST
- SST (State Snapshot Transfer) — full resync for new/empty nodes
- IST (Incremental State Transfer) — quick catch-up for nodes that briefly went offline
๐งฉ 7. Quorum & Split-Brain Protection
3 nodes → Full cluster
2 nodes → Safe (majority)
1 node → Cluster auto-stops (to protect consistency)
⚖️ 8. Load Balancing with HAProxy
Install HAProxy:
sudo dnf install haproxy -y
Configure:
/etc/haproxy/haproxy.cfg
frontend mysql-in
bind *:3306
mode tcp
default_backend galera
backend galera
mode tcp
balance roundrobin
option mysql-check user haproxy
server node1 192.168.1.10:3306 check
server node2 192.168.1.11:3306 check
server node3 192.168.1.12:3306 check
๐ท 9. Automatic Failover with Keepalived + VIP
Keepalived ensures the HAProxy tier remains highly available.
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 50
priority 120
virtual_ipaddress {
192.168.1.200
}
}
Clients always connect via VIP:
mysql -h 192.168.1.200 -u user -p
๐ฆ 10. Node Failure & Recovery
๐ก Node goes offline → other nodes continue running
๐ข Node comes back → automatic IST resync
๐ด Severe desync → SST rebuild
๐ Conclusion
A three-node MariaDB Galera Cluster delivers true Active-Active architecture with:
- Multi-node read/write capability
- Guaranteed strong consistency
- Zero downtime failover with HAProxy + VIP
- Automatic healing and node recovery
This setup is suitable for enterprise databases, APIs, backend systems, and high-traffic platforms.
๐ Related Articles
- Linux Firewall Integration: firewalld / iptables / nftables
- OpenVPN + PKI Client Routing Guide
- Complete Linux NFS Deployment Guide
- Linux LVM PV / VG / LV Resize Guide
— WWFandy • Database & System Notes
ๆฒๆ็่จ:
ๅผต่ฒผ็่จ