Keeping up with High Availability – Automating MySQL Failovers


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.

Lets
Set it Up

If you have never installed keepalived please follow the steps below.

1. Get the tarball.

# wget http://www.keepalived.org/software/keepalived-1.1.20.tar.gz
--2010-08-19 10:47:35-- http://www.keepalived.org/software/keepalived-1.1.20.tar.gz
Resolving www.keepalived.org... 188.165.36.82
Connecting to www.keepalived.org|188.165.36.82|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 233002 (228K) [application/x-gzip]
Saving to: `keepalived-1.1.20.tar.gz'
100%[======================================================================================>] 233,002 233K/s in 1.0s 
2010-08-19 10:47:37 (233 KB/s) - `keepalived-1.1.20.tar.gz' saved [233002/233002]

2. Untar and unzip the file.

# /usr/local/src#tar xzvf keepalived-1.1.20.tar.gz

3. cd to the newly created directory.

# /usr/local/src#cd keepalived-1.1.20

4. Make sure you have at a minium openssl-devel installed
on your server.

# /usr/local/src/keepalived-1.1.20#yum install openssl-devel.x86_64

4a. If you want them all please run the following.

yum -y install kernel-headers kernel-devel

5. Configure keepalived.

# /usr/local/src/keepalived-1.1.20#./configure
checking for gcc... gcc
checking for C compiler default output file name... a.out
checking whether the C compiler works... yes
checking whether we are cross compiling... no
checking for suffix of executables...
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking for a BSD-compatible install... /usr/bin/install -c
checking for strip... strip
checking how to run the C preprocessor... gcc -E
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking for ANSI C header files... yes
checking for sys/wait.h that is POSIX.1 compatible... yes
checking for uname... yes
…
configure: creating ./config.status
config.status: creating Makefile
config.status: creating genhash/Makefile
config.status: creating keepalived/core/Makefile
config.status: creating keepalived/include/config.h
config.status: creating keepalived.spec
config.status: creating keepalived/Makefile
config.status: creating lib/Makefile
config.status: creating keepalived/vrrp/Makefile
Keepalived configuration
------------------------
Keepalived version : 1.1.20
Compiler : gcc
Compiler flags : -g -O2
Extra Lib : -lpopt -lssl -lcrypto
Use IPVS Framework : No
IPVS sync daemon support : No
Use VRRP Framework : Yes
Use Debug flags : No

6. Run Make.

# /usr/local/src/keepalived-1.1.20#make
make -C lib || exit 1;
make[1]: Entering directory `/usr/local/src/keepalived-1.1.20/lib'
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes -c memory.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes -c utils.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes -c notify.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes -c timer.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes -c scheduler.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes -c vector.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes -c list.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes -c html.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes -c parser.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes -c signals.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes -c logger.c
…
gcc -g -O2 -I/usr/src/linux/include -I../include -I../../lib -Wall -Wunused -Wstrict-prototypes -D_KRNL_2_6_ -D_WITHOUT_LVS_ -D_WITHOUT_IPVS_SYNCD_ -c vrrp_track.c
… 
make[2]: Leaving directory `/usr/local/src/keepalived-1.1.20/keepalived/vrrp'
Building ../bin/keepalived
strip ../bin/keepalived
Make complete
make[1]: Leaving directory `/usr/local/src/keepalived-1.1.20/keepalived'
make -C genhash
make[1]: Entering directory `/usr/local/src/keepalived-1.1.20/genhash'
gcc -g -O2 -I/usr/src/linux/include -I../lib -Wall -Wunused -Wstrict-prototypes -c -o main.o main.c
…
Building ../bin/genhash
strip ../bin/genhash
Make complete
make[1]: Leaving directory `/usr/local/src/keepalived-1.1.20/genhash'
Make complete

7. Make install.

# /usr/local/src/keepalived-1.1.20#make install
make -C keepalived install
make[1]: Entering directory `/usr/local/src/keepalived-1.1.20/keepalived'
install -d /usr/local/sbin
install -m 700 ../bin/keepalived /usr/local/sbin/
install -d /usr/local/etc/rc.d/init.d
install -m 755 etc/init.d/keepalived.init /usr/local/etc/rc.d/init.d/keepalived
install -d /usr/local/etc/sysconfig
install -m 755 etc/init.d/keepalived.sysconfig /usr/local/etc/sysconfig/keepalived
install -d /usr/local/etc/keepalived/samples
install -m 644 etc/keepalived/keepalived.conf /usr/local/etc/keepalived/
install -m 644 ../doc/samples/* /usr/local/etc/keepalived/samples/
install -d /usr/local/share/man/man5
install -d /usr/local/share/man/man8
install -m 644 ../doc/man/man5/keepalived.conf.5 /usr/local/share/man/man5
install -m 644 ../doc/man/man8/keepalived.8 /usr/local/share/man/man8
make[1]: Leaving directory `/usr/local/src/keepalived-1.1.20/keepalived'
make -C genhash install
make[1]: Entering directory `/usr/local/src/keepalived-1.1.20/genhash'
install -d /usr/local/bin
install -m 755 ../bin/genhash /usr/local/bin/
install -d /usr/local/share/man/man1
install -m 644 ../doc/man/man1/genhash.1 /usr/local/share/man/man1
make[1]: Leaving directory `/usr/local/src/keepalived-1.1.20/genhash'

8. Type the following commands to create the service and
run level:

# cd /etc/sysconfig
# ln -s /usr/local/etc/sysconfig/keepalived .
# cd /etc/rc3.d/
# ln -s /usr/local/etc/rc.d/init.d/keepalived S100keepalived
# cd /etc/init.d/
# ln -s /usr/local/etc/rc.d/init.d/keepalived

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:

MASTER CONFIGUTATION FILE

! Configuration File for keepalived
global_defs {
router_id MYTEST
}
vrrp_instance VI_1 {
state MASTER
interface bond0
virtual_router_id 41
priority 101 # note the difference between the two servers
advert_int 1
authentication {
auth_type PASS
auth_pass 1111 # put in your own numeric password here (In this example it's 1111)
}
virtual_ipaddress {
192.168.1.102
}
}

SLAVE CONFIGURATION FILE

! Configuration File for keepalived
global_defs {
router_id MYTEST
}
vrrp_instance VI_1 {
state MASTER
interface bond0
virtual_router_id 41
priority 100 # note the difference between the two servers
advert_int 1
authentication {
auth_type PASS
auth_pass 1111 # put in your own numeric password here (In this example it's 1111)
}
virtual_ipaddress {
192.168.1.102
}
}

After you have implemented the configuration files, you should symlink them
over to the /etc/keepalived directory. This is where the /etc/init.d/keepalived
script looks for the configuration file.

# cd /etc/keepalived
# ln -s /usr/local/etc/keepalived/keepalived.conf .

You are now ready to start keepalived on the master server but before we do that,
you should check the output of the following for reference:

# /sbin/ip addr show bond0 |grep inet
inet 192.168.1.100/23 brd 192.168.1.1 scope global bond0

Start keepalived by running the following on both the master and slave
server. Start the master server first then the slave server.

# /etc/init.d/keepalived start
Starting keepalived: [ OK ]

You can check to see if the IP has been added to bond0 by running the ip
addr line again.

Example for the Master server:

# /sbin/ip addr show bond0 |grep inet
inet 192.168.1.100/23 brd 192.168.1.1 scope global bond0
inet 192.168.1.102/32 scope global bond0

You can also check /var/log/messages for good information about keepalived.

Example for the Master server:

19 20:27:38 sandbox Keepalived: Starting VRRP child process, pid=10521
19 20:27:38 sandbox Keepalived_vrrp: VRRP sockpool: [ifindex(7), proto(112), fd(9,10)]
19 20:27:39 sandbox Keepalived_vrrp: VRRP_Instance(VI_1) Transition to MASTER STATE
19 20:27:40 sandbox Keepalived_vrrp: VRRP_Instance(VI_1) Entering MASTER STATE
19 20:27:40 sandbox Keepalived_vrrp: VRRP_Instance(VI_1) setting protocol VIPs.
19 20:27:40 sandbox Keepalived_vrrp: VRRP_Instance(VI_1) Sending gratuitous ARPs on bond0 for 192.168.1.102
19 20:27:45 sandbox Keepalived_vrrp: VRRP_Instance(VI_1) Sending gratuitous ARPs on bond0 for 192.168.1.102

Example for the Slave server:

19 20:30:23 sandbox2 Keepalived_vrrp: Using LinkWatch kernel netlink reflector...
19 20:30:23 sandbox2 Keepalived_vrrp: VRRP sockpool: [ifindex(7), proto(112), fd(9,10)]
19 20:30:24 sandbox2 Keepalived_vrrp: VRRP_Instance(VI_1) Transition to MASTER STATE
19 20:30:24 sandbox2 Keepalived_vrrp: VRRP_Instance(VI_1) Received higher prio advert
19 20:30:24 sandbox2 Keepalived_vrrp: VRRP_Instance(VI_1) Entering BACKUP STATE

Testing

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:

# while true; do mysql -usomeuser --host=192.168.1.102 --port=3306 -e "select 1"; sleep .5; done

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.

Other Technologies to Consider

Heartbeat

Hearbeat with DRBD

Multi-Master Replication
Manager (MMM)

HA Proxy

»


See All Articles by Columnist

Chris Schneider

Chris Schneider
Chris Schneider
Certified MySQL professional, actively involved in the MySQL community for the better end of a decade. I have designed, implemented and maintained small to large scale MySQL installations while training and mentoring teams of DBAs. This includes building architectures from the ground up and improving on those that are currently in place while emphasizing scalability, performance and ease of use. Most of the work I am involved with now is in the Web 2.0 arena. I also run a community site, EverythingMySQL.com, that promotes collaboration within the open source community, specifically, MySQL.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles