MySQL HA with DRDB and Heartbeat on CentOS 5.5


July 20, 2010

This is one of a few MySQL High Availability strategies.  I have used this for years and found it work great.  If you don’t know about DRBD and MySQL you should read Peter’s comments.

These are step by step instructions for Redhat 5 or CentOS.

If you need more details please refer to:
http://www.drbd.org/users-guide/

Configuring MySQL for DRBD
http://dev.mysql.com/doc/refman/5.1/en/ha-drbd-install-mysql.html

Getting started:

The OS in this example is CentOS 5.5.  I added a new disk (/dev/sde) to the four disk RAID-5 and RAID-1 I was already using.   I’m only creating an 8 gig disk (vmware). You should start with a partition (LVM and or RAID) partition big enough for your data.

# uname -a 

Linux db1.grennan.com 2.6.18-194.8.1.el5 #1 SMP Thu Jul 1 19:04:48 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

 # df

Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/md1              24065660   2826564  19996896  13% /
/dev/md0                101018     20988     74814  22% /boot
tmpfs                   513476         0    513476   0% /dev/shm

# fdisk -l /dev/sde

Disk /dev/sde: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot      Start         End      Blocks   Id  System
/dev/sde1               1        1044     8385898+  83  Linux

DRBD:

Installation:
On machine1 and machine2 install DRBD and its kernel module.  You may need to review the packages you have available using ‘yum list | grep drbd’.  These are for CentOS 5.5.  You may also need to reboot after this step.

 # yum -y install drbd  # yum –y install kmod-drbd82.x86_64 # modprobe drbd

Configuration:
On both machines edit this configuration file.  I have highlighted parts you will need to edit in red.

# vi /etc/drbd.conf
# # please have a a look at the example configuration file in # /usr/share/doc/drbd82/drbd.conf # # Our MySQL share resource db { protocol C; startup { wfc-timeout 0; degr-wfc-timeout 120; } disk { on-io-error detach; } # or panic, ... syncer {  rate 6M; } on db1.grennan.com { device /dev/drbd1;} disk /dev/sde1;  address 192.168.2.13:7789;  meta-disk internal; } on db2.grennan.com { device /dev/drbd1; disk /dev/sde1; address 192.168.2.14:7789; meta-disk internal; } }

 

 

Manage DRDB processes:

On both machines run

 # drbdadm adjust db

 

On machine1

 # drbdsetup /dev/drbd1 primary –o
 # 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 /data # mount -o rw /dev/drbd1 /data

 

On machine2

 # mkdir /data

Test failover:
This is how you perform a manual fail over. You will use HA to do this for you in the next sections.

 

On primary (server1) # umount /data # drbdadm secondary db

 

On secondary (server2)

 # drbdadm primary db # service drbd status # mount -o rw /dev/drbd1 /data # df
Filesystem           1K-blocks      Used Available Use% Mounted on /dev/md1              24065660   1898696  20924764   9% / /dev/md0                101018     14886     80916  16% /boot tmpfs                   513472         0    513472   0% /dev/shm /dev/drbd1             8253948    149628   7685040   2% /data


Note we never formatted (mkfs) the disk on machine2! Here it is, ready to go, DRDB has copied all the data.

 

MySQL:

Here are a few notes for you to think about.

  • The default location for MySQL data is /var/lib/mysql.  You will be moving this to /data/mysql.
  • MySQL configuration is in /etc/my.cnf.  So that changes to the configuration move with failover, you should put my.cnf in /data/mysql and create a sym-link of /etc/my.cnf to this file.

Now comes the hurdle.

  • Install MySQL as you wish.
  • Move your data directory to a /data/mysql

On machine1

 # mkdir /data/mysql
 # chown  mysql.mysql /data/mysql
 # cp –prv /var/lib/mysql/* /data/mysql

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. When you have this working you are ready to move to Heartbeat.

Here are a couple of scripts to make this easy.

 

drdb-secondary

 # service mysql stop
 # umount /data
 # drbdadm secondary db
 # drdb-primary:
 # drbdadm primary db
 # mount -o rw /dev/drbd1 /data
 # service mysql start 

 

 

 


 

HA:

 

  • IMPORTANT: Heartbeat uses either Linux Services (LSB) Resource Agents or Heartbeat Resource Agents (HRA) to start and stop heartbeat resources. You will be adding MySQL (LSB), drbddisk (HRA) and IPaddr2 (HRA) are our heartbeat resources.
  • Refer this 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

 

Installation:

On machine1 and machine2 install Heartbeat and needed utilities.  You may need to review the packages you have available using ‘yum list | grep drbd’.  These are for CentOS 5.5.  You may also need to reboot after this step.

 # yum -y install gnutls*
 # yum -y install ipvsadm*
 # yum -y install heartbeat*
 # yum -y install heartbeat.x86_64

 

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

 

 

Configure HA:

 

You need to setup the following configuration files on both machines:

 

# vi /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 192.168.2.1 # The gateway apiauth ipfail gid=haclient uid=hacluster node db1.grennan.com node db2.grennan.com 

 

On both machines

NOTE: Assuming 192.168.2.15 is virtual IP for your MySQL resource and mysqld is the LSB resource agent. The host name (db2) should be the secondary server’s name.

 # vi /etc/ha.d haresources

# /etc/ha.d/haresources content
db2.grennan.com LVSSyncDaemonSwap::master Paddr2::192.168.2.15/24/eth0  rbddisk::db Filesystem::/dev/drbd1::/data::ext3 mysqld

# vi /etc/ha.d/authkeys

#/etc/ha.d/authkeys content
auth 2
2 sha1 BigSecretKeyks9wjwlf9gskg905snvl

Now, make your authkeys secure:

# chmod 600 /etc/ha.d/authkeys

Check your work:

On both machines, one at a time, stop MySQL and make sure MySQL does not start when the system reboots (init 6).

If it does, you may need to remove it from the init process with:

 # /sbin/chkconfig --level 2345 MySQL off

Start Heartbeat.

# service heartbeat start

These commands will give you status about this LVS setup:

 # /etc/ha.d/resource.d/LVSSyncDaemonSwap master status
 # ip addr sh
 # service heartbeat status
 # df
 # service mysqld status

Access your HA-MySQL server like:

 # mysql –h 192.168.2.15

Shutdown machine1 to see MySQL up on machine2. ‘shutdown now’

Start machine1 to see MySQL back on machine1.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

shisdew

Listens until think alike

moses.spaceku@yahoo.com / voip ipbx

Hosted PBX, IP-PBX SOHO/ CALL CENTER, VOICE GATEWAY, VOICE CARD, COST EFECTIVE SOLUTIONS (LCR), GSM/CDMA GATEWAY

%d bloggers like this: