Apr 18 2007

Mysql Failover

Published by at 12:00 am under Mysql




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.
 

Failover

 

  • 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.


4 responses so far

4 Responses to “Mysql Failover”

  1. Darenon 08 Oct 2008 at 12:39 pm

    Hi there,

    I’m trying out your experience http://www.netexpertise.eu/en/mysql/mysql-failover.html
    I’ve installed everything, and configured somewhat similiar to yours, up till heartbeat.

    After installing mon, everything runs fine. But when I kill the service (mysql) on master note, i don’t get any mail sendout. Nor do I see heartbeat stopping to rollover to the slave.

    Am I missing something?

    Also, for heartbeat, with your config it should start/stop mysql (if i remove ‘mon’) depending on heartbeat’s status

    I notice it sometimes work, it sometimes, doesn’t. Have you experienced that before?

    It seems like so near that I am able to get it to work.. almost there.
    Would love to hear your valuable input.

    Thanks!

  2. Darenon 08 Oct 2008 at 1:02 pm

    In my ha.debug log, I have this when I stop mysql service

    Warning: hostgroup mysql_servers has no watch assigned to it!

    Any idea?

  3. chandraon 07 May 2009 at 9:35 am

    I followed the above document for the mon + heartbeat configuration for both the master and slave server. Configure the heartbeat if fine but the problem is mon settings, I followed the same steps.

    When I start the mon(/etc/init.d/mon start) in both the servers am getting worning message that ” Warning: hostgroup mysql_servers has no watch assigned to it!”
    but it will start the mon process id.
    My mon.cf file as follows,
    alertdir = /usr/local/mon/mon-1.2.0/alert.d
    mondir = /usr/local/mon/mon-1.2.0/mon.d
    statedir = /usr/local/mon/mon-1.2.0/state.d
    maxprocs = 20
    histlength = 100
    randstart = 60s
    # Virtual IP
    hostgroup mysql_servers 10.0.0.90
    watch mysql_servers
    service 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

    Can you tell me what I missed for that worning. When I stop the system or removed the NIC cable then VIP is switching b/w servers, but when I stop the mysql service then it’s not stoping the Heartbeat as I mentioned in the alert script file. So VIP is not switching.

    Can anybody solve this issue.

    Regards,
    Chandra Sekar.

  4. Daveon 16 May 2009 at 10:19 am

    You’re probably better off using Heartbeat2. You don’t need “mon” anymore as HB monitors services by itself, and it’s much more powerful.
    Check this: http://www.netexpertise.eu/en/linux/heartbeat-2-howto.html
    The monitored service is Jboss but it can be Mysql or anything else that has an init script

Comments RSS

Leave a Reply