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.
moses.spaceku@yahoo.com / voip ipbx 