MySQL Auto Failover using Keepalived

Keepalived is a routing software written in C. It provide simple and robust facilities for loadbalancing and high-availability to Linux system. Loadbalancing framework relies on well-known and widely used Linux Virtual Server (IPVS) kernel module providing Layer4 loadbalancing. Keepalived implements a set of checkers to dynamically and adaptively maintain and manage loadbalanced server pool according their health. On the other hand high-availability is achieved by VRRP protocol. VRRP is a fundamental brick for router failover.

MySQL master slave replication doesn’t provide any solution in case of master failure, in that scenarios we have to manually do the configuration changes to make the next available server as master. When this happens, downtime is expected.

Keepalived is the utility that provide interface failover. With good implementation, you will be able to failover  a virtual ip, when the master server is unavailable.

MySQL Health Check

Create a MySQL health check script that continuously monitor the health of MySQL Server. This script can be setup on both Master and Slave server.

root@db03:~# cat /usr/local/script/mysql_health_check.sh
#!/bin/bash

# MySQL Credentials
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USERNAME="root"
MYSQL_PASSWORD="XXXXXXXXXXXX"

mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USERNAME  -p$MYSQL_PASSWORD --connect_timeout=5 -Bse 'SELECT version();'

if [ $? -ne 0 ]; then
  echo "bad"
  exit 1      # mysql node is bad
fi

echo "good"
exit 0        # mysql node is good
root@db03:~#

root@db03:~# /usr/local/script/mysql_health_check.sh
mysql: [Warning] Using a password on the command line interface can be insecure.
5.7.26-29
good
root@db03:~#

Keepalived Configuration for Master Server

Keepalived mysql master server configuration show below. The master server has a higher priority.

root@db03:~# cat /etc/keepalived/keepalived.conf
global_defs {
  notification_email {
    krishna.prajapati@xxxxxxxxxxxxxx.xx
    root@localhost
  }
  router_id HAPROXY
}

vrrp_script chk_mysql {
  script   "/usr/local/script/mysql_health_check.sh"
  interval 2
  weight   2
}

vrrp_instance HA1 {
  virtual_router_id 51
  advert_int 1
  priority 102
  state MASTER
  interface enp0s8
  unicast_src_ip 192.168.56.103    # IP address of Master Server
  unicast_peer {
    192.168.56.104                 # IP address of Slave Server
  }
  virtual_ipaddress {
    192.168.56.110/24 dev enp0s8
  }
  track_script {
    chk_mysql
  }
}

root@db03:~#

root@db03:~# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 08:00:27:09:34:73 brd ff:ff:ff:ff:ff:ff
    inet 10.0.2.15/24 brd 10.0.2.255 scope global dynamic enp0s3
       valid_lft 70711sec preferred_lft 70711sec
    inet6 fe80::a00:27ff:fe09:3473/64 scope link
       valid_lft forever preferred_lft forever
3: enp0s8: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 08:00:27:d3:33:97 brd ff:ff:ff:ff:ff:ff
    inet 192.168.56.103/24 brd 192.168.56.255 scope global enp0s8
       valid_lft forever preferred_lft forever
    inet 192.168.56.110/24 scope global secondary enp0s8
       valid_lft forever preferred_lft forever
    inet6 fe80::a00:27ff:fed3:3397/64 scope link
       valid_lft forever preferred_lft forever
root@db03:~#

Keepalived Configuration for Slave Server

Keepalived mysql slave server configuration show below.

root@ubuntu:~# cat /etc/keepalived/keepalived.conf
global_defs {
  notification_email {
    krishna.prajapati@xxxxxxxxxxxxxx.xx
    root@localhost
  }
  router_id HAPROXY_sec
}

vrrp_script chk_mysql {
  script   "/usr/local/script/mysql_health_check.sh"
  interval 2
  weight   2
}

vrrp_instance HA2 {
  virtual_router_id 51
  advert_int 1
  priority 101
  state BACKUP
  interface enp0s8
  unicast_src_ip 192.168.56.104    # IP address of Slave Server
  unicast_peer {
    192.168.56.103                 # IP address of Master Server
  }
  virtual_ipaddress {
    192.168.56.110/24 dev enp0s8
  }
  track_script {
    chk_mysql
  }
}
root@ubuntu:~#

Conclusion:

We have successfully setup MySQL auto failover ….

Enjoy !!!