INSTALL HAPROXY AND KEEPALIVED ON CENTOS 7 FOR MARIADB CLUSTER

UPDATED!!!

GOAL

Let’s put 2 haproxy servers in front of 3 databases along with a virtual ip

Virtual IP Address (VIP): 10.10.4.17
haproxy1 : 10.10.4.15
haproxy2 : 10.10.4.16
db1: 10.10.4.1
db2: 10.10.4.2
db3: 10.10.4.3
smtp mail relay server: 10.10.4.20

SMTP MAIL RELAY SERVER

To install your separate smtp relay server at local level. Remember that we use Socketlabs for SMTP

CONFIGURE POSTFIX RELAY FOR SOCKETLABS ON CENTOS 7
https://snapdev.net/2017/10/27/configure-postfix-relay-for-socketlabs-on-centos-7/

NTP

Apply NTP Rules For both haproxy1 and haproxy2

CONFIGURE NTP ON CENTOS 7
https://snapdev.net/2015/09/09/configure-ntp-on-centos-7/

PREREQUISITES

SELINUX
$ sudo vi /etc/sysconfig/selinux

SELINUX=disabled

$ sudo systemctl restart network

check out
$ getenforce

Disabled

FIREWALL ON HAPROXY
$ sudo systemctl status firewalld
$ sudo systemctl stop firewalld
$ sudo systemctl start firewalld
$ sudo systemctl enable firewalld

open ports for 3306, 80 and 9200 on each haproxy1 and haproxy2
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 -S

FIREWALL ON DATABASES
open ports 9200 on each database node; db1, db2, db3
$ sudo firewall-cmd --permanent --add-port=9200/tcp
$ sudo firewall-cmd --reload

HAPROXY1 and HAPROXY2

point same hosts on haproxy1, haproxy2, db1, db2 and db3
$ sudo vi /etc/hosts

10.10.4.15 haproxy1
10.10.4.16 haproxy2
10.10.4.1 db1
10.10.4.2 db2
10.10.4.3 db3
10.10.4.20 mail.na.edu

update yum
$ sudo yum update

First make sure you have installed the right editor, system and network tools on haproxy1 and haproxy2
$ sudo yum install nano wget curl net-tools lsof vim telnet xinetd psmisc socat

Note: For setting up your own mail relay server, please follow the following tutorial

https://snapdev.net/2017/10/27/configure-postfix-relay-for-socketlabs-on-centos-7/

INSTALL APACHE ON HAPROXY1 AND HAPROXY2

$ sudo yum install httpd
$ sudo systemctl start httpd.service
$ sudo systemctl stop httpd.service
$ sudo systemctl status httpd.service
$ sudo systemctl restart httpd.service
$ httpd -v

Server version: Apache/2.4.6 (CentOS)
Server built: Oct 19 2017 20:39:16

should work

http://10.10.4.15/
http://10.10.4.16/

Note: We’ll disable apache later because we don’t want port 80 is being used. We’ll use apache only to validate keepalived later

INSTALL KEEPALIVED

First we’ll install keepalived
$ sudo yum install keepalived

lookup keepalived version
$ keepalived -v

Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2

Copyright(C) 2001-2017 Alexandre Cassen, 

Build options:  PIPE2 LIBNL3 RTA_ENCAP RTA_EXPIRES FRA_OIFNAME FRA_TUN_ID RTAX_CC_ALGO RTAX_QUICKACK LIBIPTC LIBIPSET_DYNAMIC LVS LIBIPVS_NETLINK VRRP VRRP_AUTH VRRP_VMAC SOCK_NONBLOCK SOCK_CLOEXEC FIB_ROUTING INET6_ADDR_GEN_MODE SNMP_V3_FOR_V2 SNMP SNMP_KEEPALIVED SNMP_CHECKER SNMP_RFC SNMP_RFCV2 SNMP_RFCV3 SO_MARK

enable on system boot up
$ sudo systemctl enable keepalived

Created symlink from /etc/systemd/system/multi-user.target.wants/keepalived.service to /usr/lib/systemd/system/keepalived.service.

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.2 CentOS repository list - created 2017-10-19 20:17 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

update yum
$ sudo yum update

you will need to test mariadb connectivity through mysql client
$ sudo yum install MariaDB-client
make sure your galera db cluster is on for all nodes db1, db2 and db3

MYSQL GALERA DB SHOULD BE UP AND RUNNING!!!

first make sure your galera db cluster is on

[msen@db1 ~]$ sudo galera_new_cluster
[msen@db2 ~]$ sudo systemctl start mariadb
[msen@db3 ~]$ sudo systemctl start mariadb

 

CONFIGURE KEEPALIVED

learn first your network interface name
$ sudo vi /etc/sysconfig/network-scripts/ifcfg-eno16777984

DEVICE=”eno16777984″

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

configure keepalived for haproxy1
! Configuration File for keepalived

global_defs {
   notification_email {
        msen@na.edu
        fcan@na.edu
        khudoyor@na.edu
   }
   notification_email_from haproxy1@na.edu
   smtp_server mail.na.edu  # NAU Mail Relay Server
   smtp_connect_timeout 300
   router_id LVS_DEVEL
   vrrp_skip_check_adv_addr
   vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

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
    smtp_alert # Activate SMTP notifications
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        check_haproxy
    }
    track_interface {
        eno16777984
    }
    virtual_ipaddress {
        10.10.4.17 dev eno16777984 #virtual ip address
    }
}
configure keepalived for haproxy2
! Configuration File for keepalived

global_defs {
   notification_email {
        msen@na.edu
        fcan@na.edu
        khudoyor@na.edu
   }
   notification_email_from haproxy2@na.edu
   smtp_server mail.na.edu  # NAU Mail Relay Server
   smtp_connect_timeout 300
   router_id LVS_DEVEL
   vrrp_skip_check_adv_addr
   vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

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 for haproxy1, 100 for haproxy2
    advert_int 1
    smtp_alert # Activate SMTP notifications
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        check_haproxy
    }
    track_interface {
        eno16777984
    }
    virtual_ipaddress {
        10.10.4.17 dev eno16777984 #virtual ip address
    }
}

keepalived commands
$ sudo systemctl start keepalived
$ sudo systemctl stop keepalived
$ sudo systemctl status keepalived
$ sudo systemctl enable keepalived

start keepalived on both haproxy1 and haproxy2
$ sudo systemctl start keepalived

remember that haproxy web servers (apache) were working

http://10.10.4.15/
http://10.10.4.16/

now the virtual ip should work the same way. It shows the default apache page (Testing 123..)

http://10.10.4.17/

check ip, you should see 10.10.4.17 virtual ip address on both haproxy1 and haproxy2
$ ip addr show

1: lo:  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:  mtu 1500 qdisc mq state UP qlen 1000
    link/ether 00:0c:29:17:15:42 brd ff:ff:ff:ff:ff:ff
    inet 10.10.4.15/21 brd 10.10.7.255 scope global eno16777984
       valid_lft forever preferred_lft forever
    inet 10.10.4.17/32 scope global eno16777984
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe17:1542/64 scope link 
       valid_lft forever preferred_lft forever

INSTALL HAPROXY

After being sure keepalived is working, we’ll now install haproxy
$ sudo yum install haproxy

let’s lookup haproxy version
$ haproxy -v

HA-Proxy version 1.5.18 2016/05/10
Copyright 2000-2016 Willy Tarreau

enable on system boot up
$ sudo systemctl enable haproxy

Created symlink from /etc/systemd/system/multi-user.target.wants/haproxy.service to /usr/lib/systemd/system/haproxy.service.

 

CONFIGURE HAPROXY

For both haproxy1 (10.10.4.15) and haproxy2 (10.10.4.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     10000
    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.4.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.4.1:3306 check port 9200 inter 5000 weight 1 maxconn 5000 rise 2 fall 2
  server  db2             10.10.4.2:3306 check port 9200 inter 5000 weight 1 maxconn 5000 rise 2 fall 2
  server  db3             10.10.4.3: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     10000
    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   # change on 2nd HAProxy
  bind    10.10.4.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.4.1:3306 check port 9200 inter 5000 weight 1 maxconn 5000 rise 2 fall 2
  server  db2             10.10.4.2:3306 check port 9200 inter 5000 weight 1 maxconn 5000 rise 2 fall 2
  server  db3             10.10.4.3:3306 check port 9200 inter 5000 weight 1 maxconn 5000 rise 2 fall 2

we need to first stop the apache because it’s using the port 80, we need that port for haproxy 1 and 2
$ sudo systemctl stop httpd.service
if you have enabled before, make sure to disable
$ sudo systemctl disable httpd.service

Removed symlink /etc/systemd/system/multi-user.target.wants/httpd.service.

$ sudo systemctl restart keepalived

start both haproxy1 and haproxy2
$ sudo systemctl start haproxy
$ sudo systemctl status haproxy

other commands if needed
$ sudo systemctl restart haproxy
$ sudo systemctl stop haproxy
$ sudo systemctl enable haproxy

if you see “cannot bind socket” error 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 10.10.4.17:3306         	0.0.0.0:*               LISTEN      32734/haproxy       
tcp        0      0 0.0.0.0:80              	0.0.0.0:*               LISTEN      32734/haproxy

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

However, if you see that the db1, db2 and db3 as red, it’s because we can’t access to port 9200 on database servers.
If they are green, you are ok which means you already enabled the port 9200 for each database node

There is one more work to do. We can’t connect remotely through VIP. Let’s fix it!

SO WE ARE NOT FINISHED YET!

You can see the following error when connecting remotely through VIP
$ sudo mysql -u root -p -h 10.10.4.17

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

or

ERROR 2013 (HY000): Lost connection to MySQL server at 'handshake: reading inital communication packet', system error: 57

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

CONFIGURE CLUSTERCHECK AND MYSQLCHK ON DB NODES

on each database node; db1, db2, db3
configure xinetd by adding this to services, replace the mariadb username and password with yours
$ 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 
# Author: Raghavendra Prabhu 
#
# 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      "
    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-username}"
MYSQL_PASSWORD="${2-password}"
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 vi /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 vi /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
$ sudo systemctl status xinetd

TEST

Let’s Test Clustercheck and Mysqlchk

locally on db1, db2 and db3
$ 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 from your local mac (you should be on the same network to see the local ip)
$ telnet 10.10.4.1 9200

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

Percona XtraDB Cluster Node is synced.
Connection closed by foreign host.

remotely, try to connect to db1 from haproxy1 or haproxy2
$ telnet 10.10.4.1 9200

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

Percona XtraDB Cluster Node is synced.
Connection closed by foreign host.

remotely, try to connect to db2 from haproxy1 or haproxy2
$ telnet 10.10.4.2 9200

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

Percona XtraDB Cluster Node is synced.
Connection closed by foreign host.

remotely, try to connect to db3 from haproxy1 or haproxy2
$ telnet 10.10.4.3 9200

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

Percona XtraDB Cluster Node is synced.
Connection closed by foreign host.

let’s connect now to the MariaDB galera cluster database from haproxy1 using VIP (10.10.4.17)
you can try to connect from haproxy2 as well
$ sudo mysql -u root -p -h 10.10.4.17

MariaDB [(none)]> show databases;

 
WE CAN CONNECT TO MYSQL VIA 10.10.1.17
YEEEEYYYY!!!

TEST RELAY

HAPROXY1
get a base64 encoding of username (we use socketlabs.com smtp service)
$ perl -MMIME::Base64 -e 'print encode_base64("serverblabla");’

abVydmVyMTMzMQR=

get a base64 encoding of password
$ perl -MMIME::Base64 -e 'print encode_base64("Bs8b6JTn97Brq2Y6Gh");’

JnM6YjZKVG45N0JycTJZNEhm

type the italic-bold commands, you’ll enter above username and password for AUTH LOGIN
$ telnet smtp.socketlabs.com 25
Trying 54.86.14.32…
Connected to smtp.socketlabs.com.
Escape character is ‘^]’.
220 r6.us-east.aws.in.socketlabs.com Hurricane Server ESMTP service ready.
EHLO socketlabs.com
250-r6.us-east.aws.in.socketlabs.com Hello [50.201.92.222]
250-PIPELINING
250-SIZE 0
250-ENHANCEDSTATUSCODES
250-STARTTLS
250-AUTH CRAM-MD5 LOGIN
250-AUTH=LOGIN
250 OK
AUTH LOGIN
334 VXNlcm5hbWU6
abVydmVyMTMzMQR=
334 UGFzc3dvcmQ6
JnM6YjZKVG45N0JycTJZNEhm
235 2.7.0 Accepted.
mail from: <msen@na.edu>
250 2.1.0 sender msen@na.edu OK
rcpt to: <mehmetsen80@gmail.com>
250 2.1.5 recipient mehmetsen80@gmail.com OK
data
354 Send data. End with CRLF.CRLF
From:msen@na.edu
Subject:test subject

this is a test
.
250 2.0.0 Message received and queued as c80000008b34a2.
Connection closed by foreign host.

LET’S TEST HAPROXY

first make sure haproxy is running properly with ports 80 and 3306
$ ss -lntu

Netid State      Recv-Q Send-Q                                                      Local Address:Port                                                                     Peer Address:Port              
tcp   LISTEN     0      	128                                                                     *:80                                                                                  		*:*                  
tcp   LISTEN     0      	128                                                                     *:22                                                                                  		*:*                  
tcp   LISTEN     0      	100                                                             127.0.0.1:25                                                                                  	*:*                  
tcp   LISTEN     0      	128                                                            10.10.4.17:3306                                                                                	*:*                  
tcp   LISTEN     0      	128                                                                    :::22                                                                                 		:::*                  
tcp   LISTEN     0      	100                                                                   ::1:25                                                                                		:::*
  • 1- you don’t have to reboot or shutdown first, just stop keepalived and haproxy on haproxy1 and follow http://10.10.4.17
    you’ll se it will go to the haproxy2
    $ sudo systemctl stop keepalived
    $ sudo systemctl stop haproxy
  • 2- reboot haproxy1, follow up http://10.10.4.17 refresh manually after a while, you’ll see haproxy2
    $ sudo shutdown -r
    reboot haproxy2, follow up http://10.10.4.17 refresh manually after a while, you’ll see haproxy1
    $ sudo shutdown -r

    Note: Sometimes reboot doesn’t work for enough test but sudo shutdown -P should always work!

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

  • 3- shutdown haproxy1 completely, follow up http://10.10.4.17 refresh manually after a while, you’ll see haproxy2
    $ sudo shutdown -P
    when you restart haproxy1, the http://10.10.4.17 will show back to haproxy1

WE CAN WATCH ALL STREAMS from http://10.10.4.17 PRECISELY
YEEEEYYYY!!!

14 thoughts on “INSTALL HAPROXY AND KEEPALIVED ON CENTOS 7 FOR MARIADB CLUSTER”

  1. Pingback: NAU MOODLE CLUSTER | snapdev

  2. Do you know if tproxy is compiled with the version for 7, I believe it is currently 1.5.14? I got this working, but with the issue that primary IP of the haproxy machine is recogized as the src for the connection. I would like to change that to the actual source, but adding the line:

    source 0.0.0.0 usesrc clientip

    takes me back to the error:

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

    Liked by 1 person

    Reply

      • No, I have 5 total machines, 2 with HAproxy and keepalived and 3 with mariadb galera cluster setup. they are all RHEL 7.2.

        It is MariaDB 10.0.21
        HAproxy 1.5.14
        keepalived 1.2.14

        This is the code for my haproxy on my test machine. Mostly copied from here until I puppetize it for other machines.


        #---------------------------------------------------------------------
        # 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 10.76.10.105: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.76.10.105:3306
        mode tcp
        default_backend galera-cluster

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

        server mysqlcl00 10.76.10.101:3306 check port 9200 inter 5000 weight 1 maxconn 5000 rise 2 fall 2
        server mysqlcl01 10.76.10.102:3306 check port 9200 inter 5000 weight 1 maxconn 5000 rise 2 fall 2
        server mysqlcl02 10.76.10.103:3306 check port 9200 inter 5000 weight 1 maxconn 5000 rise 2 fall 2

        Like

      • And keepalived is :


        # Managed by Puppet
        vrrp_script chk_haproxy {
        script "killall -0 haproxy"
        interval 2
        weight
        }

        global_defs {
        notification_email {
        thaylin@gmail.com
        }
        notification_email_from keepalive@lvs2
        smtp_server localhost
        smtp_connect_timeout 60
        router_id mysql_cluster
        }

        vrrp_instance VI_2 {
        interface eno33559296
        state BACKUP
        virtual_router_id 1
        priority 140
        advert_int 1
        garp_master_delay 5

        # notify scripts and alerts are optional
        #
        # filenames of scripts to run on transitions
        # can be unquoted (if just filename)
        # or quoted (if has parameters)

        authentication {
        auth_type PASS
        auth_pass 1111
        }

        track_script {
        chk_haproxy
        }

        track_interface {
        eno33559296
        }

        virtual_ipaddress {
        10.76.10.105 dev eno33559296 brd 10.76.10.255
        }

        }

        Like

      • And also be carefule about the clustercheck script
        make sure you give the right username and password there

        MYSQL_USERNAME=”${1-username}”
        MYSQL_PASSWORD=”${2-password}”

        Like

    • Sorry for the late answer, I was out of country for the new year holiday..

      So, I guess you are having a typical problem with the mysql reading

      Please make sure you configure the CLUSTERCHECK AND MYSQLCHK correctly

      What do you see when you type from another machine to test your db1:

      $ telnet 10.76.10.101 9200

      you should have seen this:

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

      Like

      Reply

  3. Do you know if tpproxy is configured with this version of haproxy? I tried adding the option to pass the actual client IP:

    source 0.0.0.0 usesrc clientip

    but went back to the error:

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

    Like

    Reply

    • I think I have the same issue with cebowen, as per 3306 is already used by the galera. That’s why when you run haproxy it generates an error “ERROR 2013” as per mentioned. Does your configurations of db1 (10.10.1.1) and haproxy1(10.10.1.15) with VIPs (10.10.1.17) is on the same machine 2kere2beseder? If the same, this is also my problem but when I change my port (3307 in haproxy) with database it generates error or could not login at all or can you enlighten more to how overcome it?

      Like

      Reply

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

    that is not true. Only the master server is down, then the backup server takes over the vip….

    Like

    Reply

    • What I meant was that you see 10.10.1.17 on both of haproxyies when you list the ips, no matter which one points

      $ ip addr show

      inet 10.10.1.17/16 scope global secondary eno16777984
      valid_lft forever preferred_lft forever

      Like

      Reply

  5. Great page!
    The only thing that was missing (which took me hours to find after chasing multicast issues) was a firewall rule for vrrp.

    I came across this page (http://superuser.com/questions/837340/how-do-i-enable-set-multicast-rules-using-firewalld-in-rhel7-centos-7) where I found that I need the following for my keepalived to work (otherwise I had split brain.)

    firewall-cmd –zone=public –add-rich-rule=’rule family=”ipv4″ destination address=”224.0.0.18″ protocol value=”ip” accept’ –permanent
    Firewall-cmd –reload

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google photo

You are commenting using your Google account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s