2

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.

4

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.

  • 1
    Thanks a lot! Your answer makes sense. I'm gonna try it first thing in the morning and report back. Too tired now and more likely to make mistakes than actually fix stuff. – TuxM Oct 4 '14 at 20:15
  • 2
    Could you provide code for "second vrrp instance which configures the VIP on the loopback interface of the secondary server"? If you set them as a group, don't they fail together? If you set them separate, how does it automatically tell the master to not use the VIP on the secondary interface (since it's already on the primary interface).I'm a bit lost. :/ – jnovack Jul 3 '15 at 13:15
0

To extend Gene answer, and to get jnovack being "unlost", what worked for me on CentOS 7 was:

  • On both MySQL servers, create the file /etc/sysconfig/network-scripts/ifcfg-lo:1 containing

    DEVICE=lo:1
    BOOTPROTO=static
    ONBOOT=yes
    IPADDR=***your keepalived virtual IP***
    NETMASK=255.255.255.255
    
  • On both MySQL servers, configure sysctl with (don't forget to sysctl -p to reload)

    net.ipv4.ip_nonlocal_bind=1
    net.ipv4.ip_forward=1
    net.ipv4.conf.default.arp_ignore=1
    net.ipv4.conf.default.arp_announce=2
    net.ipv4.conf.all.arp_ignore=1
    net.ipv4.conf.all.arp_announce=2
    net.ipv4.conf.default.rp_filter=0
    net.ipv4.conf.all.rp_filter=0
    net.ipv4.tcp_syncookies=1
    net.ipv4.conf.all.log_martians=1
    net.ipv4.conf.lo.arp_ignore=1
    net.ipv4.conf.lo.arp_announce=2
    
  • Restart the network service

    systemctl restart network
    # service network restart on CentOS 6
    

(you can check your new virtual interface on loopback executing ifconfig)

  • Restart the keepalived service

    systemctl restart keepalived
    # service keepalived restart on CentOS 6
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.