Keepalived for Mysql

You can handle the automatic db fail-over in a clustered mysql environment using the Keepalived service. This is so simple that you will only need a virtual IP address and Keepalived service installed in both servers. This Keepalived service will assign the virtual IP to one of the two servers depending the server condition. For an example, if the mysql service on master node (10.10.10.1) stop or if server undergoes a sudden breakdown, then this virtual IP will get automatically assigned to the other master node(10.10.10.2). As a result, the second server will handle the traffic without any downtime in the production environment.

Let’s have 10.10.10.3 as the virtual IP and follow the below steps.

Installing the Keepalived service in both servers,

> sudo yum -y install keepalived

Now backup the default configuration file and write your own configuration for the replicated cluster.

> mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.back
> vi /etc/keepalived/keepalived.conf

This is a sample configuration file for the mysql master server,

sample keepalived.conf for master 1

Below is the sample mysqltest.sh located at /etc/keepalived/ of both servers.

#!/bin/bash
# if mysql is dead in this node, then other node will bind the IP.
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USERNAME="testUser"
MYSQL_PASSWORD="xxxxxxx"
mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD --connect_timeout=3 -e "select version();"
if [ $? -ne 0 ]; then
 echo "bad"
 exit 1      # mysql node is bad
fi
echo "good"
exit 0       # mysql node is good

This is the sample keepalived configuration file for the other mysql master node,

sample keepalived.conf for master 2

Now configure the Keepalived service to automatically start at the start of the server with,

> systemctl enable keepalived

Now start the Keepalived service,

> systemctl start keepalived

You may be using below commands as well to control the Keepalived service

service keepalived start / stop / restart / status

Testing the service

You can use the below command to check the assigned virtual IP for the server.

> ip addr show

When all the services are up and running on both servers, virtual IP will only be assigned to master server. When the mysql service, Keepalived service or the primary server is down, then virtual IP will be automatically assigned to the secondary server.