1. Comparing MySQL High Availability (HA) Architectures
Running a single database server in production is a ticking time bomb. A spike in disk I/O, network congestion, or a routine OS restart for patching can instantly bring down your entire service. To maintain a 99.9% uptime SLA, infrastructure teams typically choose one of the following four approaches:
- DNS Failover: Assign multiple IPs to a single DNS record and switch the IP address during an outage.
- Standalone HAProxy (Single LB): Deploy a single HAProxy server in front of the MySQL cluster to load balance connections and perform health checks.
- Keepalived with HAProxy (Active-Passive with Virtual IP): Run two HAProxy nodes in parallel using VRRP (Virtual Router Redundancy Protocol) to share a Virtual IP (VIP). If the Active node fails, the VIP automatically transitions to the Passive node.
- MySQL InnoDB Cluster / Galera Cluster: A synchronous Multi-Master cluster with automated node elections managed at the database engine level.
2. Pros and Cons of Each Solution
There is no silver bullet—only the right solution for your specific use case:
DNS Failover
- Pros: Extremely fast setup with no need for complex cluster management tools.
- Cons: Highly dependent on client-side DNS caching and TTL settings. When a server goes down, it often takes 5–15 minutes (or even hours with certain ISPs) for clients to resolve the new IP, causing prolonged downtime.
Standalone HAProxy
- Pros: Intelligent routing and accurate MySQL health checks at the TCP layer (Layer 4).
- Cons: Introduces a Single Point of Failure (SPOF). If HAProxy crashes, all application connections drop even if the backend MySQL instances are fully operational.
MySQL InnoDB Cluster / Galera Cluster
- Pros: Real-time synchronous replication, ensuring strict consistency across all nodes.
- Cons: Complex to operate and resource-intensive. Prone to write conflicts and deadlocks under heavy write traffic. Requires at least three nodes to prevent split-brain scenarios.
Keepalived + HAProxy (Virtual IP)
- Pros: Lightning-fast failover (under 1–2 seconds). Completely eliminates the HAProxy single point of failure. Lightweight and seamlessly compatible with traditional MySQL Master-Replica topologies.
- Cons: The internal network infrastructure must support Multicast or Unicast traffic for VRRP to function reliably.
3. Why Keepalived + HAProxy is Ideal for Small-to-Medium Systems
For small and medium-sized workloads, the Keepalived + HAProxy setup offers the best return on investment. You avoid the overhead of managing a bulky 3-to-5 node cluster, and your application services only need to connect to a single endpoint: the Virtual IP (VIP).
The system automatically handles health monitoring, traffic rerouting, and node isolation. In real-world benchmarks handling around 1,500 QPS, this architecture achieves failover in ~800ms during an outage, completely preventing 502/504 errors on the client side.
4. Step-by-Step Implementation Guide
This hands-on setup uses two Ubuntu/Debian servers in the same local network (LAN):
- Node 1 (Primary LB / MySQL Master): IP
192.168.1.10 - Node 2 (Backup LB / MySQL Replica): IP
192.168.1.11 - Virtual IP (Shared VIP):
192.168.1.100(Applications connect to this IP)
Step 1: Create a Health Check User in MySQL
Log in to MySQL on both nodes to create a dedicated user for HAProxy health checks. This user requires no database privileges:
mysql -u root -p -e "CREATE USER 'haproxy_check'@'%' IDENTIFIED BY ''; FLUSH PRIVILEGES;"Step 2: Install HAProxy, Keepalived, and Utilities
Run the installation command on both servers:
sudo apt update
sudo apt install -y haproxy keepalived psmiscStep 3: Configure HAProxy
Open /etc/haproxy/haproxy.cfg on both nodes and configure port 3306 forwarding:
global
log /dev/log local0
log /dev/log local1 notice
chroot /var/lib/haproxy
user haproxy
group haproxy
daemon
defaults
log global
mode tcp
option tcplog
option dontlognull
retries 3
timeout connect 5000ms
timeout client 50000ms
timeout server 50000ms
frontend mysql_front
bind *:3306
mode tcp
default_backend mysql_back
backend mysql_back
mode tcp
option mysql-check user haproxy_check
server db01 192.168.1.10:3306 check inter 2000 rise 2 fall 3
server db02 192.168.1.11:3306 check backup inter 2000 rise 2 fall 3Start and enable HAProxy to launch automatically on boot:
sudo systemctl restart haproxy
sudo systemctl enable haproxyStep 4: Configure Keepalived for Virtual IP Management
First, allow the Linux kernel to bind to non-local IP addresses even before the interface acquires the VIP:
echo "net.ipv4.ip_nonlocal_bind=1" | sudo tee -a /etc/sysctl.conf
sudo sysctl -pCreate a health check script for the HAProxy process at /usr/local/bin/check_haproxy.sh on both machines:
cat << 'EOF' | sudo tee /usr/local/bin/check_haproxy.sh
#!/bin/bash
killall -0 haproxy
EOF
sudo chmod +x /usr/local/bin/check_haproxy.shConfigure Keepalived on Node 1 (Master) in /etc/keepalived/keepalived.conf:
vrrp_script check_haproxy {
script "/usr/local/bin/check_haproxy.sh"
interval 2
weight 2
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 101
advert_int 1
authentication {
auth_type PASS
auth_pass SecretHAProxyPass123
}
virtual_ipaddress {
192.168.1.100/24 dev eth0
}
track_script {
check_haproxy
}
}Configure Keepalived on Node 2 (Backup) in /etc/keepalived/keepalived.conf (set priority to 100):
vrrp_script check_haproxy {
script "/usr/local/bin/check_haproxy.sh"
interval 2
weight 2
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass SecretHAProxyPass123
}
virtual_ipaddress {
192.168.1.100/24 dev eth0
}
track_script {
check_haproxy
}
}Start and enable the Keepalived service on both machines:
sudo systemctl restart keepalived
sudo systemctl enable keepalivedStep 5: Test Real-World Failover
Verify the current IP assignments on Node 1:
ip addr show eth0You should see the Virtual IP 192.168.1.100 bound to network interface eth0 on Node 1. Next, simulate a failure by stopping the HAProxy service on Node 1:
sudo systemctl stop haproxyOpen the terminal on Node 2 and inspect its IP addresses:
ip addr show eth0The Virtual IP 192.168.1.100 has instantly shifted to Node 2. Applications querying 192.168.1.100:3306 continue operating without manual intervention or application code changes.
