KfWiki: MySQL and HA

HomePage » Database » MySQL » MysqlHA

MySQL and HA

Setup MySQL on DrBD and heartbeat
Ref: http://www.linuxjournal.com/article/9074∞

Setting up DRBD

See StorageDRBD

Setting up Heartbeat

For RHEL, I use the heartbeat rpm from centos.

Create authkeys

Use the same authkeys on both nodes

authkeys

auth 1

1 md5 some_random_secret

Create ha.cf

Make these two files available on both primary and standby host, under /etc/ha.d/

ha.cf

debugfile /var/log/ha.debug

#

#       File to write other messages to

#

logfile /var/log/ha.log

#

#       Facility to use for syslog()/logger

#

logfacility     local0

#

#       keepalive: how many seconds between heartbeats

#

keepalive 2

#

#       deadtime: seconds-to-declare-host-dead

#

warntime 5

deadtime 10

udpport 694

#

#       What interfaces to heartbeat over?

#

bcast     eth0

#

auto_failback off

#

node    prod02.xxx.org

node    prod01.xxx.org

#

ping mygateway.xxx.org

haresources

prod02.domain.com drbddisk::mydata \

        Filesystem::/dev/drbd0::/var/lib/mysql::ext3[::MOUNT_OPTIONS] \

        IPaddr::10.0.0.50 \

        mysql

Starting heartbeat on primary

Make sure DRBD is in Secondary/Secondary state

drbdadm secondary mydata

Now starts up heartbeat, which will bring DRBD to primary and start mysql

service heartbeat start

Check /proc/drbd and ifconfig, you should see Primary/Secondary with drbd, and the additional ethernet alias 10.0.0.50 being brought up. Finally, check mysql availability.

Failover and failback

One should have a script on primary DB to monitor mysql's availability. You may use the following as an example:

#!/bin/bash
DBRESULT=`echo "select count(1) from mysql.user;" | mysql -s --connect_timeout=1 -uroot -pxxxx`
if [ ${DBRESULT} -lt 1];
then
 logger -t MYSQL_MON "MySQL unavailable. Stopping heartbeat service."
 service heartbeat stop
fi

Once heartbeat is stopped on the primary, it will do its work and activate the standby database.

Fail-back is preferred as a manual procedure. It is important that the heartbeat service is not configured to startup automatically on both servers. In case of a server reboot or fail-back scenario, it best to start / restart services interactively. Steps for fail-back:
1. shutdown heartbeat on both servers

2. check /proc/drbd make sure standby server is currently the primary DRBD, and primary server is secondary DRBD. If you're connected the standby machine, the status should be Primary/Secondary.

3. Now to a DRBD synchronization from standby to primary

# On secondary host, invalidate my data partition. Restart DRBD and synchronization will begin automatically.
drbdadm invalidate mydata
service drbd restart

Check /proc/drbd to ensure the sync process has started. Wait for the sync to complete.

4. Now that the primary has an updated DRBD partition, and is ready to be failed-back. Change DRBD to Secondary/Secondaryby running drbdadm secondary mydata on both hosts.

5. Start heartbeat service on primary. Check /var/log/ha-log to make sure all local resources are brought up

6. Start heartbeat service on standby