How to Implement Automatic VIP Failover with Keepalived and HAProxy for MySQL

Two Modes

There are two high‑availability scenarios:

VIP failover triggered by a database crash.

VIP failover triggered by an HAProxy failure.

Both rely on a dual‑master or multi‑master database setup (MariaDB Galera, Percona XtraDB Cluster, etc.) where each node is writable.

Database Failure Triggered VIP Failover

In a dual‑master environment, adding a Keepalived layer provides load‑balanced read/write access, and the virtual IP (VIP) moves with the Keepalived state. Without Keepalived, only the database itself provides HA: when one node fails, the VIP migrates to the surviving node.

双主模式的数据库： 10.9.8.201 和 10.9.8.223
VIP 地址：10.9.8.120

Install Keepalived on both servers and use a minimal configuration that includes a VRRP script checking MySQL status.

[root@test1 keepalived]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
    router_id LVS_DB2
}

vrrp_script check_haproxy {
    script "/etc/keepalived/check_mysql.sh"
    interval 3
    weight -5
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        10.9.8.120 dev ens33
    }
    track_script {
        check_haproxy
    }
}

The check script simply verifies that the MySQL process is running; if not, it stops Keepalived to force the VIP to move.

#!/bin/bash
if [ $(ps -C mysqld --no-header | wc -l) -eq 0 ]; then
    service keepalived stop
fi

Test by stopping MySQL on the master; the VIP disappears from the master and appears on the backup, confirming failover.

HAProxy State Triggered VIP Failover

Insert HAProxy between MySQL and clients to provide TCP load balancing. Install HAProxy on both nodes with a simple configuration that listens on port 6039 and forwards traffic to the two MySQL servers.

global
    log 127.0.0.1 local2
    chroot /var/lib/haproxy
    pidfile /var/run/haproxy.pid
    maxconn 4000
    user haproxy
    group haproxy
    daemon

defaults
    mode tcp
    timeout connect 10s
    timeout client 1m
    timeout server 1m
    retries 3

frontend mysql
    bind *:6039
    default_backend back_mysql

backend back_mysql
    balance roundrobin
    server db1 10.9.8.201:3306 check
    server db2 10.9.8.223:3306 check

Modify the Keepalived script to monitor HAProxy instead of MySQL.

vrrp_script check_haproxy {
    script "/etc/keepalived/check_haproxy_status.sh"
    interval 3
    weight -5
}

#!/bin/bash
if [ $(ps -C haproxy --no-header | wc -l) -eq 0 ]; then
    sudo service keepalived stop
fi

After restarting Keepalived, the VIP will move when HAProxy stops, demonstrating that VIP failover can be driven by either database or load‑balancer health.

Source: https://www.cnblogs.com/wxzhe/p/11459777.html