High-Availability Mysql with MyISAM support on Ubuntu 12.04

As it turns out, there's quite a few approaches to provide high-availability Mysql - of where the majority doesn't support MyISAM. This is quite annoying when the main use for the Mysql server is to host out-of-the-box open source applications, which utilize MyISAM full-text search more often than not.

For my solution, I've utilized:

• Mysql replication
• Multi-Master Replication Manager for MySQL (MMM)
• Heartbeat to make sure MMM only runs at one server at the time

 The setup:

• Server1: server1.example.com, IP address: 10.0.0.100
• Server2: server2.example.com, IP address: 10.0.0.101
• Virtual IP address: 10.0.0.200 preferred server1, failover at server2
• Virtual IP address: 10.0.0.201 preferred server2, failover at server1

Setting up circular Mysql master replication managed by mmm

The official mmm site has a good tutorial which will get you started. There are however a couple of things I've done differently:

• Ubuntu 12.04 comes with mysql-mmm in package repos: apt-get install mysql-mmm-agent mysql-mmm-monitor
• I only utilize two servers, one for primary write, and one for primary read - thus I have agent and monitor on both servers
• My config for replication is done in /etc/mysql/conf.d/replication.cnf which is included by default for mysql-server package in Ubuntu 12.04
• REMOVE startup links for mysql-mmm-monitor such that only heartbeat will start it: update-rc.d -f mysql-mmm-monitor remove

Setting up high availability for the mmm monitor

The monitor can only run on one node at the time, to avoid collisions. I'm using heartbeat to ensure that the monitor only runs at one node at the time.

apt-get install heartbeat -y
echo -e "auth 3 \n3 md5 secretpasswordhere" > /etc/ha.d/authkeys
chmod 600 /etc/ha.d/authkeys

Server1:

nano /etc/ha.d/ha.cf

# How many seconds between heartbeats
keepalive 2

# Seconds before declaring host dead
deadtime 10
 
# What UDP port to use for udp or ppp-udp communication?
udpport 694

bcast  eth0
mcast eth0 225.0.0.1 694 1 0
ucast eth0 10.0.0.101

# What interfaces to heartbeat over?
udp     eth0
logfacility     local0

# Don't allow ping pong effect on masters
auto_failback off

# Tell what machines are in the cluster
# node must match uname -n
node    server1.example.com
node    server2.example.com

  Server2:

nano /etc/ha.d/ha.cf  

#
#       keepalive: how many seconds between heartbeats
#
keepalive 2
#
#       deadtime: seconds-to-declare-host-dead
#
deadtime 10
#
#       What UDP port to use for udp or ppp-udp communication?
#
udpport        694
bcast  eth0
mcast eth0 225.0.0.1 694 1 0
ucast eth0 192.168.0.100
#       What interfaces to heartbeat over?
udp     eth0
#
#       Facility to use for syslog()/logger (alternative to log/debugfile)
#
logfacility     local0

# Don't allow ping pong effect on masters
auto_failback off 

#
#       Tell what machines are in the cluster
#       node    nodename ...    -- must match uname -n
node    server1.example.com
node    server2.example.com

Now, I'll set up heartbeat to prefer mysql-mmm-monitor script located in /etc/init.d/mysql-mmm-monitor running at server1 if possible, else it will spawn at any other node in the heartbeat cluster. Heartbeat utilize the return codes on the init script for start and stop to determine if it's running or not.

nano /etc/ha.d/haresources 

server1 mysql-mmm-monitor 

 Note: Be sure the name matches 'uname -n' output.

Restart heartbeat to make the changes take effect:

/etc/init.d/heartbeat restart