Make ProxySQL for High Availability

In this blog we will see how to set-up a ProxySQL for HA

ProxySQL doesn’t natively support any high availability solution it is a single

point of failure. (It means if the proxy server goes down your application is

down)

There are many ways to fix the SPOF issue.

Here we will be using a Keepalived on the proxy servers (we need more than

on proxysql server).

From the Keepalived doc,

• Keepalived is a routing software written in C. 
• The main goal of this project is to provide simple and robust facilities for loadbalancing and high-availability to Linux system and Linux based infrastructures. 
• Load Balancing framework relies on well-known and widely used Linux Virtual Server (IPVS) kernel module providing Layer4 loadbalancing. 
• Keepalived implements a set of checkers to dynamically and adaptively maintain and manage load balanced server pool according their health. 
• On the other hand high-availability is achieved by VRRP protocol. VRRP is a fundamental brick for router failover. 
• In addition, Keepalived implements a set of hooks to the VRRP finite state machine providing low-level and high-speed protocol interactions. 
• In order to offer fastest network failure detection, Keepalived implements BFD protocol. 
• VRRP state transition can take into account BFD hint to drive fast state transition. 
• Keepalived frameworks can be used independently or all together to provide resilient infrastructures.

Our setup will be as below 

Single proxy on 3 node galera can be found on my previous blog here.

Here Keepalived will make sure one proxysql will be routing your queries to the

PXC , if active node goes down, the  passive node will be promoted as active

and application will be connecting to your database via that proxy. It is achieved

by assigning the VIP on that particular proxy node.

Below is the process to setup a Keepalived

We will make Proxy Active as Master and Proxy Passive as Backup ie, if proxy is

down on active the Proxy on Passive which is backup act as Master.

Here  rather than percona proxy i am using native proxy 2.0.5 which supports

Native Galera Support Link . The setup can be found in the blog.

Install Keepalived on both proxy servers

Install Keepalived 

yum install keepalived

Setup the Keepalived configuration file 

vi /etc/keepalived/keepalived.conf

Config file for master node

global_defs {   # Keepalived process identifier   script_user root } # Script used to check if Proxy is running vrrp_script check_proxy {  script "/bin/systemctl status proxysql.service"  interval 2  fall 2  rise 2 } # Virtual interface # The priority specifies the order in which the assigned interface to take over in a failover vrrp_instance VI_01 {   state MASTER   interface eth0   virtual_router_id 51   priority 101   # The virtual ip address shared between the two loadbalancers   virtual_ipaddress {   192.168.1.50   }   track_script {     check_proxy   } }

Config file for backup node

global_defs {   # Keepalived process identifier   script_user root } # Script used to check if Proxy is running vrrp_script check_proxy {  script "/bin/systemctl status proxysql.service"  interval 2  fall 2  rise 2 } # Virtual interface # The priority specifies the order in which the assigned interface to take over in a failover vrrp_instance VI_01 {   state BACKUP   interface eth0   virtual_router_id 51   priority 100   # The virtual ip address shared between the two loadbalancers   virtual_ipaddress {   192.168.1.50   }   track_script {     check_proxy   } }

We need to start the Keepalived on active node at first and the

keepalived will set the state to Master and you can see the connections towards the PXC nodes.

systemctl status keepalived -l ● keepalived.service - LVS and VRRP High Availability Monitor    Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)    Active: active (running) since Sun 2019-08-25 06:06:14 UTC; 4s ago   Process: 32626 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)  Main PID: 32627 (keepalived)    CGroup: /system.slice/keepalived.service            ├─32627 /usr/sbin/keepalived -D            ├─32628 /usr/sbin/keepalived -D            └─32629 /usr/sbin/keepalived -D Aug 25 06:06:14 proxysql01.localhost.com Keepalived_vrrp[32629]: VRRP_Script(check_proxy) succeeded Aug 25 06:06:15 proxysql01.localhost.com Keepalived_vrrp[32629]: VRRP_Instance(VI_01) Transition to MASTER STATE Aug 25 06:06:16 proxysql01.localhost.com Keepalived_vrrp[32629]: VRRP_Instance(VI_01) Entering MASTER STATE Aug 25 06:06:16 proxysql01.localhost.com Keepalived_vrrp[32629]: VRRP_Instance(VI_01) setting protocol VIPs. Aug 25 06:06:16 proxysql01.localhost.com Keepalived_vrrp[32629]: Sending gratuitous ARP on eth0 for 192.168.1.50 Aug 25 06:06:16 proxysql01.localhost.com Keepalived_vrrp[32629]: VRRP_Instance(VI_01) Sending/queueing gratuitous ARPs on eth0 for 192.168.1.50 Aug 25 06:06:16 proxysql01.localhost.com Keepalived_vrrp[32629]: Sending gratuitous ARP on eth0 for 192.168.1.50 Aug 25 06:06:16 proxysql01.localhost.com Keepalived_vrrp[32629]: Sending gratuitous ARP on eth0 for 192.168.1.50 Aug 25 06:06:16 proxysql01.localhost.com Keepalived_vrrp[32629]: Sending gratuitous ARP on eth0 for 192.168.1.50 Aug 25 06:06:16 proxysql01.localhost.com Keepalived_vrrp[32629]: Sending gratuitous ARP on eth0 for 192.168.1.50

systemctl status keepalived -l ● keepalived.service - LVS and VRRP High Availability Monitor    Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)    Active: active (running) since Sun 2019-08-25 06:16:37 UTC; 3s ago   Process: 23667 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)  Main PID: 23668 (keepalived)    CGroup: /system.slice/keepalived.service            ├─23668 /usr/sbin/keepalived -D            ├─23669 /usr/sbin/keepalived -D            └─23670 /usr/sbin/keepalived -D Aug 25 06:13:17 proxysql02.localhost.com Keepalived_vrrp[23670]: Registering Kernel netlink reflector Aug 25 06:13:17 proxysql02.localhost.com Keepalived_vrrp[23670]: Registering Kernel netlink command channel Aug 25 06:13:17 proxysql02.localhost.com Keepalived_vrrp[23670]: Registering gratuitous ARP shared channel Aug 25 06:13:17 proxysql02.localhost.com Keepalived_vrrp[23670]: Opening file '/etc/keepalived/keepalived.conf'. Aug 25 06:13:17 proxysql02.localhost.com Keepalived_vrrp[23670]: VRRP_Instance(VI_01) removing protocol VIPs. Aug 25 06:13:17 proxysql02.localhost.com Keepalived_vrrp[23670]: Using LinkWatch kernel netlink reflector... Aug 25 06:13:17 proxysql02.localhost.com Keepalived_vrrp[23670]: VRRP_Instance(VI_01) Entering BACKUP STATE Aug 25 06:13:17 proxysql02.localhost.com Keepalived_vrrp[23670]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)] Aug 25 06:13:17 proxysql02.localhost.com Keepalived_vrrp[23670]: VRRP_Script(check_proxy) succeeded

On proxy active node you can see the VIP assigned 

 ip addr show eth0

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000     link/ether 08:00:27:78:77:b6 brd ff:ff:ff:ff:ff:ff     inet 192.168.1.12/24 brd 192.168.1.255 scope global noprefixroute dynamic eth0        valid_lft 259107sec preferred_lft 259107sec     inet 192.168.1.50/24 brd 192.168.1.255 scope global secondary eth0:0        valid_lft forever preferred_lft forever     inet6 fe80::1476:bc31:ebd5:a581/64 scope link noprefixroute         valid_lft forever preferred_lft forever

Testing the failover

To test the same, i will stop the proxy service on active node

systemctl stop proxysql.service

systemctl status proxysql.service ● proxysql.service - LSB: High Performance Advanced Proxy for MySQL    Loaded: loaded (/etc/rc.d/init.d/proxysql; bad; vendor preset: disabled)    Active: inactive (dead) since Sun 2019-08-25 06:50:26 UTC; 13s ago      Docs: man:systemd-sysv-generator(8)   Process: 7925 ExecStop=/etc/rc.d/init.d/proxysql stop (code=exited, status=0/SUCCESS) Aug 23 07:42:46 proxysql01.localhost.com systemd[1]: Starting LSB: High Performance Advanced Proxy for MySQL... Aug 23 07:42:46 proxysql01.localhost.com su[11712]: (to proxysql) root on none Aug 23 07:42:46 proxysql01.localhost.com proxysql[11710]: Starting ProxySQL: 2019-08-23 07:42:46 [INFO] Using config file /etc/proxysql.cnf Aug 23 07:42:46 proxysql01.localhost.com proxysql[11710]: 2019-08-23 07:42:46 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them. Aug 23 07:42:46 proxysql01.localhost.com proxysql[11710]: DONE! Aug 23 07:42:46 proxysql01.localhost.com systemd[1]: Started LSB: High Performance Advanced Proxy for MySQL. Aug 25 06:50:26 proxysql01.localhost.com systemd[1]: Stopping LSB: High Performance Advanced Proxy for MySQL... Aug 25 06:50:26 proxysql01.localhost.com proxysql[7925]: Shutting down ProxySQL: DONE! Aug 25 06:50:26 proxysql01.localhost.com systemd[1]: Stopped LSB: High Performance Advanced Proxy for MySQL.

On checking the status of keepalived on passive node

systemctl status keepalived -l ● keepalived.service - LVS and VRRP High Availability Monitor    Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)    Active: active (running) since Sun 2019-08-25 06:13:17 UTC; 37min ago   Process: 23667 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)  Main PID: 23668 (keepalived)    CGroup: /system.slice/keepalived.service            ├─23668 /usr/sbin/keepalived -D            ├─23669 /usr/sbin/keepalived -D            └─23670 /usr/sbin/keepalived -D Aug 25 06:13:17 proxysql02.localhost.com Keepalived_vrrp[23670]: Registering Kernel netlink command channel Aug 25 06:13:17 proxysql02.localhost.com Keepalived_vrrp[23670]: Registering gratuitous ARP shared channel Aug 25 06:13:17 proxysql02.localhost.com Keepalived_vrrp[23670]: Opening file '/etc/keepalived/keepalived.conf'. Aug 25 06:13:17 proxysql02.localhost.com Keepalived_vrrp[23670]: SECURITY VIOLATION - scripts are being executed but script_security not enabled. Aug 25 06:13:17 proxysql02.localhost.com Keepalived_vrrp[23670]: VRRP_Instance(VI_01) removing protocol VIPs. Aug 25 06:13:17 proxysql02.localhost.com Keepalived_vrrp[23670]: Using LinkWatch kernel netlink reflector... Aug 25 06:13:17 proxysql02.localhost.com Keepalived_vrrp[23670]: VRRP_Instance(VI_01) Entering BACKUP STATE Aug 25 06:13:17 proxysql02.localhost.com Keepalived_vrrp[23670]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)] Aug 25 06:13:17 proxysql02.localhost.com Keepalived_vrrp[23670]: VRRP_Script(check_proxy) succeeded Aug 25 06:50:31 proxysql02.localhost.com Keepalived_vrrp[23670]: VRRP_Instance(VI_01) Transition to MASTER STATE Aug 25 06:50:32 proxysql02.localhost.com Keepalived_vrrp[23670]: VRRP_Instance(VI_01) Entering MASTER STATE Aug 25 06:50:32 proxysql02.localhost.com Keepalived_vrrp[23670]: VRRP_Instance(VI_01) setting protocol VIPs. Aug 25 06:50:32 proxysql02.localhost.com Keepalived_vrrp[23670]: Sending gratuitous ARP on eth0 for 192.168.1.50 Aug 25 06:50:32 proxysql02.localhost.com Keepalived_vrrp[23670]: VRRP_Instance(VI_01) Sending/queueing gratuitous ARPs on eth0 for 192.168.1.50 Aug 25 06:50:32 proxysql02.localhost.com Keepalived_vrrp[23670]: Sending gratuitous ARP on eth0 for 192.168.1.50 Aug 25 06:50:32 proxysql02.localhost.com Keepalived_vrrp[23670]: Sending gratuitous ARP on eth0 for 192.168.1.50 Aug 25 06:50:32 proxysql02.localhost.com Keepalived_vrrp[23670]: Sending gratuitous ARP on eth0 for 192.168.1.50 Aug 25 06:50:32 proxysql02.localhost.com Keepalived_vrrp[23670]: Sending gratuitous ARP on eth0 for 192.168.1.50 Aug 25 06:50:37 proxysql02.localhost.com Keepalived_vrrp[23670]: Sending gratuitous ARP on eth0 for 192.168.1.50 Aug 25 06:50:37 proxysql02.localhost.com Keepalived_vrrp[23670]: VRRP_Instance(VI_01) Sending/queueing gratuitous ARPs on eth0 for 192.168.1.50 Aug 25 06:50:37 proxysql02.localhost.com Keepalived_vrrp[23670]: Sending gratuitous ARP on eth0 for 192.168.1.50 Aug 25 06:50:37 proxysql02.localhost.com Keepalived_vrrp[23670]: Sending gratuitous ARP on eth0 for 192.168.1.50 Aug 25 06:50:37 proxysql02.localhost.com Keepalived_vrrp[23670]: Sending gratuitous ARP on eth0 for 192.168.1.50 Aug 25 06:50:37 proxysql02.localhost.com Keepalived_vrrp[23670]: Sending gratuitous ARP on eth0 for 192.168.1.50

You can see the floating IP been assigned to the passive node and it transfer its state from backup to master.