Installing and Configuring Percona XtraDB Cluster High Availability On CentOS 7 / RHEL 7 – Part3

Choosing a high-availability solution for MySQL to prevent downtime and data losing, is really one of the important subjects for an IT System Admin to handle. Today, we are going to present you Percona XtraDB Cluster one of the MYSQL high-availability solutions on the market.

In the third part of the tutorial “Installing and Configuring Percona XtraDB Cluster High Availability“, we are going to add a HAProxy backup node with KeepAlived to prevent any downtime access to the Percona XtraDB Cluster nodes on CentOS 7 / RHEL 7.

READ Part 1 – How to install and configure Percona XtraDB Cluster on Centos 7 / RHEL 7

READ Part 2 – How to install and configure HAProxy Load Balancer for Percona XtraDB Cluster on Centos 7 / RHEL 7

Environment

This Lab will be assembled of three CentOS 7 servers/nodes and 2 HAProxy nodes Load Balancer.

• Node 1
  • Host name: pxc01.yallalabs.com
  • IP address: 192.168.1.10
• Node 2
  • Host name: pxc02.yallalabs.com
  • IP address: 192.168.1.11
• Node 3
  • Host name: pxc03.yallalabs.com
  • IP address: 192.168.1.12
• HAProxy 1
  • Host name: lb01.yallalabs.com
  • IP address: 192.168.1.15
• HAProxy 2
  • Host name: lb02.yallalabs.com
  • IP address: 192.168.1.16
• Float (Virtual) IP Shared Between the HAProxy 1 And HAProxy 2
  • IP address: 192.168.1.100

Step 1: HAProxy Installation and Configuration

1- Install HAProxy (Backup Node)

[root@lb02 ~]# yum install haproxy -y

2- HAProxy File Configuration:

[root@lb02 ~] # mv /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg.org
[root@lb02 ~] # vi /etc/haproxy/haproxy.cfg

– Your file configuration should be like this.

#
global
        log 127.0.0.1   local0
        log 127.0.0.1   local1 notice
        maxconn 4096
        uid 99
        gid 99
        #daemon
        debug
        #quiet

defaults
        log     global
        mode    http
        option  tcplog
        option  dontlognull
        retries 3
        redispatch
        maxconn 2000
        contimeout      5000
        clitimeout      50000
        srvtimeout      50000

listen mysql-cluster 0.0.0.0:3306
    mode tcp
    balance roundrobin
    option  httpchk

    server pxc01 192.168.1.10:3306 check port 9200 inter 12000 rise 3 fall 3
    server pxc02 192.168.1.11:3306 check port 9200 inter 12000 rise 3 fall 3
    server pxc03 192.168.1.12:3306 check port 9200 inter 12000 rise 3 fall 3

listen stats 0.0.0.0:9000
## HAProxy stats web gui running on port 9000 - username  lotfi and password: secret
        mode http
        stats enable
        stats uri /stats
        stats realm HAProxy\ Statistics
        stats auth lotfi:secret
        stats admin if TRUE

3- Firewall Adjusting

[root@lb02 ~]# firewall-cmd --permanent --add-port=9000/tcp
[root@lb02 ~]# firewall-cmd --permanent --add-port=3030/tcp
[root@lb02 ~]# firewall-cmd --reload

4- Start/Enable HAProxy

[root@lb02 ~]# systemctl enable haproxy
[root@lb02 ~]# systemctl start haproxy

Step 2: Installing and Configuration KeepAlived

1- Install KeepAlived (Load Balancing nodes)

[root@lb02 ~]# yum install keepalived -y

2- Getting the device name

[root@lb02 ~]# nmcli con show
NAME         UUID                                  TYPE            DEVICE
eno16777736  fa2f5a44-e4ea-4cd1-98bf-bc69bacfb1ee  802-3-ethernet  ens33

Like you see in above my network device name is ens33

3 – KeepAlived Configuration HAProxy Master Node
– On both the load balancer nodes, we have to backup the keepAlived configuration file

[root@lb01 ~]# mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.org

– On the master load balancer lb01.yallalab.com open the /etc/keepalived/keepalived.conf and add the following configuration

[root@lb01 ~]# vi /etc/keepalived/keepalived.conf

vrrp_script chk_haproxy {
  script "killall -0 haproxy" # check the haproxy process
  interval 2 # every 2 seconds
  weight 2 # add 2 points if OK
}

vrrp_instance VI_1 {
  interface ens33 # interface to monitor
  state MASTER # MASTER on hAProxy1, BACKUP on hAProxy2
  virtual_router_id 51
  priority 101 # 101 on hAProxy1, 100 on hAProxy2
  virtual_ipaddress {
    192.168.1.100 # virtual ip address
  }
  track_script {
    chk_haproxy
  }
}

3 – KeepAlived Configuration HAProxy Bakup Node

– On the backup load balancer lb02.yallalab.com open the /etc/keepalived/keepalived.conf and add the following configuration

[root@lb02 ~]# mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.org
[root@lb02 ~]# vi /etc/keepalived/keepalived.conf

vrrp_script chk_haproxy {
  script "killall -0 haproxy" # check the haproxy process
  interval 2 # every 2 seconds
  weight 2 # add 2 points if OK
}

vrrp_instance VI_1 {
  interface ens33 # interface to monitor
  state BACKUP # MASTER on hAProxy1, BACKUP on hAProxy2
  virtual_router_id 51
  priority 100 # 101 on hAProxy1, 100 on hAProxy2
  virtual_ipaddress {
    192.168.1.100 # virtual ip address
  }
  track_script {
    chk_haproxy
  }
}

-Note that the master and backup HAProxy have a different priority

4- Enable/ Start KeepAlived
– On the both Haproxy Load Balancing nodes Enable and start the KeepAlived service using the following commands:

[root@lb01 ~]# systemctl enable keepalived
[root@lb01 ~]# systemctl start keepalived

5- Check KeepAlived
– To verify if KeepAlived is running, you can use the following command to check if floating IP is assigned to ens33 device

[root@lb01 ~]# ip addr show ens33
2: ens33:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:d5:01:2a brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.15/24 brd 192.168.1.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.1.100/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fed5:12a/64 scope link
       valid_lft forever preferred_lft forever

– To verify KeepAlived is running you can check the log file or ping the Float IP using the following commands:

[root@lb01 ~]# ping 192.168.1.100 -c3
PING 192.168.1.100 (192.168.1.100) 56(84) bytes of data.
64 bytes from 192.168.1.100: icmp_seq=1 ttl=64 time=0.173 ms
64 bytes from 192.168.1.100: icmp_seq=2 ttl=64 time=0.115 ms
64 bytes from 192.168.1.100: icmp_seq=3 ttl=64 time=0.096 ms

--- 192.168.1.100 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2004ms
rtt min/avg/max/mdev = 0.096/0.128/0.173/0.032 ms

[root@lb01 ~]# tail -f /var/log/messages 

Step 3: Checking and Testing

– Open the browser with the Float (Virtual) IP address shared by the 2 HAProxy nodes on port 9000:

– All nodes are online, let’s now try to query the cluster from a client machine using the Float IP address

[root@lb01 ~]# mysql -u user -p -h 192.168.1.100 -P 3306 -e "SELECT * FROM clustertest.employees"
Enter password:
+--------+------------------+
| number | name             |
+--------+------------------+
|      1 | Lotfi Waderni    |
|      2 | Gigi Andrea      |
|      3 | Filipo Gigi      |
+--------+------------------+

– Finally, you can test the high availability of your Percona XtraDB Cluster by switching off one of the HAProxy load balancer.

 
We hope this tutorial was enough Helpful. If you need more information, or have any questions, just comment below and we will be glad to assist you!