Using LVS (Linux Virtual Server) + keepalived (Direct Route mode) for Server Load Balancing on MariaDB Galera Cluster

參考 Reference
http://blog.xuite.net/misgarlic/weblogic/249523049-Using+keepalived+to+simulate+SLB+DSR
http://www.keepalived.org/pdf/sery-lvs-cluster.pdf

### Version Information
All Servers OS : CentOS 6.7
ipvsadm : 1.26
keepalived : 1.2.13
MariaDB : 5.10
MariaDB Galera Cluster : 25.3.9

### Network Infrastructure
Load Balance Server (SLB_1) : 192.168.1.10 [priority  50, active]
Load Balance Server (SLB_2) : 192.168.1.20 [priority 100, standby]

MariaDB Galera Cluster Server (DB_1) : 192.168.1.201:3306
MariaDB Galera Cluster Server (DB_2) : 192.168.1.202:3306
MariaDB Galera Cluster Server (DB_3) : 192.168.1.203:3306

Load Balance VIP (VIP_1) : 192.168.1.110:3306 (for read, mapping to DB_1[weight 4] & DB_2[weight 4] & DB_3[weight 1])
Load Balance VIP (VIP_2) : 192.168.1.111:3306 (for write, mapping to DB_3)

### Config

## Execute following commands on DB_1, DB_2, DB_3, SLB_1 and SLB_2
# set iptables
sudo /sbin/iptables -A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT 

sudo /sbin/service iptables save
sudo /sbin/service iptables restart

## Execute following commands on DB_1, DB_2 and DB_3
# set sysctl parameters
sudo /bin/cat << EOF >> /etc/sysctl.conf

# Prevent ARP action on lo interface for keepalived Direct-Route mode
net.ipv4.conf.lo.arp_ignore = 1
net.ipv4.conf.lo.arp_announce = 2
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.all.arp_announce = 2
EOF

sudo /sbin/sysctl -p
# bind VIPs on lo interface
sudo /bin/cat << EOF > /etc/sysconfig/network-scripts/ifcfg-lo:vip110
DEVICE=lo:vip110
IPADDR=192.168.1.110
NETMASK=255.255.255.255
ONPARENT=yes
BOOTPROTO=none
TYPE=Ethernet
EOF

sudo /bin/cat << EOF > /etc/sysconfig/network-scripts/ifcfg-lo:vip111
DEVICE=lo:vip111
IPADDR=192.168.1.111
NETMASK=255.255.255.255
ONPARENT=yes
BOOTPROTO=none
TYPE=Ethernet
EOF

sudo /sbin/ifup lo:vip110
sudo /sbin/ifup lo:vip111

sudo /bin/ls -la /etc/sysconfig/network-scripts/ifcfg-lo*
sudo /sbin/ip addr show lo

## Execute following commands on SLB_1 and SLB_2
# set iptables
sudo /sbin/iptables -I INPUT -p vrrp -m comment --comment "VRRP - keepalived HA detecting" -j ACCEPT

sudo /sbin/service iptables save
sudo /sbin/service iptables restart

# install ipvsadm
sudo /usr/bin/yum -y install ipvsadm
sudo /sbin/chkconfig ipvsadm on

sudo /sbin/ipvsadm
# install and config keepalived
sudo /usr/bin/yum -y install keepalived
sudo /sbin/chkconfig keepalived on

sudo /bin/mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.`/bin/date +%y%m%d`
sudo /bin/cat << EOF > /etc/keepalived/keepalived.conf
global_defs {
   notification_email {
     user1@xxx.com
     user2@xxx.com
   }
   notification_email_from user3@xxx.com
   smtp_server xxx.xxx.xxx.xxx
   smtp_connect_timeout 30
   router_id MYRTID
}

vrrp_sync_group G1 {
   group {
     VI_1
   }
}

vrrp_instance VI_1 {
   state BACKUP
   interface eth0
   lvs_sync_daemon_interface eth0
   smtp_alert
   ## set virtual_router_id between 1 to 255
   virtual_router_id MYVRTID
   priority MYPRIORITY
   advert_int 1
   authentication {
     auth_type PASS
     auth_pass 11223344
   }
   virtual_ipaddress {
     192.168.1.110
     192.168.1.111
   }
}

virtual_server 192.168.1.110 3306 {
  delay_loop       5      # health check interval
  lb_algo          wrr    # weighted round robin
  lb_kind          DR     # direct route mode
  protocol         TCP

  real_server      192.168.1.201 3306 {
    weight                 4
    inhibit_on_failure
    TCP_CHECK {
      connect_port         3306
      connect_timeout      3
    }
  }

  real_server      192.168.1.202 3306 {
    weight                 4
    inhibit_on_failure
    TCP_CHECK {
      connect_port         3306
      connect_timeout      3
    }
  }

  real_server      192.168.1.203 3306 {
    weight                 1
    inhibit_on_failure
    TCP_CHECK {
      connect_port         3306
      connect_timeout      3
    }
  }

}

virtual_server 192.168.1.111 3306 {
  delay_loop       5      # health check interval
  lb_algo          wrr    # weighted round robin
  lb_kind          DR     # direct route mode
  protocol         TCP

  real_server      192.168.1.203 3306 {
    weight                 4
##    inhibit_on_failure
    TCP_CHECK {
      connect_port         3306
      connect_timeout      3
    }
  }
}
EOF

## Execute following commands on SLB_2
# config and run keepalived
sudo /bin/sed -i s/MYRTID/rt`/bin/uname -n`/ /etc/keepalived/keepalived.conf
sudo /bin/sed -i s/MYVRTID/110/ /etc/keepalived/keepalived.conf

sudo /bin/sed -i s/MYPRIORITY/100/ /etc/keepalived/keepalived.conf

sudo /sbin/service keepalived start

## Execute following commands on SLB_1
# config and run keepalived
sudo /bin/sed -i s/MYRTID/rt`/bin/uname -n`/ /etc/keepalived/keepalived.conf
sudo /bin/sed -i s/MYVRTID/110/ /etc/keepalived/keepalived.conf

sudo /bin/sed -i s/MYPRIORITY/50/ /etc/keepalived/keepalived.conf

sudo /sbin/service keepalived start