Netexpertise

Important note:
Heartbeat is now obsolete and has moved to a new stack available on Clusterlabs. For simple high availability project using a virtual IP, try out keepalived that does monitoring and failover with just a simple configuration file.

When a Mysql server contains critical data, it’s always a good idea to rely on more than one server.
Implementing a cluster would be ideal in this situation and could offer a high availability setup.
It gives the option to replace a node if it fails with no disruption of service or even data loss.
The fact is it becomes much less interesting financially considering a minimum of 4 machines is required, and a lot of time as well.
I’d rather have a replication setup but a manual fix needs to be done to switch the traffic from the master to the slave. This article gives the ability to switch the traffic automatically.

Initial Setup

Let’s assume we have a standalone server server running on 192.168.0.2
After modification, the live server will take IP 192.168.0.4
A slave will be added with IP 192.168.0.5
192.168.0.2 becomes the virtual IP of the “cluster”. Changes are transparent to the programs connecting to the database. They will still use IP 192.168.0.2 as originally written.

• Mysql replication needs to be implemented with the following settings:
  Master 192.168.0.2
  Slave 192.168.0.5
  The master keeps the original IP – for now – so all applications still connect to the same machine.
  Check this other tutorial about replication here.
• Firewalls updates
  Apply the master’s rules on the slave ie 192.168.0.5 identical to 192.168.0.2 so each single program can connect to the slave if requested.
  This method could be used to implement load-balancing in a quick and easy way.
  Allow all traffic between master and slave. This is important to get Heartbeat and Mon to check services, as well as the Mysql replication protocol.
• Disable Selinux on the 2 machines
  It creates problems with heartbeat
  In /etc/selinux/config
  set “SELINUX=disabled”
  Changes need unfortunately a server reboot to be applied

Heartbeat

Download and install heartbeat from the Linux high availability project website at http://www.linux-ha.org/Heartbeat.
RPMs are available for Fedora Core.
sudo yum install heartbeat

Configuration files are located in /etc/ha.d/. You need to create those 3 files on the two servers:

cat > /etc/ha.d/authkeys
auth 1
1 crc

Set appropriate rights to the file

chmod 600 /etc/ha.d/authkeys

cat > /etc/ha.d/haresources
master 192.168.0.2 mysqld mon

haresources must contain the virtual IP.

cat > /etc/ha.d/ha.cf
logfile /var/log/ha-log
keepalive 2
# the time to wait before declaring a node to be dead
deadtime 10
# this is the time before heartbeat will start the resources the first time it starts ie mon,mysql...
initdead 20
bcast eth0
node master.mydomain.com
node slave.mydomain.com
# Set this to "on" only if you have a multi-master setup
auto_failback off
# will ping the default gateway to check the network connectivity
ping 192.168.0.1
respawn hacluster /usr/lib64/heartbeat/ipfail

Note Nodes should be literally taken from `uname -n`. This is fairly important or heartbeat won’t work.

Note /etc/hosts file on BOTH machines should contain entries for the 2 hosts
192.168.0.4 master.mydomain.com
192.168.0.5 slave.mydomain.com

Note /usr/lib64/heartbeat/ipfail is for a 64 bit-architecture. Remove ’64’ if you’re on a 32.

Mon

Download MON

cd /usr/local/src
wget ftp://ftp.kernel.org/pub/software/admin/mon/mon-0.99.2.tar.gz

Check if a new version is available on http://www.kernel.org/software/mon/. 0.99.2 was the most recent stable version when I last checked.
Uncompress the tarball and move it to /usr/local or your usual favourite location

tar xvfz mon-0.99.2.tar.gz
mv mon-0.99.2 /usr/local/mon

Copy the configuration directory to /etc

mv /usr/local/mon/etc /etc/mon

Install Perl and the modules that Mon requires

- DBI
- DBD::mysql
- Time::Period
- Time::HiRes
- Convert::BER
- Mon::Client

Modules can be found on the CPAN website at http://www.perl.com/CPAN/modules/index.html.
Download and install the appropriate packages following instructions given in INSTALL. Usually:

gunzip  <module>.tar.gz
tar -xvf  <module>.tar
cd <module-dir>
perl Makefile.pl
make
make test
make install

or via

perl -MCPAN -e shell

and run

install <module>

Note Make sure Mysql libraries are in the path before installing DBD::mysql.

Create the Mon configuration file

cat > mon.cf
alertdir   = /usr/local/mon/alert.d
mondir     = /usr/local/mon/mon.d
statedir   = /usr/local/mon/state.d
maxprocs    = 20
histlength = 100
randstart = 60s
# Virtual IP
hostgroup mysql_servers 192.168.0.2
watch mysql_servers
  mysql
    ## Mon will do a test on Mysql port every 60 seconds
    interval 1m
    monitor mysql.monitor
    period wd {Mon-Sun}
      alert bring-ha-down.alert
      alert mail.alert -S "Host1 MYSQL is down" admin@example.com
      upalert mail.alert -S "Host1 MYSQL server is back up" admin@example.com
      alertevery 600s
      ## Sends an alert after 3 failures
      ## ie 2mn
      alertafter 3

Create a script to bring heartbeat down if Mysql service becomes unavailable

cat > /usr/local/mon/alert.d/bring-ha-down.alert
/etc/rc.d/init.d/heartbeat stop

Change the script’s name to activate mysql mode by default

cd mon.d
mv msql-mysql.monitor mysql.monitor

Create a user authorized to access the test database

mysql> GRANT ALL PRIVILEGES ON test.* TO alive@'%' IDENTIFIED BY 'mypassword';

Edit mysql.server accordingly and add a line to connect to the ‘test’ database

$options{database} ||= "test";

This option was missing in my default configuration file.

Create a startup script for mon. Here’s a sample:

cat > /etc/rc.d/init.d/mon
#!/bin/bash
MON_HOME=/usr/local/mon
case "$1" in
    start)
        if [ -f $MON_HOME/mon.pid ]; then
                echo "mon already started"
                exit
        fi
        echo "Starting Mon"
    $MON_HOME/mon -c $MON_HOME/mon.cf -L $MON_HOME -P $MON_HOME/mon.pid &
        ;;
    stop)
    if [ -f $MON_HOME/mon.pid ]; then
    echo "Stopping Mon"
        kill -9 `cat $MON_HOME/mon.pid`
        rm  -f $MON_HOME/mon.pid
    else
        echo "no server pid, server doesn't seem to run"
    fi
    ;;
    *)
    echo "Usage: $0 {start|stop|status|reload|restart}"
    exit 1
esac
exit 0

Make it executable
chmod 755 /etc/rc.d/init.d/mon

Run Applications

Make sure Mysql is running on the 2 boxes and replication is up-to-date before doing anything else.
Change the Master’s IP to 192.168.0.4 in the config files and restart the 2 servers (master first).
Fire up Mysql and Heartbeat on the master
/etc/rc.d/init/mysqld start
/etc/rc.d/init.d/heartbeat start

# You can check hearbeat logs in /var/log/ha-log
The virtual IP will be assigned to eth0:0 after a few seconds
This can be checked with `ifconfig`

eth0:0    Link encap:Ethernet  HWaddr 00:13:72:5D:1D:1F
          inet addr:192.168.0.2  Bcast:192.168.0.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          Base address:0xdcc0 Memory:fe6e0000-fe700000

If you experience problems, check messages for Mon in /var/log/messages as well
Do the same on the slave

You can then try to turn the machine off, disconnect the cable or shutdown Mysql;
The virtual IP will migrate to the second server after 2mn (this time can be reduced in mon.cf 1mn and 3 checks.
It is better to give some time in case Mysql becomes unresponsive for a short period of time while there are traffic spikes)

Some basic load-balancing is now possible redirecting all read commands to the slave (192.168.0.5), leaving the writes to the master.
You need to monitor that the slave is always live in this case though because the failover won’t apply to the reads.

Tags: cluster, Database, Mysql

Comments RSS