Mysql – Keepalived load balancing MySQL. Backup server time-out

keepalivedlinux-networkingload balancingMySQL

Dear beloved people at ServerFault. I'm trying (and failing) to setup a load balanced MySQL server array. Please enlighten me, show me the error in my ways.

Current setup: two MySQL servers with dedicated fixed IP's (10.116.219.47 & 10.116.219.48) and one virtual IP that is shared using keepalived (10.116.219.12). This setup is successful in providing failover. DB2-server quickly takes over if we turn off DB1-server. It's only when adding a virtual_server block to the config, that things start to break.

DB1

vrrp_instance VI_2 {
    state MASTER

    interface eth0
    virtual_router_id 60
    priority 150

    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 3S83hbt200SbwY6
    }
    virtual_ipaddress {
        10.116.219.12
    }
}

DB2

vrrp_instance VI_2 {
    state BACKUP

    interface eth0
    virtual_router_id 60
    priority 100

    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 3S83hbt200SbwY6
    }
    virtual_ipaddress {
        10.116.219.12
    }
}

We've been trying to add load balancing to the keepalived config, but here DB2 stops responding. 

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

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

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

Trying to connect to the virtual ip (10.116.219.12) will alternate on a successful response from DB1, or a time-out from DB2.

Debugging so far:

  • MySQL connecting to the fixed ip's works on both DB1 and DB2. Firewall/MySQL binding is not blocking incoming connections.
  • Set net.ipv4.ip_forward=1 on both DB1 and DB2
  • Listening for TCP inbound connections on DB2 will show up the attempt. (20:46:08.385786 IP 10.116.219.44.46211 > 10.116.219.12.mysql)

So it appears that DB2 MySQL-server refuses to respond, my guess is because the incoming packet destination (the shared virtual ip) is not known to the DB2 server, because DB1 is carrying it as keepalived master.

Best Answer

Since you are using direct routing the virtual IP will need to be configured on both servers at the same time. You can accomplish this by having a second vrrp instance which configures the VIP (10.116.219.12) on the loopback interface of the secondary server. When the connection is forwarded from the load balancer to the secondary server it will respond back out on eth0 as normal.

If you do this you'll need to disable source routing via sysctl. For example:

# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0

Additionally you'll need to change how ARP is announced and responds to requests (more information):

net.ipv4.conf.eth0.arp_ignore = 1
net.ipv4.conf.eth0.arp_announce = 2
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.all.arp_announce = 2

Also, configure rp filtering:

net.ipv4.conf.eth0.rp_filter = 1 # Works for CentOS 5
or
net.ipv4.conf.eth0.rp_filter = 2 # Works for CentOS 6+

The default rp_filter setting can vary between kernel versions, so make sure you choose the correct setting. More information.

Related Topic