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

#

#       Use multiple interfaces here, one of the should be the 

#       interface dedicated for heartbeat. This gives you redundancy

#       I do not prefer serial connection, but that could be a more 

#       accurate connection to prevent split brain situation.

#

bcast     eth0

bcast     eth1

#

auto_failback off

#

node    prod02.xxx.org

node    prod01.xxx.org

#

ping_group hb-ping-test 10.0.0.1 10.0.0.254

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

Copy the mysql init script from /etc/init.d to /etc/ha.d/resources.d. Edit it such that it uses /var/lib/mysql/my.cnf to look for configurations. The reason is obvious, when mysql fails over to the standby host, you want it to always use the same defaults file. This can be done by adding the option --defaults-file=/var/lib/mysql/my.cnf to my_print_defaults:

get_mysql_option(){
		result=`/usr/bin/my_print_defaults  --defaults-file=/var/lib/mysql/my.cnf "$1" | sed -n "s/^--$2=//p" | tail -n 1`
		if [ -z "$result" ]; then
			# not found, use default
			result="$3"
		fi
}

Move /etc/my.cnf to /var/lib/mysql/ or whereever DRBD will be mounted on
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

Update: One may instruct heartbeat to failover / failback using the commands /usr/lib64/heartbeat/hb_standby and /usr/lib64/heartbeat/hb_takeover commands. But heartbeat does not actually check mysql's status.

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

KfWiki : MysqlHA