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
