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.
thanks for the good work 🙂
when i did the setup for the first time i used lcmc to setup heartbeat and pacemaker for automatic failover.
Thank you for your comments – Appreciate it . I am not a big fan of GUI especially LCMC – AS that confuse me a lot I have more control over command line and I feel more comfortable this way.