{{parent page="MySQL"}}

===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
%%(text;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/

%%(text;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
%%

%%(text;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:

%%(mysql_mon.sh;bash)
#!/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