0

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
    }
Cosmo
  • 1
  • 1
    All your nodes are called "mysql_1"? Does the haproxy status show the mysql backends as "up"? Also Mgm_nodes have an invalid IP address according to your info: 192.168.101 and 192.168.102... – wurtel Apr 30 '19 at 14:51
  • Mgm_node (haproxy + keepalived) are 101 and 102 and take the floating ip 100 under the keepalived control. the MySQL node are 3 and 4. HAproxy and keepalived are up and running. Backup role on the 102 and MASTER role on the 101. The master have the 100 floating IP also. – Cosmo Apr 30 '19 at 15:04

0 Answers0