Load balance for MySQL (HaProxy vs. pure Keepalived)

My basic need was to balance to traffic between MySQL Slaves according to the application needs. Due to the MyISAM storage on some databases I let to some applications the delay up to 120 s. (I measured the delay with MySQL command `show slave status`. I created two clusters:
1) Slaves – no delay;
2) Slaves – up to 120s delay;

First of all, I have read on the Internet only tutorials for LB conisted of:

1) Keepalived (for VRRP as backup server)
2) HaProxy as LB MySQL.

I configured HaProxy and it works fine. The GUI is beautiful! However, my client was to used keepalived for another purpose so I wanted to avoid to introduce some additional applications. I choose the Keepalived taking into considerations the following conditions:
a) HaProxy works on 7-th layer of OSI.
b) HaProxy use NAT for LB. In case of MySQL you have to resign from privilages per host.

on lvs:
sysctl -w net.ipv4.ip_forward = 1

on real servers:
ifconfig lo:0 10.0.0.237 netmask 255.255.255.255 broadcast 10.0.0.237 up
ifconfig lo:1 10.0.0.238 netmask 255.255.255.255 broadcast 10.0.0.238 up

OR with ip command:

ip addr add 10.0.0.237/32 broadcast 10.0.0.237 dev lo label lo:1
ip link set lo:1 up
ip addr add 10.0.0.238/32 broadcast 10.0.0.238 dev lo label lo:2
ip link set lo:2 up

http://kb.linuxvirtualserver.org/wiki/Using_arp_announce/arp_ignore_to_disable_ARP
sysctl -w net.ipv4.conf.bond0.arp_ignore=1
sysctl -w net.ipv4.conf.bond0.arp_announce=2
sysctl -w net.ipv4.conf.bond0/15.arp_ignore=1
sysctl -w net.ipv4.conf.bond0/15.arp_announce=2
sysctl -w net.ipv4.conf.default.arp_announce=2
sysctl -w net.ipv4.conf.default.arp_ignore=1

My links:
http://www.austintek.com/LVS/LVS-HOWTO/HOWTO/LVS-HOWTO.arp_problem.html
http://linuxdevcenter.com/pub/a/linux/2005/09/01/keepalived.html?page=2
http://gcharriere.com/blog/?p=339
http://en.wikipedia.org/wiki/Address_Resolution_Protocol

  1. No comments yet.

  1. No trackbacks yet.