Keeping up with High Availability - Automating MySQL Failovers

September 7, 2010

Automating failover situations can benefit an organization along with the database administrator who is on call. This article discusses how to setup Keepalived and how it may help in automated failovers for a Multi-Master MySQL cluster.

Last month was all about setting up your Multi-Master
MySQL cluster and keeping your standby master hot. This article is an
extension of our Multi-Master configuration and talks about great techniques to
automate a failover. There are several ways automating failover situations can
benefit an organization along with the database administrator who is on call.

This post discusses a simple setup of keepalived in moderate detail, and
how it may help in automated failovers for a Multi-Master MySQL cluster.

What
is Keepalived

Keepalived is a
utility that provides interface failover, in our case one virtual IP, and can
perform health checks. In the MySQL world, when using Multi-Master replication,
this is a very good mechanism to have. With a good implementation of Keepalived
you will be able to fail over a virtual/floating IP address when the master
(write) server becomes unavailable and switch that IP over to the hot standby
server.

The
Configuration Files

Locate the main keepalived configuration file in the /usr/local/etc/keepalived
directory. The file name should be obvious but if not then look for
keepalived.conf. I like to make a backup of this file so I have something to
reference quickly if I need it but this is not necessary.

The configuration file on the master (write) server will be slightly
different from the configuration file on the slave (read) server. Below are the
two example files:

We can test our setup with a very simple ping. First, you will need to have
three shells open, one to the master server, one to the slave server and one
running a ping to the virtual IP 192.168.1.102. While the ping is running, you
can simply stop keepalived and watch as the virtual IP flips over to the slave
server. You should use the same techniques described above to check if the
virtual IP has switched over to the slave server.

A ping test is the easiest way to test if the failover is going to work but
it is NOT the only test you need to run. Running a simple BASH script that
connects to MySQL on the Virtual IP (192.168.1.102) is a good way to test. Here
is a VERY simple test for mysql:

So instead of running a ping, run the following script and see what the
results are. I would expect that the Virtual IP would be flipped over to the
slave server and the script above would not error out. Note that this is a very
simple test and if you are implementing keepalived in production, you should
test with your production load.

As always, millage may vary and there are defiantly other ways to accomplish
automated failovers to accomplish HA in MySQL.