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:
\n1) Slaves – no delay;
\n2) Slaves – up to 120s delay;<\/p>\n
First of all, I have read on the Internet only tutorials for LB conisted of:<\/p>\n
1) Keepalived (for VRRP as backup server)
\n2) HaProxy as LB MySQL.<\/p>\n
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:
\na) HaProxy works on 7-th layer of OSI.
\nb) HaProxy use NAT for LB. In case of MySQL you have to resign from privilages per host. <\/p>\n
on lvs:
\nsysctl -w net.ipv4.ip_forward = 1<\/p>\n
on real servers:
\nifconfig lo:0 10.0.0.237 netmask 255.255.255.255 broadcast 10.0.0.237 up
\nifconfig lo:1 10.0.0.238 netmask 255.255.255.255 broadcast 10.0.0.238 up<\/p>\n
OR with ip command:<\/p>\n
ip addr add 10.0.0.237\/32 broadcast 10.0.0.237 dev lo label lo:1
\nip link set lo:1 up
\nip addr add 10.0.0.238\/32 broadcast 10.0.0.238 dev lo label lo:2
\nip link set lo:2 up<\/p>\n
http:\/\/kb.linuxvirtualserver.org\/wiki\/Using_arp_announce\/arp_ignore_to_disable_ARP
\nsysctl -w net.ipv4.conf.bond0.arp_ignore=1
\nsysctl -w net.ipv4.conf.bond0.arp_announce=2
\nsysctl -w net.ipv4.conf.bond0\/15.arp_ignore=1
\nsysctl -w net.ipv4.conf.bond0\/15.arp_announce=2
\nsysctl -w net.ipv4.conf.default.arp_announce=2
\nsysctl -w net.ipv4.conf.default.arp_ignore=1<\/p>\n
My links:
\nhttp:\/\/www.austintek.com\/LVS\/LVS-HOWTO\/HOWTO\/LVS-HOWTO.arp_problem.html
\nhttp:\/\/linuxdevcenter.com\/pub\/a\/linux\/2005\/09\/01\/keepalived.html?page=2
\nhttp:\/\/gcharriere.com\/blog\/?p=339
\nhttp:\/\/en.wikipedia.org\/wiki\/Address_Resolution_Protocol<\/p>\n","protected":false},"excerpt":{"rendered":"
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) […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7],"tags":[],"_links":{"self":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/582"}],"collection":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=582"}],"version-history":[{"count":18,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/582\/revisions"}],"predecessor-version":[{"id":898,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/582\/revisions\/898"}],"wp:attachment":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=582"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=582"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=582"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}