MySQL High Availability with Keepalived.md

MySQL High Availability with Keepalived and HAProxy

Keepalived along with HAProxy would able to do the load-balanced High Availability Cluster. With these we can do the cluster for any application service whether MySQL or any other application service as per needs.

Keepalived - LVS and VRRP High Availability Monitor
HAProxy - High Availability Load Balancer

Let's Assume below mentioned Configuration details

Virtual IP : 192.168.0.100
Load Balancer Server IP : 192.168.0.25 (loadb1)
Load Balancer Server IP : 192.168.0.26 (loadb2) - For Fail Over of Load balancer
App Server IP1 : 192.168.0.1 (appserver1)
App Server IP2 : 192.168.0.2 (appserver2)

Keepalived

A Linux Virtual Server by balancing the IP load across a set of real servers. LVS runs on a pair of equally configured computers: one that is an active LVS router and one that is a backup LVS router.

A Virtual Router Redundancy Protocol (VRRP) configuration can be used as a high availability solution to make sure that a network maintains connectivity with the internet (or with other networks) even if the default router for the network fails.

VIP: Virtual IP addresses (or VIPs) allow you to use multiple IPs on a single physical network interface.

Configuration of Keepalived

Install the Keepalived Package on Load Balancing Server which would balance the load where HAProxy would running.

On Load Balancer Server 1 (loadb1) install and configured it as Master Load Balancer.
```
yum install keepalived
```

Move existing configuration as backup configuration.

    mv /etc/keepalived/keepalived.conf{,.back}
    vim /etc/keepalived/keepalived.conf

As you can see, both load Balancer server will use 192.168.0.100, shared IP address. This virtual IP will move on between servers, so we need to make some trick and enable net.ipv4.ip_nonlocal_bind sysctl option, to allow system services binding on the non-local IP.
```
 vi /etc/sysctl.conf
 net.ipv4.ip_nonlocal_bind=1
```

Create new configuration as mentioned below according to our server IP configuration.

global_defs {
notification_email {
    [email protected]
}
notification_email_from [email protected]
smtp_server 127.0.0.1
smtp_connect_timeout 30
}
vrrp_instance VRRP1 {
    state MASTER
#   Specify the network interface to which the virtual address is assigned
    interface eth0
#   The virtual router ID must be unique to each VRRP instance that you define
    virtual_router_id 71
#   Set the value of priority higher on the master server than on a backup server
    priority 200
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1234
    }
virtual_ipaddress {
192.168.0.100
}
}

Restart the keepalived daemon once after successful configuration. Also enable the daemon on startup.
```
    systemctl restart keepalived
    systemctl enable keepalived
```
Now load balancer(loadb1) physical network interface would have assigned with virtual IP address which is mentioned in the above configuration. Verify the same with current IP address using below command.
```
    [root@loadb1 ~]# ip a | grep inet | grep eth0
        inet 192.168.0.25/24 brd 192.168.3.255 scope global eth0
        inet 192.168.0.100/32 scope global eth0
```

In-case if we required Backup Load balancer for fail-over of main load balancer then we need to do below configuration on fail-over load balancer Server(loadb2-192.168.0.26) as per above setup.

global_defs {
notification_email {
    [email protected]
}
notification_email_from [email protected]
smtp_server 127.0.0.1
smtp_connect_timeout 30
}
vrrp_instance VRRP1 {
    state BACKUP
#   Specify the network interface to which the virtual address is assigned
    interface eth0
#   The virtual router ID must be unique to each VRRP instance that you define
    virtual_router_id 71
#   Set the value of priority higher on the master server than on a backup server
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1234
    }
virtual_ipaddress {
192.168.0.100
}
}

Also remember backup load balancer would assign the virtual IP address on network interface only when Master is failure/unreachable and start functionaling as active load balancer.

HAProxy

HAProxy, or High Availability Proxy is used by RightScale for load balancing in the network with TCP & HTTP layer.

HAProxy is installed with RightScale load balancer ServerTemplates. Load-balancer servers are also known as front-end servers. Generally, their purpose is to direct users to available application servers. A load-balancer server may have only the load balancer application (HAProxy) installed or, in rare cases, it may be an application server in addition to a load balancer, which is not a recommended configuration.

Each load-balancer server has its own IP address but shares the same fully qualified domain name (e.g. host.domain.tld) as the other load-balancer servers in your configuration.

Install haproxy package
```
 yum install haproxy
```
Move default configuration into backup file.
```
 mv /etc/haproxy/haproxy.cfg{,.back}
```

Add mentioned below configuration on each Load Balancer server which includes master and backup load balancer server.

    global
        user haproxy
        group haproxy
    defaults
        mode http
        log global
        retries 2
        timeout connect 3000ms
        timeout server 5000ms
        timeout client 5000ms
    listen stats
        bind 192.168.0.100:9999
        stats enable
        stats hide-version
        stats uri /stats
        stats auth statadmin:statadminpass
    listen mysql-cluster
        bind 192.168.0.100:3306
        mode tcp
        option mysql-check user haproxy_check
        balance roundrobin
        server mysql-1 192.168.0.1:3306 check
        server mysql-2 192.168.0.2:3306 check

First we need to add additional user on our MySQL servers (user must be created without any password), this user will be used by HAProxy for checking a health status of MySQL servers. Also make sure create user once master slave configuration is completed, so that same user would created on each MySQL cluster server.
```
 appserver1# mysql -u root -p
 MariaDB> CREATE USER 'haproxy_check'@'%';
 MariaDB> FLUSH PRIVILEGES;
```
Make Sure restart service for effective changes and enable on start-up as well.
```
     systemctl restart haproxy
     systemctl enable haproxy
```

ganapathichidambaram/MySQL High Availability with Keepalived.md

MySQL High Availability with Keepalived and HAProxy

Keepalived

Configuration of Keepalived

HAProxy