In this post we are going to setup MySQL cluster with DRBD and heartbeat. For this setup we have two machines. Let’s see setup details for MySQL cluster.

Setup

Machine name
IP address
OS distribution
Packages installed
srv110.linuxphobia.com
Primary MySQL server
192.168.1.110
10.1.1.1
192.168.1.200
CentOS 5.9
heartbeat-2.1.3-3.el5.centos
heartbeat-devel-2.1.3-3.el5.centos
heartbeat-gui-2.1.3-3.el5.centos
heartbeat-ldirectord-2.1.3-3.el5.centos
heartbeat-pils-2.1.3-3.el5.centos
heartbeat-stonith-2.1.3-3.el5.centos
drbd83-8.3.15-2.el5.centos
kmod-drbd83-8.3.15-3.el5.centos
srv101.linuxphobia.com
192.168.1.101
10.1.1.2
CentOS 5.9
heartbeat-2.1.3-3.el5.centos
heartbeat-devel-2.1.3-3.el5.centos
heartbeat-gui-2.1.3-3.el5.centos
heartbeat-ldirectord-2.1.3-3.el5.centos
heartbeat-pils-2.1.3-3.el5.centos
heartbeat-stonith-2.1.3-3.el5.centos
drbd83-8.3.15-2.el5.centos
kmod-drbd83-8.3.15-3.el5.centos

Machines /etc/hosts file

[root@srv110 ~]# cat /etc/hosts
Do not remove the following line, or various programs
that require network functionality will fail.
127.0.0.1               localhost.localdomain localhost
192.168.1.110           srv110.linuxphobia.com  srv110
192.168.1.101           srv101.linuxphobia.com  srv101

DRBD Disk

I have added one hard disk for MySQL data directory on both machines. Just make partition on this hard disk for DRBD, but don’t format this partition in any file-system

Disk /dev/sdb: 5368 MB, 5368709120 bytes
255 heads, 63 sectors/track, 652 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1         652     5237158+  83  Linux

As we have installed drbd kernel module in machines, these machines should include this module. Check this

[root@srv110 ~]# lsmod | grep drbd
drbd                  273204  4
In case, you will not get any details, then add this module 
[root@srv110 ~]# modprobe drbd

Heartbeat Configuration

For this we configure heartbeat for floating MySQL IP and service while any primary machines get down and DRBD for data disk sync between two nodes. So let’s see various configuration files one by one.

Heartbeat configuration files are consisting in /etc/ha.d directory, in which there are three main files. Below box shows various files and files in bold font are files that need to edit. But these files not present in this directory.

[root@srv110 ha.d]# ls
authkeys  ha.cf  harc  haresources  rc.d  README.config  resource.d  shellfuncs

We need to copy these files from /usr/share/doc/heartbeat-<version>

Let’s see the contents of these files which make this setup live

[root@srv110 ha.d]# sed '/^#/d' haresources
srv110  IPaddr::192.168.1.200/24/eth0 drbddisk Filesystem::/dev/drbd0::/mysqldata::ext3  mysqld

With this line in haresources, we set srv110 as primary node, 192.168.1.200 as floating Ip address, floating DRBD disk that should mount on /mysqldata and mysqld service. These all services are floating and will assign on primary node (srv110) and will float on secondary node. Let’s see where we mention nodes names.

ha.cf

[root@srv110 ha.d]# sed '/^#/d' ha.cf
debugfile /var/log/ha-debug
logfile /var/log/ha-log
logfacility     local0
keepalive 2
deadtime 30
warntime 10
initdead 120
udpport 694
bcast   eth0            # Linux
auto_failback on
node    srv110
node    srv101

These various perimeters should mention in ha.cf and node names are displaying at the bottom output. As we mentioned hostname, these should be resolved through any means like in our case we use /etc/hosts file.

authkeys
This file needs not much editing. One important thing about this file is its permission, which should 600

[root@srv110 ha.d]# sed '/^#/d' authkeys
auth 1
1 crc

Let’s see who we use DRBD configuration. In DRBD configuration, we use /etc/drbd.conf file. Let’s see its details

resource drbd0 {
  protocol C; # Slowest and safest protocol
handlers {
    pri-on-incon-degr "echo o > /proc/sysrq-trigger ; halt -f";
    pri-lost-after-sb "echo o > /proc/sysrq-trigger ; halt -f";
    local-io-error "echo o > /proc/sysrq-trigger ; halt -f";
    outdate-peer "/usr/lib/heartbeat/drbd-peer-outdater -t 5";
  }
net {
     after-sb-0pri   discard-younger-primary;
     after-sb-1pri   consensus;
     after-sb-2pri   disconnect;
    }
syncer {
   rate 680M;
 }
  on srv110 {
    device /dev/drbd0;
    disk /dev/sdb1;
    meta-disk internal;
    address 10.1.1.1:7788;
  }
  on srv101 {
    device /dev/drbd0;
    disk /dev/sdb1;
    meta-disk internal;
    address 10.1.1.2:7788;
  }
}

Synching of DRBD Disk

Now let’s start configuring DRBD for MySQL data sync between the two for their own local hard disk. So As I have added one disk on both hosts /dev/sdb, we make a new partition /dev/sdb1 on both the servers. In case these hard disks used earlier in some other applications or formatted in some file-system format. Then please damage partition table of this hard disk with this command

[root@srv110 ~]# dd if=/dev/zero of=/dev/sdb bs=1 count=102400

But please be careful don’t use this command on any other partition and even after start working on DRBD.

Start DRBD service on both the servers with this command

[root@srv110 ~]# service drbd start

You can see its output in /proc/drbd file on both servers

[root@srv110 ~]# cat /proc/drbd

Now create meta-data on both the servers with this command

[root@srv110 ~]# drbdadm create-md drbd0

Now set primary and secondary node with these commands

[root@srv110 ~]# drbdsetup /dev/drbd0 primary –o
[root@srv101 ~]# drbdsetup /dev/drbd0 secondary

You can again check meta-data sync status with /proc/drbd. You can see it start syncing continually. Wait for complete sync. It will start showing UpToDate

[root@srv110 ~]# cat /proc/drbd
version: 8.3.15 (api:88/proto:86-97)
GIT-hash: 0ce4d235fc02b5c53c1c52c53433d11a694eab8c build by mockbuild@builder17.centos.org, 2013-03-27 16:04:08
 0: cs:SyncSource ro:Primary/Secondary ds:UpToDate/Inconsistent C r---n-
    ns:337812 nr:0 dw:0 dr:345984 al:0 bm:20 lo:1 pe:10 ua:64 ap:0 ep:1 wo:b oos:4900320
        [>...................] sync'ed:  6.5% (4784/5112)M
        finish: 0:02:10 speed: 37,404 (37,404) K/sec
[root@srv110 ~]# cat /proc/drbd
version: 8.3.15 (api:88/proto:86-97)
GIT-hash: 0ce4d235fc02b5c53c1c52c53433d11a694eab8c build by mockbuild@builder17.centos.org, 2013-03-27 16:04:08
 0: cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r-----
    ns:4 nr:168 dw:172 dr:17 al:1 bm:3 lo:0 pe:0 ua:0 ap:0 ep:1 wo:b oos:0
[root@srv110 ~]# service drbd status
drbd driver loaded OK; device status:
version: 8.3.15 (api:88/proto:86-97)
GIT-hash: 0ce4d235fc02b5c53c1c52c53433d11a694eab8c build by mockbuild@builder17.centos.org, 2013-03-27 16:04:08
m:res    cs         ro                 ds                 p  mounted     fstype
0:drbd0  Connected  Primary/Secondary  UpToDate/UpToDate  C  

Formating and Mouting of DRBD Disk

After completing sync, use mkfs on /dev/drbd0

[root@srv110 ~]# mkfs.ext3 /dev/drbd0
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
655360 inodes, 1309240 blocks
65462 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=1342177280
40 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736

Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 36 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.

Now mount /dev/drbd0 on /mysqldata directory

[root@srv110 ~]# mount /dev/drbd0 /mysqldata/
[root@srv110 ~]# df -hl
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       18G  5.3G   12G  32% /
/dev/sda1              99M   18M   76M  20% /boot
tmpfs                 506M     0  506M   0% /dev/shm
/dev/drbd0            5.0G  160M  4.6G   4% /mysqldata

Now do chkconfig for DRBD service on both servers

[root@srv110 ~]# chkconfig drbd on
[root@srv110 ~]# chkconfig --list drbd
drbd            0:off   1:off   2:on    3:on    4:on    5:on    6:off

Configuration of MySQL

Now you can have your MySQL data in this directory as it is replication on both the servers. For this I did some changes in my MySQL configuration file.

[root@srv110 ~]# sed -e '/^#/d' -e '/^$/d' /etc/my.cnf
[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
datadir=/mysqldata/data           #important perimeter that used this DRBD configuration, you can #use other perimeter as per requirement
pid-file=/var/lib/mysql/srv101.pid
thread_concurrency = 8
skip-federated
log-bin=mysql-bin
server-id       = 1
default_storage_engine=InnoDB
[mysqldump]
quick
max_allowed_packet = 16M

After all this you need to start heartbeat configuration and chkconfig it on both servers

Starting of Services

[root@srv110 ~]# service heartbeat restart
Stopping High-Availability services:
                                                           [  OK  ]
Waiting to allow resource takeover to complete:
                                                           [  OK  ]
Starting High-Availability services:
2013/04/16_18:50:38 INFO:  Resource is stopped
                                                           [  OK  ]
[root@srv110 ~]# chkconfig heartbeat on
[root@srv110 ~]# chkconfig --list heartbeat
heartbeat       0:off   1:off   2:on    3:on    4:on    5:on    6:off

Now we can check our mysql services start on primary node and an additional IP address 192.168.1.200 assigned on eth0 for this machine.

[root@srv110 ~]# ip addr show eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000
    link/ether 00:0c:29:23:99:38 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.110/24 brd 192.168.1.255 scope global eth0
    inet 192.168.1.200/24 brd 192.168.1.255 scope global secondary eth0:0
    inet6 fe80::20c:29ff:fe23:9938/64 scope link
       valid_lft forever preferred_lft forever
[root@srv110 ~]# service mysqld status
mysqld (pid 8962) is running...

You can check by rebooting any of machines, so this service and IP Address should float on other machine.