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.

Image for post
Image for post

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

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

Image for post
Image for post
sample keepalived.conf for master 1

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

#!/bin/bash

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

Image for post
Image for post
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.

Senior Software Engineer | BSc (Hons) Engineering | CIMA | Autodidact | Knowledge-Seeker

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store