DevOps

MySQL Auto Failover using Keepalived on Debian/Centos

RivenLab

RivenLab

— 3 min read
MySQL Auto Failover using Keepalived on Debian/Centos

What is Keepalived ?

Keepalived is a lightweight, high-availability framework solution for Linux. It performs load balancing and failover tasks on Linux Virtual Servers.

Keepalived can monitor systems and automatically switch to standby mode if problems arise. If the primary server fails, the floating IP will be automatically moved to the backup server, allowing the service to resume.

How Does Keepalived Work?

When the Master fails in a Master-Slave or Master-Master replication database setup, manual configuration changes are typically required to failover to the next available server.

Downtime is expected during this process because manual failover takes time.

Keepalived manages interface failover, allowing us to switch a virtual IP address to a hot standby server when the master server becomes unavailable. As a result, traffic in the production environment can be handled by the secondary server without any downtime.

Requirements

A working Master-Master or Master-Slave MySQL/MariaDB replication setup.

Note: This article uses Debian 12, but you can replicate this setup on Ubuntu or Red Hat.

Example Network Setup:

  • SRV-MYSQL01: 10.10.10.10
  • SRV-MYSQL02: 10.10.10.20
  • Virtual IP Address: 90.90.90.90

MySQL cluster KeepAlived: Configuration

Install the necessary packages on both servers:

apt install keepalived

Now, configure Keepalived on both servers:
Edit the configuration file with:

vim /etc/keepalived/keepalived.conf

Primary Server (SRV-MYSQL01 - 10.10.10.10):

global_defs {
  router_id SRV-MYSQL01
}

vrrp_script chk_mysql {
    script "/usr/bin/pgrep mariadb"
    interval 2
    weight 10
}

vrrp_instance VI_1 {
    interface ens192
    state MASTER
    virtual_router_id 50
    authentication {
        auth_type PASS
        auth_pass 1234
    }
    unicast_src_ip 10.10.10.10
    unicast_peer {
        10.10.10.20
    }
    priority 102
    track_script {
        chk_mysql
    }
    virtual_ipaddress {
        90.90.90.90/24 dev ens192
    }
}

state : MASTER or BACKUP. Master means that it is defined at its launch as the owner of the VIP, Backup that it is a backup and will therefore not come to mount the interface.

interface : the interface on which our keepalived will run

virtual_router_id : a common identifier for each peer in the keepalived cluster

priority : the initial weight given to this cluster node

advert_int : the time in seconds between each announcement to other nodes

auth_type : the authentication mode between nodes

auth_pass : a common password to validate communication

virtual_ipaddress : the famous VIP(s) that will be carried between each node

unicast_src_ip : the source address from which you send messages to other keepalived peers

unicast_peer : the nodes to communicate the cluster state to

Secondary Server (SRV-MYSQL02 - 10.10.10.20):

global_defs {
  router_id SRV-MYSQL02
}

vrrp_script chk_mysql {
    script "/usr/bin/pgrep mariadb"
    interval 2
    weight 2
}

vrrp_instance VI_1 {
    interface ens192
    state BACKUP
    virtual_router_id 50
    authentication {
        auth_type PASS
        auth_pass 1234
    }
    unicast_src_ip 10.10.10.20
    unicast_peer {
        10.10.10.10
    }
    priority 101
    track_script {
        chk_mysql
    }
    virtual_ipaddress {
        90.90.90.90/24 dev ens192
    }
}

state : We switch to BACKUP to say that we are on the "backup"

priority : we lower the weight so that the MASTER is the highest in normal times.

unicast_src_ip : we think about changing the address to our own

unicast_peer : same, we put the address of the peer(s) concerned

Don’t forget to change the network interface ens192 to match your corresponding interface.

start/enable the Keepalived service on both servers:

systemctl enable --now keepalived.service

Testing :

Check Initial Status:

On both servers, verify that the Keepalived service is running:

systemctl status keepalived.service

Check that the virtual IP is assigned to the primary server:

ip a

You must see 90.90.90.90.

Simulate Primary Server Failure:

Stop the MariaDB service on the primary server:

systemctl stop mariadb

On the secondary server see if it takes over the virtual IP:

ip a

Restore the Primary Server:

Start the MariaDB service on the primary server:

systemctl start mariadb

Check the status of Keepalived again to see if the primary server has regained the virtual IP:

ip a

Read more