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.

Tweet