MySQL Auto Failover using Keepalived on CentOS

Introduction

Keepalived is a lightweight and high availability framework solution in Linux. It uses Linux Virtual Server (LVS) to perform load balancing and failover tasks on. The Keepalived can be used to monitor systems and to automatically failover to a standby if problems occur. If the primary server goes down, the floating IP will be moved to the second server automatically, allowing service to resume.

How Does Keepalived Work?

In Master-Slave Replication database setup, when the Master failed, we need to manually make the configuration changes in order to failover to the next available server. When this happens, downtime is expected since manual failover will take time.

Keepalived is a utility that provides interface failover. With a good implementation of Keepalived, you will be able to failover a virtual IP address when the master server becomes unavailable and automatically switches that IP over to the hot standby server. As a result, the second server will handle the traffic without any downtime in the production environment.

Prerequisites

We’ll be using CentOS Linux as the operating system for this tutorial. Here are the following prerequisites that you need to setup first in order to complete this guide:

1. Working MySQL Master-Slave Replication
   To setup a Master-Slave Replication, follow the steps here.
2. Virtual IP Address (VIP) of Master Server
   You can follow the steps here to learn how to configure a VIP on your server.

Network Scenario for this tutorial:

Master Server – 192.168.40.222
Slave Server – 192.168.40.223
Virtual IP Address – 192.168.40.224

Tutorial

Step 1. Install Keepalived

Keepalived is usually available in most Linux repositories. Here’s the command to install Keepalived service in both servers:

sudo yum -y install keepalived

Step 2. Backup Default Keepalived Config File

Let’s back up the default Keepalived configuration file if in case you’d like to use it later as a template or for reference.

mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.back

Step 3. Configure Keepalived Config File in Master Server

You can copy the following configuration block below but you have to make appropriate changes for your environment which include the IPs and interfaces, priority and unicast_peer.

Location: /etc/keepalived/keepalived.conf

vrrp_script chk_mysql {
script "pidof mysqld"
interval 2                       # every 2 seconds
weight 2
}

vrrp_instance VI_1 {
interface enp0s3                 # the network interface to which the virtual address is assigned
state MASTER                     # MASTER for master server
virtual_router_id 50             # the virtual router ID must be unique to each VRRP instance
unicast_src_ip 192.168.40.222    # IP address of Master Server

unicast_peer {

192.168.40.223                   # IP address of Slave Server
}

priority 102                     # master server should always have a higher priority number 
                                 # than the slave

track_script {
     chk_mysql
}

virtual_ipaddress {
192.168.40.224/24 dev enp0s3     # Virtual IP address
}              
}

Tip

Run this command to show the current network configuration and see the configured network interface in the server:

ip addr show

Through this command, you can also see the Virtual IP Address assigned to the Master Server as shown below:

Take note that the Virtual IP Address (192.168.40.224) is assigned to the Master Server (192.168.40.222)

Step 4. Configure Keepalived config File in Slave Server

Again, you can copy the following configuration block below, but you need to make appropriate changes for your environment.

Location: /etc/keepalived/keepalived.conf

vrrp_script chk_mysql {
script "pidof mysqld"
interval 2                       # every 2 seconds
weight 2
}

vrrp_instance VI_1 {
interface enp0s3                 # the network interface to which the virtual address is assigned
state BACKUP                     # BACKUP for slave server
virtual_router_id 50             # the virtual router ID must be unique to each VRRP instance
unicast_src_ip 192.168.40.223    # IP address of Slave Server

unicast_peer {
192.168.40.222                   # IP address of Master Server
}

priority 101                     # slave server should have a priority number lower than the slave

track_script {
     chk_mysql
}

virtual_ipaddress {
192.168.40.224/24 dev enp0s3     # Virtual IP address
}               
}

Step 5. Run Command on Both Servers

Run this command on both servers for the firewall:

firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 
--in-interface enp0s3 --destination 224.0.0.18 --protocol vrrp -j ACCEPT;

firewall-cmd --direct --permanent --add-rule ipv4 filter OUTPUT 0 
--out-interface enp0s3 --destination 224.0.0.18 --protocol vrrp -j ACCEPT;

firewall-cmd –reload;

Step 6. Configure Keepalived Service

Configure the Keepalived service to automatically start at the start of the server:

systemctl enable keepalived

Step 7. Start the Keepalived Service

Start the Keepalived service:

systemctl start keepalived

Step 8. Check Status of Keepalived

Now check the status of the Keepalived on Master and Slave servers by:

service keepalived status

You will know if you have configured the Keepalived service properly if the status indicated is active as shown below:

Notes

Here are the following commands you will need to control Keepalived:

service keepalived start    - used to start the Keepalived service
service keepalived stop     - used to stop the Keepalived service
service keepalived restart  - used to restart the Keepalived service
service keepalived status   - used to check the status of the Keepalived service

If you encountered a problem like this:

Keepalived[1650]: WARNING - default user 'keepalived_script' 
for script execution does not exist - please create.

Resolve this by:

sudo useradd -g users -M keepalived_script

and restart the Keepalived service:

sudo systemctl restart keepalived

You can check the logs of the keepalived here:

/var/log/messages

Test

To the most awaited part of testing if the Keepalived is going to work for the failover, let’s test it by first shutting down the Master Server (192.168.40.222).

Now, you will see that the Virtual IP address (192.168.40.224) is now shifted to the Slave Server (192.168.40.223).

Check the status of the Keepalived in the Slave Server.

History

• 5^th December, 2019: Initial version