Febru Wasono: HAProxy keepalived failover mysql galera

High Availability Services

on Enterprise level, application software should be able to guarantee that its services will not down. Especially on telecom industries, we are very consider about HA (high availability) services.

This HA is applicable for all service that need to reach almost 100% service alive, can be for database with cluster, app server, or any others.

How to double app services

You can setup HA by proxying your app-intance with session stickiness to keep all session on each client running well. Can be with F5 or other opensource loadbalancer. It can be setup as round robin or fail over then.

Keepalive the load balancer

even though you create load balancer on your apps, there still have any conditions that may make your balancer going down, thats why you need to make some backup of your balancer. Thats why we need keepalived.

well, on this case, i want to setup HAProxy as balancer, then keepalived to backup the balancer for mysql services. All are opensources software, lets try :

HAProxy keepalived failover mysql galera

Let say :
h1 : 192.168.43.201
h2 : 192.168.43.202
virtual ip : 192.168.43.200

create user for mysql

mysql -u root -p
grant all on *.* to root@'%' identified by 'Passw0rd' with grant option;
insert into mysql.user (Host,User) values ('192.168.43.201','haproxy');
insert into mysql.user (Host,User) values ('192.168.43.202','haproxy');
flush privileges;
exit;

DO on BOTH :
sudo update
sudo apt-get install mysql-client keepalived haproxy -y
sudo vim /etc/sysctl.conf
net.ipv4.ip_nonlocal_bind=1

you can check with :
sudo sysctl -p

CREATE FILE : (router_id to be the hostname)
sudo vim /etc/keepalived/keepalived.conf

global_defs {
router_id h1
}
vrrp_script haproxy {
script "killall -0 haproxy"
interval 2
weight 2
}
vrrp_instance 50 {
virtual_router_id 50
advert_int 1
priority 101
state MASTER
interface eth0
virtual_ipaddress {
    192.168.43.200 dev eth0
}
track_script {
    haproxy
}
}

ON NODE 2:
global_defs {
router_id h2
}
vrrp_script haproxy {
script "killall -0 haproxy"
interval 2
weight 2
}
vrrp_instance 50 {
virtual_router_id 50
advert_int 1
priority 102
state SLAVE
interface eth0
virtual_ipaddress {
    192.168.43.200 dev eth0
}
track_script {
    haproxy
}
}

ON H1:
sudo vim /etc/haproxy/haproxy.cfg

global
        log 192.168.43.201 local0
        stats socket /var/lib/haproxy/stats
        maxconn 10000
        chroot /var/lib/haproxy
        user haproxy
        group haproxy
        daemon

defaults
        log     global
        mode    http
        option httplog
        option dontlognull
        contimeout 5000
        clitimeout 50000
        srvtimeout 50000
        errorfile 400 /etc/haproxy/errors/400.http
        errorfile 403 /etc/haproxy/errors/403.http
        errorfile 408 /etc/haproxy/errors/408.http
        errorfile 500 /etc/haproxy/errors/500.http
        errorfile 502 /etc/haproxy/errors/502.http
        errorfile 503 /etc/haproxy/errors/503.http
        errorfile 504 /etc/haproxy/errors/504.http

listen stats 192.168.43.201:80
        mode http
        stats enable
        stats uri /stats
        stats realm HAProxy\ Statistics
        stats auth admin:Passw0rd1

ON H2:
sudo vim /etc/haproxy/haproxy.cfg

global
        log 192.168.43.202 local0
        stats socket /var/lib/haproxy/stats
        maxconn 10000
        chroot /var/lib/haproxy
        user haproxy
        group haproxy
        daemon

defaults
        log     global
        mode    http
        option httplog
        option dontlognull
        contimeout 5000
        clitimeout 50000
        srvtimeout 50000
        errorfile 400 /etc/haproxy/errors/400.http
        errorfile 403 /etc/haproxy/errors/403.http
        errorfile 408 /etc/haproxy/errors/408.http
        errorfile 500 /etc/haproxy/errors/500.http
        errorfile 502 /etc/haproxy/errors/502.http
        errorfile 503 /etc/haproxy/errors/503.http
        errorfile 504 /etc/haproxy/errors/504.http

listen stats 192.168.43.202:80
        mode http
        stats enable
        stats uri /stats
        stats realm HAProxy\ Statistics
        stats auth admin:Passw0rd1

ON BOTH
sudo vim /etc/default/haproxy

# Set ENABLED to 1 if you want the init script to start haproxy.
ENABLED=1

ON BOTH
sudo service keepalived restart
sudo service haproxy restart

TESTING :
ip a | grep eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    inet 192.168.43.202/24 brd 192.168.43.255 scope global eth0
    inet 192.168.43.200/32 scope global eth0

Once you’ve completed all of these steps on both nodes, you should now have a highly available load balancer pair. At this point, our VIP should be active on one node (assuming that you built node 1 first, it should be active on that node).

NOW SET FOR MYSQL-Galera:

mysql -h 192.168.43.200 -u root -p

then turn off one machine, it should be swithed automatically to the next side

ADD THIS ON BOTH:

listen galera 192.168.43.200:3306
        balance source
        mode tcp
        option tcpka
        option mysql-check user haproxy
        server m1 192.168.43.203:3306 check weight 1
        server m2 192.168.43.204:3306 check weight 1

..show all:.

Febru Wasono

Feb 23, 2015

HAProxy keepalived failover mysql galera

High Availability Services

How to double app services

Keepalive the load balancer

High Availability Services

How to double app services

Keepalive the load balancer

No comments:

Post a Comment

Your Search Terms

Contact Us