MySQL Cluster with Redundant Load balancing

Discussion in 'Installation/Configuration' started by teec_michda, Oct 19, 2009.

  1. teec_michda

    teec_michda New Member

    Hey guys,

    I've been trying to get this load balancing working for a while now but completely stumped.

    All servers are virtual machines running Centos5

    My Setup is,
    1 X MySQL management server
    2 X MySQL Cluster nodes
    2 X Load Balancers

    The MySQL Cluster works perfectly and the heartbeat monitors of the loadbalancers works.

    My issue is when I try and connect to the Virtual IP it says
    Error 2003 (HY000): Can't connect to MySQL server on '192.168.12.210' (110).

    when I run ip addr sh seth0 I get
    2: seth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000
    link/ether 00:15:5d:0f:73:0a brd ff:ff:ff:ff:ff:ff
    inet 192.168.12.212/24 brd 192.168.12.255 scope global seth0
    inet 192.168.12.210/24 brd 192.168.12.255 scope global secondary seth0
    inet6 fe80::215:5dff:fe0f:730a/64 scope link
    valid_lft forever preferred_lft forever

    and when I run ldirectord ldirectord.cf status
    ldirectord for /etc/ha.d/ldirectord.cf is running with pid: 2430

    and I know the nodes are ready because ipvsadm -L -n
    IP Virtual Server version 1.2.1 (size=4096)
    Prot LocalAddress:port Scheduler Flags
    -> RemoteAddress:port Forward Weight ActiveConn InActConn
    TCP 192.168.12.210:3306 wrr
    -> 192.168.12.215:3306 Route 1 0 0
    -> 192.168.12.214:3306 Route 1 0 0

    The only clue I can see as to why this isn't working is the the load balancer isn't listening on port 3306.

    netstat -nap | grep 3306 shows lots of connections to the MySQL cluster, I presume testing it's available.

    The firewall is disabled so it should just be forwarding the traffic. Can someone help me before I go crazy please?
     
  2. teec_michda

    teec_michda New Member

    Have I got this in the right section?
     
  3. falko

    falko Super Moderator ISPConfig Developer

    Can you post the output of
    Code:
    netstat -tap
    from the active load balancer?
     
  4. teec_michda

    teec_michda New Member

    netstat -tap

    [root@Centos5-LB1 ~]# netstat -tap
    Active Internet connections (servers and established)
    Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
    tcp 0 0 Centos5-Cluster1:2208 *:* LISTEN 2093/hpiod
    tcp 0 0 *:sunrpc *:* LISTEN 1804/portmap
    tcp 0 0 *:rrh *:* LISTEN 1843/rpc.statd
    tcp 0 0 192.168.122.1:domain *:* LISTEN 2999/dnsmasq
    tcp 0 0 Centos5-Cluster1:ipp *:* LISTEN 2168/cupsd
    tcp 0 0 Centos5-Cluster1:smtp *:* LISTEN 2731/sendmail: acce
    tcp 0 0 Centos5-Cluster1:2207 *:* LISTEN 2098/python
    tcp 0 0 Centos5-LB1:53749 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53751 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53747 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53757 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53759 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53753 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53755 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53799 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53797 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53795 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53793 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53783 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53781 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53779 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53777 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53791 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53789 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53787 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53785 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53767 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53765 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53763 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53761 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53775 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53773 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53771 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:53769 192.168.12.215:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36715 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36713 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36719 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36717 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36707 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36705 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36711 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36709 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36729 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36723 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36721 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36727 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36725 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36683 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36681 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36687 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36685 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36679 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36677 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36699 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36697 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36703 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36701 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36691 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36689 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36695 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 Centos5-LB1:36693 192.168.12.214:mysql TIME_WAIT -
    tcp 0 0 *:ssh *:* LISTEN 2135/sshd
    tcp 0 0 Centos5-LB1:ssh ::ffff:192.168.12.200:50760 ESTABLISHED 7594/sshd: teec [pr
    [root@Centos5-LB1 ~]#
     
  5. falko

    falko Super Moderator ISPConfig Developer

    The load balancer doesn't seem to listen on port 3306...
     
  6. teec_michda

    teec_michda New Member

    That's what I thought, so here is my config for the load balancer:
    [root@Centos5-LB1 ~]# vim /etc/ha.d/ldirectord.cf
    # Global Directives
    checktimeout=10
    checkinterval=2
    autoreload=no
    logfile="local0"
    quiescent=yes
    virtual = 192.168.12.210:3306
    service = mysql
    real = 192.168.12.214:3306 gate
    real = 192.168.12.215:3306 gate
    checktype = negotiate
    login = "ldirector"
    passwd = "ldirectorpassword"
    database = "ldirectordb"
    request = "SELECT * FROM connectioncheck"
    scheduler = wrr


    Can someone point out what I've done wrong here?
     
  7. falko

    falko Super Moderator ISPConfig Developer

    What's the output of
    Code:
    ifconfig
    on the active load balancer?
     
  8. teec_michda

    teec_michda New Member

    the output of ifconfig is
    [root@Centos5-LB1 ~]# ifconfig
    lo Link encap:Local Loopback
    inet addr:127.0.0.1 Mask:255.0.0.0
    inet6 addr: ::1/128 Scope:Host
    UP LOOPBACK RUNNING MTU:16436 Metric:1
    RX packets:60 errors:0 dropped:0 overruns:0 frame:0
    TX packets:60 errors:0 dropped:0 overruns:0 carrier:0
    collisions:0 txqueuelen:0
    RX bytes:8356 (8.1 KiB) TX bytes:8356 (8.1 KiB)

    seth0 Link encap:Ethernet HWaddr 00:15:5D:0F:73:0A
    inet addr:192.168.12.212 Bcast:192.168.12.255 Mask:255.255.255.0
    inet6 addr: fe80::215:5dff:fe0f:730a/64 Scope:Link
    UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
    RX packets:1103141 errors:0 dropped:0 overruns:0 frame:0
    TX packets:1337692 errors:0 dropped:0 overruns:0 carrier:0
    collisions:0 txqueuelen:1000
    RX bytes:124523036 (118.7 MiB) TX bytes:152859759 (145.7 MiB)

    virbr0 Link encap:Ethernet HWaddr 00:00:00:00:00:00
    inet addr:192.168.122.1 Bcast:192.168.122.255 Mask:255.255.255.0
    inet6 addr: fe80::200:ff:fe00:0/64 Scope:Link
    UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
    RX packets:0 errors:0 dropped:0 overruns:0 frame:0
    TX packets:27 errors:0 dropped:0 overruns:0 carrier:0
    collisions:0 txqueuelen:0
    RX bytes:0 (0.0 b) TX bytes:7180 (7.0 KiB)

    [root@Centos5-LB1 ~]#

    But the output of ip addr sh seth0
    [root@Centos5-LB1 ~]# ip addr sh seth0
    2: seth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000
    link/ether 00:15:5d:0f:73:0a brd ff:ff:ff:ff:ff:ff
    inet 192.168.12.212/24 brd 192.168.12.255 scope global seth0
    inet 192.168.12.210/24 brd 192.168.12.255 scope global secondary seth0
    inet6 fe80::215:5dff:fe0f:730a/64 scope link
    valid_lft forever preferred_lft forever
    [root@Centos5-LB1 ~]#
     

Share This Page