DRBD+Heartbeat+Mysql

MySQL high availability cluster with DRBD+Heartbeat+MySQL – You can also download the cleaner version of this document by clicking here – Download here

1) Set up hostnames and network

On all Nodes. Please copy this file on all servers/nodes

Run this on db1 and db2 server

nano /etc/hosts

##### IP’s of MySQL DB servers #####

192.168.1.210 db1.wcws.net db1

192.168.1.211 db2.wcws.net db2

Run this on db1 and db2 server

ssh-keygen
ssh-keygen
ssh-copy-id [email protected]
ssh-copy-id [email protected]

In my configuration I, Installed CentOS with just one hard drive on both servers and the drive I just to boot centos was 10GB therefore I have added another drive with 10GB Space space, please note each server hard drive must be identical in size other wise you will run into problems.

I am assuming you have already added a secondary disk with 10GB so lets start some serious configuration and feel yourself proud.

Following these steps and you will thank me later

[[email protected] ~]# nano /etc/selinux/config

SELINUX=disabled

chkconfig iptables off

Make sure your firewall is OFF – you can also do that by running setup command on your shell and follow the steps

Partion setup on both server identical same with fdisk

Once I added a drive my drive was named as /dev/sdb – please verify that before you do any thing

So in my server after I added an additional disk my drive was named as /dev/sdb which you can also verify by running fdisk –l

Lets create partition

fdisk /dev/sdb

primary partition – press p

Partition number (1-4): 1

Press ENTER FOR ALL DEFAULT SETTINGS

Press t and type 8e – HIT ENTER
TYPE w to write  once done simply type

partprobe

Secondary we will create some logical volume

pvcreate /dev/sdb1

vgcreate vgdb /dev/sdb1

Create Logical volume partition

lvcreate -L +10000M -n lvdb vgdb

RUN ABOVE STEP ON BOTH DB1 & DB2 Server and please make sure the drive size are exact and accurate.

Once everything is done simply reboot your server

Now the fun part J YEA BABY

rpm -Uvh http://elrepo.org/elrepo-release-6-4.el6.elrepo.noarch.rpm

nano /etc/yum.repos.d/elrepo.repo

[elrepo]

name=ELRepo.org Community Enterprise Linux Repository – el6

baseurl=http://elrepo.org/linux/elrepo/el6/$basearch/

mirrorlist=http://elrepo.org/mirrors-elrepo.el6

enabled=0

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-elrepo.org

protect=0

rpm –Uvh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

yum –enablerepo=elrepo install drbd83-utils kmod-drbd83 mysql mysql-server heartbeat heartbeat-pils heartbeat-stonith –y

Once installed simply run the following command as we don’t want mysql to start on boot time

chkconfig mysqld off
chkconfig heartbeat off
chkconfig drbd off

now reboot your server – PLEASE NOTE ALL ABOVE COMMANDS MUST BE RUN ON BOTH DB1 and DB2 Server.

Now your servers are up and running do the following

[[email protected] ~]modprobe drbd

[[email protected] ~]modprobe drbd

[[email protected] ~]echo “modprobe drbd” > /etc/rc.local

[[email protected] ~]echo “modprobe drbd” > /etc/rc.local

[[email protected] ~] nano /etc/drbd.conf

and delete all the lines from your current drbd.conf file and copy the following

global {usage-count no; } common { syncer { rate 3M; } } resource r0 { protocol C; handlers { split-brain “/usr/lib/drbd/notify-split-brain.sh root”; pri-on-incon-degr “/usr/lib/drbd/notify-pri-on-incon-degr.sh; /usr/lib/drbd/notify-emergency-reboot.sh; echo b > /proc/sysrq-trigger ; reboot -f”; pri-lost-after-sb “/usr/lib/drbd/notify-pri-lost-after-sb.sh; /usr/lib/drbd/notify-emergency-reboot.sh; echo b > /proc/sysrq-trigger ; reboot -f”; local-io-error “/usr/lib/drbd/notify-io-error.sh; /usr/lib/drbd/notify-emergency-shutdown.sh; echo o > /proc/sysrq-trigger ; halt -f”; } startup { degr-wfc-timeout 120; } disk { on-io-error detach; } net { # The following lines are dedicated to handle # split-brain situations (e.g., if one of the nodes fails) after-sb-0pri discard-zero-changes; # If both nodes are secondary, just make one of them primary after-sb-1pri discard-secondary; # If one is primary, one is not, trust the primary node after-sb-2pri call-pri-lost-after-sb; # If there are two primaries, make the unchanged one secondary rr-conflict disconnect; } syncer { rate 3M; al-extents 257; } on db1.wcws.net { device /dev/drbd0; disk /dev/vgdb/lvdb; address 192.168.1.210:7788; #DEFINE YOUR DB1 IP flexible-meta-disk internal; } on db2.wcws.net { device /dev/drbd0; disk /dev/vgdb/lvdb; address 192.168.1.211:7788; #DEFINE YOUR DB2 IP flexible-meta-disk internal; } }

Once above file is saved – now simply copy that file to DB2 server

scp /etc/drbd.conf db2:/etc/

Run the following on both servers

echo “modprobe drbd” >> /etc/rc.local

now lets start drbd on both db1 and db2

service drbd start

Run the following commands on both db1 & db2

drbdadm create-md r0

drbdadm attach r0

drbdadm syncer r0

drbdadm connect r0

On Primary Node only

[[email protected] ~]#drbdadm — –overwrite-data-of-peer primary r0

On both Nodes: 

[[email protected] ~]#drbdadm up all

[[email protected] ~]#drbdadm up all

On Primary Node only

[[email protected] ~]#drbdadm — primary all #### ON Node one Only ####

You are done with DRBD Part – now check the sync status by

[[email protected] ~]#watch cat /proc/drbd

only on db1 ########## Primary Node ########

[[email protected] ~]#mkfs.ext3 /dev/drbd0

[[email protected] ~]#mkdir /data/

[[email protected] ~]#mount /dev/drbd0 /data/

On db2 ####### Secondry Node #######

[[email protected] ~]#mkdir /data

[[email protected] ~] nano /etc/ha.d/ha.cf ## Create this file and copy this text ##

logfacility local0
keepalive 2
#deadtime 30 # USE THIS!!!
deadtime 10
bcast eth0
#serial /dev/ttyS0
baud 19200
auto_failback off
node db1.wcws.net
node db2.wcws.net

 

Server db1 

 [[email protected] ~]#vi /etc/ha.d/haresources

db1.wcws.net IPaddr::192.168.1.219/32/eth0 drbddisk::r0 Filesystem::/dev/drbd0::/data::ext3 mysqld

Server db2:

 [[email protected] ~]#vi /etc/ha.d/haresources

db2.wcws.net IPaddr::192.168.1.219/32/eth0 drbddisk::r0 Filesystem::/dev/drbd0::/data::ext3 mysqld

On Both Servers:

 [[email protected] ~]#vi /etc/ha.d/authkeys

auth 3
3 md5 wcws

[[email protected] ~]#chmod 600 /etc/ha.d/authkeys

[[email protected] ~]#scp /etc/ha.d/authkeys db2:/etc/ha.d/authkeys

[[email protected] ~]#chkconfig heartbeat on

Mysql Configuration.    cp /etc/my.cnf /etc/my.cnf.orig   vi /etc/my.cnf   [mysqld] # datadir=/var/lib/mysql datadir=/data/mysql #socket=/var/lib/mysql/mysql.sock socket=/data/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1   [mysql.server] user=mysql #basedir=/var/lib basedir=/data   [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid   [mysql] socket=/data/mysql/mysql.sock

 

Run the following command on both servers db1 & db2

chkconfig drbd on

Copy my.cnf file on db2 server

scp /etc/my.cnf db2:/etc/

 Now simply restart your server and connect your web server to your FLOATING IP which we define in haresource file i.e. 192.168.1.219 – Connect to MYSQL setup your root password after you create your database In MySQL simply run this command to give grant access to your DB

GRANT ALL ON store.* TO [email protected]’192.168.1.212′ IDENTIFIED BY ‘YOURMYSQLROOTPASSWORD’;

In my case 212 IP is – IP of my web server

 

One last thing

nano /etc/aliases

go at the very bottom and on both db1 & db2 server and change root  like this

root: [email protected]

– Congrats you just setup a DRBD HIGH AVAILABILITY

Please do leave me comments and let me know what you think about this tutorial and if that help’s you – I would love to get your feedbacks.