We have a pretty normal single master MySQL setup.

Since we have a read heavy application it makes sense. Everyone writes to the master and reads from a large pool of read-only slaves.

But, with more and more slaves it becomes hard to manage what nodes read from what slaves.  It can get unmanageable pretty quick when configuring the app servers.

If we lose a MySQL slave, we have to redirect all of those servers to the new one… which descends into a bunch of temporary app config or DNS changes that sometimes are not temporary :/

The stuff in this article isn’t my bit of magic, but it is what we have been using in one of our three datacenters for about a year now and am hoping to migrate the others to the scheme. My boss and an ex co-worker set it up an I think it is pretty nice.

On your loadbalancer nodes install keepalived and ipvsadm (ipvsadm can be installed via yum from CentOS’s base repo)

Set up your /etc/keepalived/keepalived.conf to look something like the following:

global_defs {
  notification_email {
    your@email addr
  }
  notification_email_from keepalived@lb1
  smtp_server localhost
  smtp_connect_timeout 30
  # This should be unique.
  router_id lb1
}

vrrp_instance mysql_pool {
  # The interface we listen on.
  interface eth0

  # The default state, one should be master, the others should be set to SLAVE.
  state MASTER

  # This should be the same on all participating load balancers.
  virtual_router_id 1

  priority 101

  # Set the interface whose status to track to trigger a failover.                   
  track_interface {           
    eth0
  }

  # Password for the loadbalancers to share.
  authentication {
    auth_type PASS
    auth_pass password
  }

  # This is the IP address that floats between the loadbalancers.
  virtual_ipaddress {
    10.10.10.99 dev eth0
  }
}

# Here we add the virtal mysql node
virtual_server 10.10.10.99 3306 {
  delay_loop 6
  # Round robin, but you can use whatever fits your needs.
  lb_algo rr
  lb_kind DR
  protocol TCP

  # The server to default to if all others are down, in our case our master.
  sorry_server 10.10.10.100 3306

  # For each server add the following. 
  real_server 10.10.10.1 3306 {
    weight 10
    TCP_CHECK {
      connect_port    3306
      connect_timeout 2
    }
    TCP_CHECK {
      connect_port    9999
      connect_timeout 2
    }
  }
}

Now, on your MySQL slaves:
Install thttpd from EPEL:

yum -y install thttpd

BTW, if you’re doing this in RHEL6 you need to enable the epel-testing repo to get thttpd.

Make it listen on port 9999:

cat <<EOF_THTTPD > /etc/thttpd.conf
dir=/var/www/thttpd/html
chroot
user=thttpd
logfile=/var/log/thttpd.log
pidfile=/var/run/thttpd.pid
port=9999
EOF_THTTPD

Fire it up:

/etc/init.d/thttpd start

Add the shared IP so we can listen for it:

cat <<EOF_LO0 > /etc/sysconfig/network-scripts/ifcfg-lo:1
DEVICE=lo:1
IPADDR=10.10.10.99
NETMASK=255.255.255.255
ONBOOT=yes
NAME=loopback
EOF_LO0

Bring it up:

ifup lo:1

Now you can point ALL your app nodes to read from 10.10.10.99:3306 and they will be round-robin balanced.

On the active loadbalancer it should look like this.

[root@lb1:~)# ipvsadm -Ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  10.10.10.99:3306 rr
  -> 10.10.10.1:3306              Route   10     366        2         
  -> 10.10.10.2:3306              Route   10     375        1   
  -> 10.10.10.3:3306              Route   10     377        1   

My favorite part about this scheme is that any time you want to administrative remove a node from the pool you can just take thttpd down thus

/etc/init.d/thttpd stop

and keepalived chucks it because of the second TCP_CHECK statement in the config above. This is fantastic because you don’t have to shut MySQL down on the node if you need to do work with the live instance, but keep traffic off of it.

Also, because of the magic of VRRP if your main load balancer dies, the second one will take right over