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,
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_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
exit 1 # mysql node is bad
exit 0 # mysql node is good
This is the sample keepalived configuration file for the other mysql master node,
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.