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
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
now the virtual ip should work the same way. It shows the default apache page (Testing 123..)
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: 40Percona 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: 40Percona 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: 40Percona 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: 40Percona 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: 40Percona 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!!!
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)”
LikeLiked by 1 person
Can you share your haproxy settings and architecture with us? I guess you want to put haproxy together on the same server with mariadb?
LikeLike
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
LikeLike
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
}
}
LikeLike
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}”
LikeLike
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
LikeLike
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)”
LikeLike
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?
LikeLike
Make sure you use different machines to avoid port conflicts..
here is the entire moodle cluster architecture we use
https://snapdev.net/2015/10/26/nau-moodle-cluster/
10.10.1.17 is on haproxy1 and haproxy2 settings.. actually they are the same settings.. it’s just a virtual ip
LikeLike
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….
LikeLike
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
LikeLike
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
LikeLike
Thanks Ken, I didn’t know that either even though I didn’t experience that issue.. Thanks!
LikeLike