Friday, January 22, 2010


SkyHi @ Friday, January 22, 2010
This is one of the MySQL High Availability strategy I had discussed earlier. I have consolidated what I found on net in bits and pieces + some of my experiences. Here are some tips to get it working.
In this approach, I have noticed that the failover is smooth and quick. If you are looking only for High Availability of MySQL resources, then
, this is the one.

I tried CentOS release 4.4 (Final) x86_64 on 2 servers.
One which has a better RAM can be used as a active node. Other, can be considered the failover.

Partitioning during OS installtion:
You need to reserve a huge physical volume which would be later used as a DRBD volume.
Don't specify any file system type.

fdisk /dev/sda

Should print:

The number of cylinders for this disk is set to 9729.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs

Command (m for help): p

Disk /dev/sda: 80.0 GB, 80026361856 bytes
255 heads, 63 sectors/track, 9729 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 2611 20972826 83 Linux
/dev/sda2 2612 2872 2096482+ 82 Linux swap
/dev/sda3 2873 3003 1052257+ 8e Linux LVM
/dev/sda4 3004 9729 54026595 5 Extended
/dev/sda5 3004 9729 54026563+ 8e Linux LVM

We are going to use /dev/sda5 as a DRBD device.


On machine1 and machine2

yum -y install drb
yum -y install kernel-module-drbd-2.6.9-42.ELsmp
modprobe drbd

On both machines:

vi /etc/drbd.conf

# please have a a look at the example configuration file in
# /usr/share/doc/drbd/drbd.conf
# Our MySQL share
resource db
protocol C;
incon-degr-cmd "echo '!DRBD! pri on incon-degr' | wall ; sleep 60 ; halt -f";
startup { wfc-timeout 0; degr-wfc-timeout 120; }
disk { on-io-error detach; } # or panic, ...
syncer {
group 1;
rate 6M;
on {
device /dev/drbd1;
disk /dev/sda5;
meta-disk internal;
on {
device /dev/drbd1;
disk /dev/sda5;
meta-disk internal;


On both machines:
drbdadm adjust db
On machine1:
drbdsetup /dev/drbd1 primary --do-what-I-say
service drbd start
On machine2:
service drbd start
On both machines(see status):
service drbd status
On machine1:
mkfs -j /dev/drbd1
tune2fs -c -1 -i 0 /dev/drbd1
mkdir /db
mount -o rw /dev/drbd1 /db
On machine2:
mkdir /db

Test failover:
For manual switchover(This wont be needed as HA will do this for you):
On primary-
umount /db
drbdadm secondary db
On secondary-
drbdadm primary db
service drbd status
mount -o rw /dev/drbd1 /db

This finishes DRBD part of it. You have created a DRBD mount which will be used as a data directory for your MySQL.

  • Install it as you wish.
  • You can do an RPM based or a BINARY or a SOURCE compilation.
  • IMPORTANT:(Crucial for failover) Heartbeat uses either LSB Resource Agents or OCF Resource Agents or Heartbeat Resource Agents to start and stop heartbeat resources. Here, MySQL,DRBD and IP are our heartbeat resources.
  • As you are aware of it many *nix services are started using LSB Resource Agents. They are found in /etc/init.d
  • A service is started/stopped using: /etc/init.d/servicename start/stop/status
  • You should see to it that you have similar LSB Resource Agent for MySQL.
  • In, source based installation it will be created in $PREFIX/share directory as mysql.server. $PREFIX is one you give during source compilation.
  • Fix that script and copy it to /etc/init.d/
  • In case of RPM based installation you will get LSB Resource Agent in place.
  • End objective is that, MySQL should be up and running.
Now comes the hurdle.
  • Stop MySQL.
  • Move your data directory to a directory on DRBD share.
  • Later, create a softlink.
  • This is how I would have done assuming my initial data directory was /home/mysql/data :
mkdir /db/mysql
NOTE: /db should be mounted to do this
mkdir /db/mysql/data
chown -R mysql /db/mysql/data
chgrp -R mysql /db/mysql/data
mv /home/mysql/data /db/mysql/data
ln -s /db/mysql/data /home/mysql/data
mv /home/mysql/data /tmp
ln -s /db/mysql/data /home/mysql/data

Now, start MySQL on machine1. Create some sample database and table. Stop MySQL. Do a manual switchover of DRBD. Start MySQL on machine2 and query for that table. It should work. But, this is of no use if you have to switchover manually every time. Now we are heading to HA.


yum -y install gnutls*
yum -y install ipvsadm*
yum -y install heartbeat*

Edit /etc/sysctl.conf and set net.ipv4.ip_forward = 1
vi /etc/sysctl.conf
# Controls IP packet forwarding
net.ipv4.ip_forward = 1
/sbin/chkconfig --level 2345 heartbeat on
/sbin/chkconfig --del ldirectord
You need to setup the following conf files on both machines:
#/etc/ha.d/ content
debugfile /var/log/ha-debug
logfile /var/log/ha-log
logfacility local0
keepalive 2
deadtime 30
warntime 10
initdead 120
udpport 694 #(If you have multiple HA setup in same network.. use different ports)
bcast eth0 # Linux
auto_failback on #(This will failback to machine1 after it comes back)
ping #(Your gateway IP)
apiauth ipfail gid=haclient uid=hacluster
On both machines:
NOTE: Assuming is virtual IP for your MySQL resource and mysqld is the LSB resource agent.
#/etc/ha.d/haresources content LVSSyncDaemonSwap::master IPaddr2:: drbddisk::db Filesystem::/dev/drbd1::/db::ext3 mysqld
#/etc/ha.d/authkeys content
auth 2
2 sha1 YourSecretString
Now, make your authkeys secure:
chmod 600 /etc/ha.d/authkeys

On both machines(first on machine1):
Stop MySQL.
Make sure MySQL does not start on system init.
For that:
/sbin/chkconfig --level 2345 MySQL off
/etc/init.d/heartbeat start
These commands will give you status about this LVS setup:
/etc/ha.d/resource.d/LVSSyncDaemonSwap master status
ip addr sh
/etc/init.d/heartbeat status
/etc/init.d/mysqld status

Access your HA-MySQL server like:
mysql -h10.10.150.3

Shutdown machine1 to see MySQL up on machine2.
Start machine1 to see MySQL back on machine1.