linuxsutra | Howto MySQL DRBD HA

Howto MySQL DRBD HA

praveenmanja | 10 February, 2007 13:19

Howto MySQL DRBD HA:

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.

Env:
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
(e.g., DOS FDISK, OS/2 FDISK)

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.

DRBD:

Installation:
On machine1 and machine2

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

Configuration:
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 machine1.myhost.com {
device /dev/drbd1;
disk /dev/sda5;
address 10.10.150.1:7789;
meta-disk internal;
}
on machine2.myhost.com {
device /dev/drbd1;
disk /dev/sda5;
address 10.10.150.2:7789;
meta-disk internal;
}
}

Start:

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
df

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

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.

Refer thi page on Resource Agent

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 :

machine1:
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
machine2:
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.

HA:

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

Configuration:
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:
a)/etc/ha.d/ha.cf
#/etc/ha.d/ha.cf 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 10.10.150.100 #(Your gateway IP)
apiauth ipfail gid=haclient uid=hacluster
node machine1.myhost.com
node machine2.myhost.com
On both machines:
b)/etc/ha.d/haresources
NOTE: Assuming 10.10.150.3 is virtual IP for your MySQL resource and mysqld is the LSB resource agent.
#/etc/ha.d/haresources content
machine1.myhost.com LVSSyncDaemonSwap::master IPaddr2::10.10.150.3/24/eth0 drbddisk::db Filesystem::/dev/drbd1::/db::ext3 mysqld
c)/etc/ha.d/authkeys
#/etc/ha.d/authkeys content
auth 2
2 sha1 YourSecretString
Now, make your authkeys secure:
chmod 600 /etc/ha.d/authkeys

Start:
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
df
/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.

13 comments:


Irwan said...: Great article!
I'm blogging about DRBD for MySQL too.; June 07, 2007 1:57 AM
Anonymous said...: Good article, but why do you need LVS? Aren't DRDB and the IP addr sufficient for MySQL?; July 17, 2007 3:13 PM
ku3_p417ch0n6 said...: LVS needed because Kumar using virtual IP for his mysql server.; November 29, 2007 11:49 PM
Anonymous said...: i have some error " Lower device is already claimed drbd cmd 'drbdsetup /dev/drbd0 disk /dev/sda6 /dev/sda6 internal --set-defaults --create-device --on-io-error=detach'. how to solve this problem? i have 2 resources in drbd.conf. can i use Kumar's step setup same this above document? thank you; January 16, 2008 1:44 AM
tompata said...: Is there anybody tried to install / test MySQL DRBD on virtual machines (example: vmware)?; January 29, 2008 1:21 AM
pannu said...: Great article.
I have one question.. if my data volume or the drdb volume where mysql will keep the data is on a SAN partition connected to machine 1.; February 02, 2008 3:28 AM
kerneloverdrive said...: Hi,

Is it possible to use to virutal IPs for the mysql-drdb-ha box?

ex. 192.168.0.1 - Local
100.100.100.1 - External

where both ip's will cater mysql services and also replicate to local and remote slaves.; February 13, 2008 8:35 PM
kerneloverdrive said...: mybad: that's -- possible to use "two" or more virtual IP's...; February 13, 2008 8:37 PM
Praveen Kumar K.S said...: @kerneloverdrive:

Ya.. you can use more that one VIP.
In haresources file you can mention VIP per eth device.

i.e

IPaddr2::192.168.0.1/24/eth0
IPaddr2::100.100.100.1/24/eth1

The MySQL service will be availed on both IP's for sure.; February 13, 2008 10:58 PM
Praveen Kumar K.S said...: @ pannu:

If you have a SAN.. Why you have to go for DRBD HA. Instead go for RedHat Cluster with SAN to be used as a shared data volume for multiple MySQL. Thereby you get HA and load balancing.; February 13, 2008 11:00 PM
kerneloverdrive said...: Hi Praveen,

Thanks for the help. Will try implementing this setup asap.

when I can get a copy of RHEL, will try it with SAN/NAS and so that I can have a real HPC/HA MySQL Cluster.; February 14, 2008 7:20 AM
Suhas said...: Hi Praveen,
I have configured MySQL DRBD / HA it's working great.. I also made Slave for VIP. this is really works fine for us.
we are using 32 bit CentOS.

Thanks
~Pandit; March 03, 2008 3:58 AM
Praveen Kumar K.S said...: Nice to know that 'HA-Master replicating to slave' is working fine. Cheers!; March 04, 2008 12:23 PM

Howto MySQL DRBD HA

13 comments:

search

recently...

Categories

Menu

Syndicate

Who am I?