INSTALL HAPROXY AND KEEPALIVED ON CENTOS 7 FOR MARIADB CLUSTER

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!!!

Advertisements

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

  1. 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

      • 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

  2. 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

    • 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

  3. 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

    • 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

  4. 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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s