I'm new to clustering. I'm trying to configure keepalived in HA with HAProxy and MySQL cluster (+2 NDB cluster node). Configuration with 1 HAProxy (without keepalived and virtual ip address) seems to work properly (I can connect from client MySQL to HAProxy ip address and access one of the two MySQL node). When I add keepalived on the HA proxy node and add 1 HAProxy+keepalived BACKUP node I can't connect from MySQL client anymore:
mysql: ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0
firewall is disabled.
web server (Nginx) access is ok also with HAproxy+keepalived to virtual ip address !! I'm using Virtualbox with Ubuntu 18.04 and MySQL cluster 7.6.6.1. Ping is ok from any server to another.
MySQL Node #1: mysql_1 192.168.135.3 MySQL Node #2: mysql_1 192.168.135.4
Mgm_node #1 (HA Proxy + Keepalived): mysql_1 192.168.101
capodic@mgm_cluster1:~$ netstat -nlta
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:8080 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.53:53 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:443 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:1186 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:1186 127.0.0.1:48968 ESTABLISHED
tcp 0 0 192.168.135.100:1186 192.168.135.2:39346 ESTABLISHED
tcp 0 0 192.168.135.101:40602 192.168.135.4:3306 TIME_WAIT
tcp 0 0 192.168.135.100:1186 192.168.135.3:44122 ESTABLISHED
tcp 0 0 192.168.135.100:1186 192.168.135.4:34628 ESTABLISHED
tcp 0 0 127.0.0.1:48968 127.0.0.1:1186 ESTABLISHED
tcp 0 0 192.168.135.100:1186 192.168.135.1:35682 ESTABLISHED
tcp 0 0 192.168.135.100:1186 192.168.135.1:35578 ESTABLISHED
tcp 0 0 192.168.135.101:40654 192.168.135.4:3306 TIME_WAIT
tcp 0 0 192.168.135.101:22 192.168.135.200:6765 ESTABLISHED
tcp 0 0 192.168.135.100:1186 192.168.135.2:39522 ESTABLISHED
tcp6 0 0 :::22 :::* LISTEN
/etc/haproxy/haproxy.cfg
global
log 127.0.0.1 local0 notice
maxconn 2000
user haproxy
group haproxy
defaults
log global
retries 2
timeout connect 3000
timeout server 5000
timeout client 5000
option dontlognull
balance leastconn
option redispatch 1
option http-server-close
listen mysql-cluster
bind 0.0.0.0:3306
mode tcp
option mysql-check user haproxy_check
balance roundrobin
server mysql_1 192.168.135.3:3306 check
server mysql_2 192.168.135.4:3306 check
listen http
# bind 192.168.135.100:80
bind 0.0.0.0:80
mode tcp
option tcplog
option httpchk
balance roundrobin # source
maxconn 500
server web01 192.168.135.3:80 maxconn 250
server web02 192.168.135.4:80 maxconn 250
listen stats
bind *:8080
mode http
stats enable
stats uri /stats
stats realm HAProxy Statistics
/etc/keepalived/keepalived.conf ========
global_defs {
…
enable_script_security
script_user capodic
}
vrrp_instance VI_1 {
interface enp0s8 # check eth port
state MASTER
priority 200 # 100 for BACKUP
virtual_router_id 33
advert_int 1
unicast_src_ip 192.168.135.101
unicast_peer {
192.168.135.102
}
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.135.100/24
}
notify_master /etc/keepalived/master.sh
notify_backup /etc/keepalived/backup.sh
}
capodic@mgm_cluster1:~$ ip addr show enp0s8
3: enp0s8: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000 link/ether 08:00:27:fa:d2:c0 brd ff:ff:ff:ff:ff:ff
inet 192.168.135.101/24 brd 192.168.135.255 scope global enp0s8
valid_lft forever preferred_lft forever inet 192.168.135.100/24 scope global secondary enp0s8
valid_lft forever preferred_lft forever inet6 fe80::a00:27ff:fefa:d2c0/64 scope link
valid_lft forever preferred_lft forever
Mgm_node #2 (HA Proxy + keepalived): mysql_1 192.168.102
/etc/haproxy/haproxy.cfg =============================
global
log 127.0.0.1 local0 notice
maxconn 2000
user haproxy
group haproxy
defaults
….
listen mysql-cluster
bind *:3306
mode tcp
option mysql-check user haproxy_check
balance roundrobin
server mysql_1 192.168.135.3:3306 check
server mysql_2 192.168.135.4:3306 check
listen http
bind 0.0.0.0:80
mode tcp
option tcplog
option httpchk
balance roundrobin # source
maxconn 500
server web01 192.168.135.3:80 maxconn 250
server web02 192.168.135.4:80 maxconn 250
listen stats
bind *:8080
mode http
stats enable
stats uri /stats
stats realm HAProxy Statistics
stats auth admin:supersecret
/etc/keepalived/keepalived.conf ===================
global_defs {
enable_script_security
script_user capodic
}
vrrp_instance VI_1 {
interface enp0s8 # check eth port
state BACKUP
priority 100 # 100 for BACKUP
virtual_router_id 33
advert_int 1
unicast_src_ip 192.168.135.102
unicast_peer {
192.168.135.101
}
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.135.100/24
}