GOAL

We want to put 2 haproxy servers in front of 2 databases along with a virtual ip

Virtual IP Address (VIP): 10.10.1.17
haproxy1 : 10.10.1.15
haproxy2 : 10.10.1.16
db1: 10.10.1.1
db2: 10.10.1.2

PREREQUISITES

IMPORTANT

We have 3 db servers in total but our haproxy applies only for 2 because db3 is in different datacenter that is connected with VPN

That is our custom architecture as one datacenter goes down another can continue. You can have only one single datacenter with 3 db servers in total in same place. You can even apply haproxy to all 3 db servers in you own architecture.

RUN THE BELOW COMMANDS FOR BOTH haproxy1 and haproxy2

update yum

$ sudo yum update

First make sure you have installed the right editor, system and network tools

$ sudo yum install nano wget curl net-tools lsof vim telnet xinetd psmisc socat

install haproxy

$ sudo yum install -y haproxy

let’s lookup haproxy version just for curiosity

$ haproxy -v

HA-Proxy version 1.5.4 2014/09/02
Copyright 2000-2014 Willy Tarreau <w@1wt.eu>

install keepalived

$ sudo yum install keepalived

lookup keepalived version just for curiosity

$ keepalived –version

Keepalived v1.2.13 (03/06,2015)

enable on system boot up (haproxy sometimes doesn’t run on boot up no matter what..)

$ sudo systemctl enable haproxy
$ sudo systemctl enable keepalived

edit hosts for haproxy1 

$ sudo vi /etc/hosts

127.0.0.1 haproxy1
10.10.1.15 haproxy1
10.10.1.16 haproxy2
10.10.1.1 db1
10.10.1.2 db2

edit hosts for haproxy2

127.0.0.1 haproxy2
10.10.1.15 haproxy1
10.10.1.16 haproxy2
10.10.1.1 db1
10.10.1.2 db2

disable selinux

$ sudo vi /etc/sysconfig/selinux

SELINUX=disabled

$ sudo systemctl restart network

open ports for 3306, 80

$ sudo systemctl status firewalld
$ sudo systemctl start firewalld
$ sudo systemctl enable firewalld

add service or port to to exclude from firewall

$ sudo firewall-cmd –permanent –add-service=http
$ sudo firewall-cmd –permanent –add-service=mysql
$ sudo firewall-cmd –permanent –add-port=9200/tcp
$ sudo firewall-cmd –reload
$ sudo systemctl restart firewalld.service
$ sudo iptables -L

SETUP MARIADB REPO AND INSTALL MARIADB CLIENT

select your OS to get the latest MariaDB repo
https://downloads.mariadb.org/mariadb/repositories
you can rename a similar name to MariaDB

$ sudo vi /etc/yum.repos.d/MariaDB.repo

copy paste this (for 64 bit)

# MariaDB 10.0 CentOS repository list - created 2015-07-09 14:56 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

you will need to test mariadb connectivity through mysql client

$ sudo yum install MariaDB-client

MYSQL GALERA DB SHOULD BE UP AND RUNNING

first make sure your galera db cluster is on

Note: Remember we have 3 dbs in total but our haproxy applies only for 2 because db3 is in different datacenter connected with VPN

[msen@db1 ~]$ sudo service mysql start –wsrep-new-cluster
[msen@db2 ~]$ sudo service mysql start
[msen@db3 ~]$ sudo service mysql start

---

 

CONFIGURE KEEPALIVED

backup the original keepalived config file

$ sudo cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak

edit keepalived config file

$ sudo vi /etc/keepalived/keepalived.conf

copy paste this for haproxy1:

! Configuration File for keepalived

global_defs {
router_id haproxy1 # hostname

notification_email {
msen@na.edu
}
notification_email_from haproxy1@na.edu
smtp_server 127.0.0.1
smtp_connect_timeout 30
}

vrrp_script check_haproxy {
script "killall -0 haproxy"
interval 2 # every 2 seconds
weight 2 # add 2 points if OK
}

vrrp_instance VI_1 {
state MASTER # MASTER on haproxy1, BACKUP on haproxy2
interface eno16777984 # interface to monitor
virtual_router_id 51
priority 101 # 101 on haproxy1, 100 on haproxy2
advert_int 1

virtual_ipaddress {
10.10.1.17 dev eno16777984 #virtual ip address
}
track_script {
check_haproxy
}
}

check your network interface name, you’ll see ifcfg-eno16777984 file
eno16777984 is

$ ls -l /etc/sysconfig/network-scripts/

-rw-r--r-- 1 root root 402 Aug 13 09:39 ifcfg-eno16777984

and copy paste this for haproxy2

! Configuration File for keepalived

global_defs {
router_id haproxy2
notification_email {
msen@na.edu
}
notification_email_from haproxy2@na.edu
smtp_server 127.0.0.1
smtp_connect_timeout 30
}

vrrp_script check_haproxy {
script "killall -0 haproxy"
interval 2 # every 2 seconds
weight 2 # add 2 points if OK
}

vrrp_instance VI_1 {
state BACKUP # MASTER on haproxy1, BACKUP on haproxy2
interface eno16777984 # interface to monitor
virtual_router_id 51
priority 100 # 101 on haproxy1 and 100 on haproxy2
advert_int 1

virtual_ipaddress {
10.10.1.17 dev eno16777984 # virtual ip address
}
}

start keepalived on both haproxy1 and haproxy2

$ sudo systemctl start keepalived

check ip, you should see 10.10.1.17 virtual ip address on both haproxy1 and haproxy2 along with their own local ips

$ ip addr show

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eno16777984: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:b1:30:12 brd ff:ff:ff:ff:ff:ff
inet 10.10.1.15/16 brd 10.10.255.255 scope global eno16777984
valid_lft forever preferred_lft forever
inet 10.10.1.17/32 scope global eno16777984
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:feb1:3012/64 scope link
valid_lft forever preferred_lft forever

 

CONFIGURE HAPROXY

For both haproxy1 (10.10.1.15) and haproxy2 (10.10.1.16) servers

backup the haproxy config file

$ sudo cp /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg.bak

edit haproxy config

$ sudo vi /etc/haproxy/haproxy.cfg

haproxy1:

#---------------------------------------------------------------------
# Example configuration for a possible web application. See the
# full configuration options online.
#
# http://haproxy.1wt.eu/download/1.4/doc/configuration.txt
#
#---------------------------------------------------------------------

#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
# to have these messages end up in /var/log/haproxy.log you will
# need to:
#
# 1) configure syslog to accept network log events. This is done
# by adding the '-r' option to the SYSLOGD_OPTIONS in
# /etc/sysconfig/syslog
#
# 2) configure local2 events to go to the /var/log/haproxy.log
# file. A line like the following can be added to
# /etc/sysconfig/syslog
#
# local2.* /var/log/haproxy.log
#
log 127.0.0.1 local2

chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
daemon

# turn on stats unix socket
stats socket /var/lib/haproxy/stats

#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
mode http
log global
option httplog
option dontlognull
option http-server-close
option forwardfor except 127.0.0.0/8
option redispatch
retries 3
timeout http-request 10s
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout http-keep-alive 10s
timeout check 10s
maxconn 5000

#---------------------------------------------------------------------
# HAProxy statistics backend
#---------------------------------------------------------------------
listen haproxy1-monitoring *:80
mode http
stats enable
stats show-legends
stats refresh 5s
stats uri /
stats realm Haproxy\ Statistics
stats auth username:password
stats admin if TRUE

#---------------------------------------------------------------------
# main frontend which proxys to the backends
#---------------------------------------------------------------------
frontend haproxy1 # change on 2nd HAProxy
bind 10.10.1.17:3306
mode tcp
default_backend galera-cluster

#---------------------------------------------------------------------
# round robin balancing between the various backends
#---------------------------------------------------------------------
backend galera-cluster
balance roundrobin
option httpchk
mode tcp

server db1 10.10.1.1:3306 check port 9200 inter 5000 weight 1 maxconn 5000 rise 2 fall 2
server db2 10.10.1.2:3306 check port 9200 inter 5000 weight 1 maxconn 5000 rise 2 fall 2

haproxy2:

#---------------------------------------------------------------------
# Example configuration for a possible web application. See the
# full configuration options online.
#
# http://haproxy.1wt.eu/download/1.4/doc/configuration.txt
#
#---------------------------------------------------------------------

#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
# to have these messages end up in /var/log/haproxy.log you will
# need to:
#
# 1) configure syslog to accept network log events. This is done
# by adding the '-r' option to the SYSLOGD_OPTIONS in
# /etc/sysconfig/syslog
#
# 2) configure local2 events to go to the /var/log/haproxy.log
# file. A line like the following can be added to
# /etc/sysconfig/syslog
#
# local2.* /var/log/haproxy.log
#
log 127.0.0.1 local2

chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
daemon

# turn on stats unix socket
stats socket /var/lib/haproxy/stats

#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
mode http
log global
option httplog
option dontlognull
option http-server-close
option forwardfor except 127.0.0.0/8
option redispatch
retries 3
timeout http-request 10s
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout http-keep-alive 10s
timeout check 10s
maxconn 5000

#---------------------------------------------------------------------
# HAProxy statistics backend
#---------------------------------------------------------------------
listen haproxy2-monitoring *:80
mode http
stats enable
stats show-legends
stats refresh 5s
stats uri /
stats realm Haproxy\ Statistics
stats auth username:password
stats admin if TRUE

#---------------------------------------------------------------------
# main frontend which proxys to the backends
#---------------------------------------------------------------------
frontend haproxy2
bind 10.10.1.17:3306
mode tcp
default_backend galera-cluster

#---------------------------------------------------------------------
# round robin balancing between the various backends
#--------------------------------------------------------------------
backend galera-cluster
balance roundrobin
option httpchk
mode tcp

server db1 10.10.1.1:3306 check port 9200 inter 5000 weight 1 maxconn 5000 rise 2 fall 2
server db2 10.10.1.2:3306 check port 9200 inter 5000 weight 1 maxconn 5000 rise 2 fall 2

start both haproxy1 and haproxy2

$ sudo systemctl start haproxy
$ sudo systemctl status haproxy

if you see cannot bind socket when lookup status of haproxy then run this instead of start

$ sudo /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg
$ sudo netstat -tapnl

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 21714/haproxy
tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 21714/haproxy

If you see both of 80 and 3306 port processes, then you are good to go!
lookup http://10.10.1.17
type: username and password for web username and password as you defined in haproxy config

LET’S TEST HAPROXY

reboot haproxy1, follow up http://10.10.1.17 refresh manually after a while, you’ll see haproxy2

$ sudo shutdown -r

reboot haproxy2, follow up http://10.10.1.17 refresh manually after a while, you’ll see haproxy1

$ sudo shutdown -r

Note: Sometimes reboot doesn’t work. However shutting down ( $  sudo shutdown -P )should always work!

when you restart keepalived in one of them, you assign haproxy to that server

$ sudo systemctl restart keepalived

shutdown haproxy1 completely, follow up http://10.10.1.17 refresh manually after a while, you’ll see haproxy2

$ sudo shutdown -P

when you restart haproxy1, the http://10.10.1.17 will show back to haproxy1

---

 

WE ARE NOT FINISHED YET!

You can see the following error when connecting remotely through VIP

$ sudo mysql -u root -p -h 10.10.1.17

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0 "Internal error/check (Not system error)"

to fix it, continue to configure one more step of clustercheck and mysqlchk

---

 

CONFIGURE CLUSTERCHECK AND MYSQLCHK

on each db1 and db2, no need on db3 because haproxy1 and haproxy2 will point only to db1 and db2, it’s up to your architecture

configure xinetd by adding this to services

$ sudo yum install xinetd

add this to clustercheck
copy paste this content https://github.com/olafz/percona-clustercheck/blob/master/clustercheck

$ sudo vi /usr/bin/clustercheck

#!/bin/bash
#
# Script to make a proxy (ie HAProxy) capable of monitoring Percona XtraDB Cluster nodes properly
#
# Author: Olaf van Zandwijk <olaf.vanzandwijk@nedap.com> # Author: Raghavendra Prabhu <raghavendra.prabhu@percona.com>
#
# Documentation and download: https://github.com/olafz/percona-clustercheck
#
# Based on the original script from Unai Rodriguez
#

if [[ $1 == '-h' || $1 == '--help' ]];then
echo "Usage: $0 <available_when_donor=0|1> <available_when_readonly=0|1> "
exit
fi

# if the disabled file is present, return 503. This allows
# admins to manually remove a node from a cluster easily.
if [ -e "/var/tmp/clustercheck.disabled" ]; then
# Shell return-code is 1
echo -en "HTTP/1.1 503 Service Unavailable\r\n"
echo -en "Content-Type: text/plain\r\n"
echo -en "Connection: close\r\n"
echo -en "Content-Length: 51\r\n"
echo -en "\r\n"
echo -en "Percona XtraDB Cluster Node is manually disabled.\r\n"
sleep 0.1
exit 1
fi

MYSQL_USERNAME="${1-root}"
MYSQL_PASSWORD="${2-Memo3480$}"
AVAILABLE_WHEN_DONOR=${3:-0}
ERR_FILE="${4:-/dev/null}"
AVAILABLE_WHEN_READONLY=${5:-1}
DEFAULTS_EXTRA_FILE=${6:-/etc/my.cnf}

#Timeout exists for instances where mysqld may be hung
TIMEOUT=10

EXTRA_ARGS=""
if [[ -n "$MYSQL_USERNAME" ]]; then
EXTRA_ARGS="$EXTRA_ARGS --user=${MYSQL_USERNAME}"
fi
if [[ -n "$MYSQL_PASSWORD" ]]; then
EXTRA_ARGS="$EXTRA_ARGS --password=${MYSQL_PASSWORD}"
fi
if [[ -r $DEFAULTS_EXTRA_FILE ]];then
MYSQL_CMDLINE="mysql --defaults-extra-file=$DEFAULTS_EXTRA_FILE -nNE --connect-timeout=$TIMEOUT \
${EXTRA_ARGS}"
else
MYSQL_CMDLINE="mysql -nNE --connect-timeout=$TIMEOUT ${EXTRA_ARGS}"
fi
#
# Perform the query to check the wsrep_local_state
#
WSREP_STATUS=$($MYSQL_CMDLINE -e "SHOW STATUS LIKE 'wsrep_local_state';" \
2>${ERR_FILE} | tail -1 2>>${ERR_FILE})

if [[ "${WSREP_STATUS}" == "4" ]] || [[ "${WSREP_STATUS}" == "2" && ${AVAILABLE_WHEN_DONOR} == 1 ]]
then
# Check only when set to 0 to avoid latency in response.
if [[ $AVAILABLE_WHEN_READONLY -eq 0 ]];then
READ_ONLY=$($MYSQL_CMDLINE -e "SHOW GLOBAL VARIABLES LIKE 'read_only';" \
2>${ERR_FILE} | tail -1 2>>${ERR_FILE})

if [[ "${READ_ONLY}" == "ON" ]];then
# Percona XtraDB Cluster node local state is 'Synced', but it is in
# read-only mode. The variable AVAILABLE_WHEN_READONLY is set to 0.
# => return HTTP 503
# Shell return-code is 1
echo -en "HTTP/1.1 503 Service Unavailable\r\n"
echo -en "Content-Type: text/plain\r\n"
echo -en "Connection: close\r\n"
echo -en "Content-Length: 43\r\n"
echo -en "\r\n"
echo -en "Percona XtraDB Cluster Node is read-only.\r\n"
sleep 0.1
exit 1
fi
fi
# Percona XtraDB Cluster node local state is 'Synced' => return HTTP 200
# Shell return-code is 0
echo -en "HTTP/1.1 200 OK\r\n"
echo -en "Content-Type: text/plain\r\n"
echo -en "Connection: close\r\n"
echo -en "Content-Length: 40\r\n"
echo -en "\r\n"
echo -en "Percona XtraDB Cluster Node is synced.\r\n"
sleep 0.1
exit 0
else
# Percona XtraDB Cluster node local state is not 'Synced' => return HTTP 503
# Shell return-code is 1
echo -en "HTTP/1.1 503 Service Unavailable\r\n"
echo -en "Content-Type: text/plain\r\n"
echo -en "Connection: close\r\n"
echo -en "Content-Length: 44\r\n"
echo -en "\r\n"
echo -en "Percona XtraDB Cluster Node is not synced.\r\n"
sleep 0.1
exit 1
fi

give permission to clustercheck

$ sudo chmod 755 /usr/bin/clustercheck
$ sudo chown nobody /usr/bin/clustercheck

find port 9200 with wap-wsp, disable it and add mysqlchk

$ sudo vim /etc/services

#disabled by Mehmet Sen
#wap-wsp 9200/tcp # WAP connectionless session service
#wap-wsp 9200/udp # WAP connectionless session service
#added by Mehmet Sen
mysqlchk 9200/tcp # mysqlchk

add the service mysqlchk to /etc/xinetd.d/mysqlchk

$ sudo vim /etc/xinetd.d/mysqlchk

# default: on
# description: mysqlchk
service mysqlchk
{
 flags = REUSE
 socket_type = stream
 port = 9200
 wait = no
 user = nobody
 server = /usr/bin/clustercheck
 log_on_failure += USERID
 disable = no
 only_from = 0.0.0.0/0
 per_source = UNLIMITED
}

Restart xinetd (you can watch for issues on /var/log/messages):

$ sudo systemctl stop xinetd
$ sudo systemctl start xinetd

TEST

Let’s Test Clustercheck and Mysqlchk

locally

$ sudo /usr/bin/clustercheck

HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40

Percona XtraDB Cluster Node is synced.

remotely, try to connect to db1 for example from haproxy1

$ telnet 10.10.1.1 9200

Trying 10.10.1.1...
Connected to 10.10.1.1.
Escape character is '^]'.
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40

remotely, try to connect to db2 for example from haproxy1

$ telnet 10.10.1.2 9200

Trying 10.10.1.2...
Connected to 10.10.1.2.
Escape character is '^]'.
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40

let’s connect now to the MariaDB galera cluster database through VIP (10.10.1.17)
you can try to connect from haproxy2 as well

$ sudo mysql -u root -p -h 10.10.1.17
MariaDB [(none)]> show databases;

 

WE CAN CONNECT TO MYSQL VIA 10.10.1.17
YEEEEYYYY!!!

WE CAN WATCH http://10.10.1.17 PRECISELY
YEEEEYYYY!!!