high availability with keepalived for Mysql

so i needed to setup 2 mysql servers in a HA setup on CentOS 6, i decided to use keepalived (yum search keepalive).

here were the requirements:

– 1 virtual ip
– both servers had to still replicate from each other but only one can take traffic from the vip
– quick failover incase machine went down or was pulled

in this setup:
192.168.168.249 = virtual ip address (where all mysql traffic should come to)
192.168.168.251 = mysql server 1 / lb1
192.168.168.252 = mysql server 2 / lb2

here is my keepalived.conf :

global_defs {
  notification_email {
    me@pissedoffadmins.com
  }
  notification_email_from loadbalancer1
  smtp_server smtp server
  smtp_connect_timeout 5
  router_id lb1
}

vrrp_instance mysql_pool {
  interface eth0
  state MASTER
  virtual_router_id 1
  priority 101
  track_interface {
    eth0
  }

  authentication {
    auth_type PASS
    auth_pass set a password here
  }

  virtual_ipaddress {
    192.168.168.249 dev eth0
  }
}

virtual_server 192.168.168.249 3306 {
  delay_loop 2
  lb_algo rr
  lb_kind DR
  protocol TCP

  sorry_server 192.168.168.251 3306

  real_server 192.168.168.251 3306 {
    weight 10
    TCP_CHECK {
      connect_port    3306
      connect_timeout 1
    }
  }

  real_server 192.168.168.252 3306 {
    weight 10
    TCP_CHECK {
      connect_port    3306
      connect_timeout 1
    }
  }
}

this was the conf file that was installed on balancer one – make sure you make the right changes for it to work on balancer two like setting state to slave and changing router_id.

you can also add a second TCP_CHECK under the 3306 check with a port that you can keep open with netcat. in that case, you can stop netcat and then mysql would be automagically be pulled out of the balancer while still up (in case you needed to do some work on it), but this would require netcat to remain up and running, while keeping that port open at all times.

for the network configuration, we bound the vip to LO:1 :

lo:1      Link encap:Local Loopback  
          inet addr:192.168.168.249  Mask:255.255.255.255
          UP LOOPBACK RUNNING  MTU:16436  Metric:1

here is the ifcfg-lo:1 :

DEVICE=lo:1
IPADDR=192.168.168.249
NETMASK=255.255.255.255
ONBOOT=yes

once keepalive is up and running, we can check the balancers and pools by using ipvsadm (yum search ipvsadm):

# ipvsadm -Ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.168.249:3306 rr
  -> 192.168.168.251:3306         Local   10     13         8         
  -> 192.168.168.252:3306         Route   10     8          93

and for mysql replication, you can google that one since your replications needs may be different.

once all of this is configured, you can ensure that mysql is up and running and being seen by keepalived by running ipvsadm -Ln

YMMV

• I was configured like as your post, but server number 1 not work

  IP Virtual Server version 1.2.1 (size=4096)
  Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port Forward Weight ActiveConn InActConn
  TCP 172.16.0.48:3306 rr
  -> 172.16.0.47:3306 Local 10 0 2
  -> 172.16.0.49:3306 Route 10 0 1

  172.16.0.48 and 172.16.0.47 same server

  When I check connect by command: mysql -uroot -p12312311. -h172.16.0.48
  Just 172.16.0.49 connect, 172.16.0.47 not work and timeout connect

  Reply

  • why were you trying to go to that port ?

    Reply