mysql-cluster load balancing problem !!

Discussion in 'Server Operation' started by lychee_lch, Apr 9, 2007.

  1. lychee_lch

    lychee_lch New Member

    I have follow every step of how to mysql cluster and load balancing, But when i reach the page 7 "prepare the mysql cluster node for load balancing",after i type "ifup lo:0" .. Then i check the management node typed "ndb_mgm" >> "show". It show

    =============================================
    Cluster Configuration
    ---------------------
    [ndbd(NDB)] 2 node(s)
    id=2 (not connected, accepting connect from 10.100.100.102)
    id=3 (not connected, accepting connect from 10.100.100.103)

    [ndb_mgmd(MGM)] 1 node(s)
    id=1 @10.100.100.101 (Version: 5.0.27)

    [mysqld(API)] 2 node(s)
    id=4 (not connected, accepting connect from any host)
    id=5 (not connected, accepting connect from any host)

    ==============================================
    This show all the node have been disconnected when i typed "ifup lo:0"

    1) Is this normal when i typed ifup lo:0 the mysql cluster will disconnected, i cant even online with the both sql node ....????

    I donno this situation is normal or not, so i proceed to nex step. Everything look same in the tutorial except i came to a step where i typed "ipvsadm -L -n" and the result is ...

    =============================================
    IP Virtual Server version 1.2.1 (size=4096)
    Prot LocalAddress:port Scheduler Flags
    -> RemoteAddress:port Forward Weight ActiveConn InActConn
    TCP 10.100.100.105:3306 wrr
    -> 10.100.100.103:3306 Route 0 0 0
    -> 10.100.100.102:3306 Route 0 0 0
    =============================================


    whereby, the "weight" is "0" and in tutorial is "1" (actually i not really understand what does the weight means... ) T_T

    so without any choice, i still proceed to another step until "mysql -h 10.100.100.105 -u ldirector -p " with the correct password. But after i login, i try to use the "select all from connectioncheck table" and it say

    "Error 1105 (HY000): Failed to open 'connectioncheck', error while unpacking from engine"

    2) Is there other way to check the whole cluster-load balancer configured right ?
    3) Can i use the same one of the my four pc to access mysql ??
    4) The same network other pc that mentioned must installed mysql to access my virtual ip mysql ?

    Please help and give some advise !!!!! thanks .....
     
  2. falko

    falko Super Moderator

    Which distribution do you use?
    Are you sure you followed the tutorial as close as possible? Maybe you made a typo somewhere?
     
  3. lychee_lch

    lychee_lch New Member

    What do you mean "which distribution" ?
    I follow the "How To Set Up A Load-Balanced MySQL Cluster".

    The following are the spec i used;
    Operating system : Ubuntu 5.10
    * 10.100.100.102 MySQL cluster node 1 ( 1 NIC )
    * 10.100.100.103 MySQL cluster node 2 ( 1 NIC )
    * 10.100.100.101 Load Balancer 1 / MySQL cluster ( 1 NIC )
    management server ( 1 NIC )
    * 10.100.100.104 Load Balancer 2 ( 1 NIC )
    All connected to 1 switch then to internet.
    All package using apt-get as in tutorial except the mysql-max 5.0.27 version changed.

    From the tutorial, i succeed follow until page 4 and completed it with no problem. Mysql clustering work fine and pass all test mentioned. ( So i comfirm until here with 100% no mistake).

    So the following step is "6 Configure The Load Balancers" until the end of "page 7 Create A Database Called ldirector" which required to type "ifup lo:0" command at the last page. When i ifup lo:0 the network interface at the sql1 node and sql2 node , the mysql cluster management (ndb_mgm) will automatically show

    =============================================
    Cluster Configuration
    ---------------------
    [ndbd(NDB)] 2 node(s)
    id=2 (not connected, accepting connect from 10.100.100.102)
    id=3 (not connected, accepting connect from 10.100.100.103)

    [ndb_mgmd(MGM)] 1 node(s)
    id=1 @10.100.100.101 (Version: 5.0.27)

    [mysqld(API)] 2 node(s)
    id=4 (not connected, accepting connect from any host)
    id=5 (not connected, accepting connect from any host)

    ==============================================

    The mysql cluster disconnected. Without any error log or error specificed. The situation just like i pull out the cable.
    Can you let me know is this situation normal after i type ifup lo:0 at sql node mysql clustering with disconnected?


    I think i misunderstand or misconfiguring some part of the network. But i dono which part. I am sure there were no typo error( All have been re-check several times). So i wont ask many question first. I hope falko can correct me step by step. If any information needed please state it, i will provide it as soon as possible.

    Thanks falko for reply..... thanks !
     
  4. falko

    falko Super Moderator

    Debian Sarge, Debian Etch, Ubuntu, Fedora, Mandriva, SuSE, ...?
    Can you post all configuration files here that are mentioned in the tutorial?
     
  5. lychee_lch

    lychee_lch New Member

    I am using Ubuntu 5.10.

    So below are the configuration files at loadb1 10.100.100.101 start from installing ultramonkey :

    #gedit /etc/modules
    Code:
    # /etc/modules: kernel modules to load at boot time.
    #
    # This file contains the names of kernel modules that should be loaded
    # at boot time, one per line. Lines beginning with "#" are ignored.
    
    lp
    mousedev
    psmouse
    ip_vs_dh
    ip_vs_ftp
    ip_vs
    ip_vs_lblc
    ip_vs_lblcr
    ip_vs_lc
    ip_vs_nq
    ip_vs_rr
    ip_vs_sed
    ip_vs_sh
    ip_vs_wlc
    ip_vs_wrr
    
    
    #gedit /etc/apt/sources.list
    Code:
    #deb cdrom:[Ubuntu 5.10 _Breezy Badger_ - Release i386 (20051012)]/ breezy main restricted
    
    ## Uncomment the following two lines to fetch updated software from the network
    deb http://archive.ubuntu.com/ubuntu breezy main restricted
    deb-src http://archive.ubuntu.com/ubuntu breezy main restricted
    
    ## Uncomment the following two lines to fetch major bug fix updates produced
    ## after the final release of the distribution.
    deb http://archive.ubuntu.com/ubuntu breezy-updates main restricted
    deb-src http://archive.ubuntu.com/ubuntu breezy-updates main restricted
    
    ## Uncomment the following two lines to add software from the 'universe'
    ## repository.
    ## N.B. software from this repository is ENTIRELY UNSUPPORTED by the Ubuntu
    ## team, and may not be under a free licence. Please satisfy yourself as to
    ## your rights to use the software. Also, please note that software in
    ## universe WILL NOT receive any review or updates from the Ubuntu security
    ## team.
    deb http://archive.ubuntu.com/ubuntu breezy universe
    deb-src http://archive.ubuntu.com/ubuntu breezy universe
    
    deb http://security.ubuntu.com/ubuntu breezy-security main restricted
    deb-src http://security.ubuntu.com/ubuntu breezy-security main restricted
    
    deb http://security.ubuntu.com/ubuntu breezy-security universe
    deb-src http://security.ubuntu.com/ubuntu breezy-security universe
    
    deb http://archive.ubuntu.com/ubuntu breezy multiverse
    deb-src http://archive.ubuntu.com/ubuntu breezy multiverse
    
    deb http://archive.ubuntu.com/ubuntu breezy-backports main restricted universe multiverse 
    
    
    deb http://www.ultramonkey.org/download/3/ sarge main
    deb-src http://www.ultramonkey.org/download/3 sarge main
    
    #gedit /etc/sysctl.conf
    Code:
    #
    # /etc/sysctl.conf - Configuration file for setting system variables
    # See sysctl.conf (5) for information.
    #
    # Be warned that /etc/init.d/procps is executed to set the following
    # variables.  However, after that, /etc/init.d/networking sets some
    # network options with builtin values.  These values may be overridden
    # using /etc/network/options.
    
    #kernel.domainname = example.com
    #net/ipv4/icmp_echo_ignore_broadcasts=1
    
    # Enables packet forwarding
    net.ipv4.ip_forward = 1
    
    

    Configure Heartbeat
    #gedit /etc/ha.d/ha.cf
    Code:
    logfacility        local0
    #keepalive 2 
    #deadtime 10
    #warntime 10
    #initdead 10
    auto_failback off
    bcast        eth0
    mcast eth0 225.0.0.1 694 1 1
    node        loadb1
    node        loadb2
    respawn hacluster /usr/lib/heartbeat/ipfail
    apiauth ipfail gid=haclient uid=hacluster
    

    #gedit /etc/ha.d/haresources
    Code:
    
    
    loadb1        \
            ldirectord::ldirectord.cf \
            LVSSyncDaemonSwap::master \
            IPaddr2::10.100.100.105/8/eth0/10.255.255.255
    
    
    #gedit /etc/ha.d/authkeys
    Code:
    auth 3
    3 md5 hello
    
    Configre ldirectord
    #gedit /etc/ha.d/ldirectord.cf

    Code:
    # Global Directives
    checktimeout=10
    checkinterval=2
    autoreload=no
    logfile="local0"
    quiescent=yes
    
    virtual = 10.100.100.105:3306
            service = mysql
            real = 10.100.100.102:3306 gate
            real = 10.100.100.103:3306 gate
            checktype = negotiate
            login = "ldirector"
            passwd = "ldirectorpassword"
            database = "ldirectordb"
            request = "SELECT * FROM connectioncheck"
            scheduler = wrr
    
    

    test result
    #ip addr sh eth0
    Code:
    2: eth0: <BROADCAST,MULTICAST,UP> mtu 1500 qdisc pfifo_fast qlen 1000
        link/ether 00:90:27:a5:0d:3b brd ff:ff:ff:ff:ff:ff
        inet 10.100.100.101/8 brd 10.255.255.255 scope global eth0
        inet 10.100.100.105/8 brd 10.255.255.255 scope global secondary eth0
        inet6 fe80::290:27ff:fea5:d3b/64 scope link
           valid_lft forever preferred_lft forever
    
    
    #ldirectord ldirectord.cf status
    Code:
    ldirectord for /etc/ha.d/ldirectord.cf is running with pid: 6566
    
    #ipvsadm -L -n
    Code:
    IP Virtual Server version 1.2.1 (size=4096)
    Prot LocalAddress:Port Scheduler Flags
      -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
    TCP  10.100.100.105:3306 wrr
      -> 10.100.100.103:3306          Route   0      0          0
      -> 10.100.100.102:3306          Route   0      0          0
    
    
    #/etc/ha.d/resource.d/LVSSyncDaemonSwap master status
    Code:
    master running
    (ipvs_syncmaster pid: 6691)
    
     
  6. lychee_lch

    lychee_lch New Member

    loadb2 configuration file ::

    root@loadb2:/# vi etc/modules
    Code:
    # /etc/modules: kernel modules to load at boot time.
    #
    # This file contains the names of kernel modules that should be loaded
    # at boot time, one per line. Lines beginning with "#" are ignored.
    
    lp
    mousedev
    psmouse
    
    ip_vs_dh
    ip_vs_ftp
    ip_vs
    ip_vs_lblc
    ip_vs_lblcr
    ip_vs_lc
    ip_vs_nq
    ip_vs_rr
    ip_vs_sed
    ip_vs_sh
    ip_vs_wlc
    ip_vs_wrr
    
    
    #gedit /etc/apt/sources.list
    Code:
    #deb cdrom:[Ubuntu 5.10 _Breezy Badger_ - Release i386 (20051012)]/ breezy main restricted
    
    ## Uncomment the following two lines to fetch updated software from the network
    deb http://archive.ubuntu.com/ubuntu breezy main restricted
    deb-src http://archive.ubuntu.com/ubuntu breezy main restricted
    
    ## Uncomment the following two lines to fetch major bug fix updates produced
    ## after the final release of the distribution.
    deb http://archive.ubuntu.com/ubuntu breezy-updates main restricted
    deb-src http://archive.ubuntu.com/ubuntu breezy-updates main restricted
    
    ## Uncomment the following two lines to add software from the 'universe'
    ## repository.
    ## N.B. software from this repository is ENTIRELY UNSUPPORTED by the Ubuntu
    ## team, and may not be under a free licence. Please satisfy yourself as to
    ## your rights to use the software. Also, please note that software in
    ## universe WILL NOT receive any review or updates from the Ubuntu security
    ## team.
    deb http://archive.ubuntu.com/ubuntu breezy universe
    deb-src http://archive.ubuntu.com/ubuntu breezy universe
    
    deb http://security.ubuntu.com/ubuntu breezy-security main restricted
    deb-src http://security.ubuntu.com/ubuntu breezy-security main restricted
    
    deb http://security.ubuntu.com/ubuntu breezy-security universe
    deb-src http://security.ubuntu.com/ubuntu breezy-security universe
    
    deb http://archive.ubuntu.com/ubuntu breezy multiverse
    deb-src http://archive.ubuntu.com/ubuntu breezy multiverse
    
    deb http://archive.ubuntu.com/ubuntu breezy-backports main restricted universe multiverse 
    
    deb http://www.ultramonkey.org/download/3/ sarge main
    deb-src http://www.ultramonkey.org/download/3 sarge main
    
    #gedit /etc/sysctl.conf
    Code:
    # /etc/sysctl.conf - Configuration file for setting system variables
    # See sysctl.conf (5) for information.
    #
    # Be warned that /etc/init.d/procps is executed to set the following
    # variables.  However, after that, /etc/init.d/networking sets some
    # network options with builtin values.  These values may be overridden
    # using /etc/network/options.
    
    #kernel.domainname = example.com
    #net/ipv4/icmp_echo_ignore_broadcasts=1
    
    # Enables packet forwarding
    net.ipv4.ip_forward = 1
    

    #gedit /etc/ha.d/ha.cf
    Code:
    logfacility        local0
    bcast        eth0
    mcast eth0 225.0.0.1 694 1 0
    auto_failback off
    node        loadb1
    node        loadb2
    respawn hacluster /usr/lib/heartbeat/ipfail
    apiauth ipfail gid=haclient uid=hacluster
    
    #gedit /etc/ha.d/haresources
    Code:
    loadb1        \
            ldirectord::ldirectord.cf \
            LVSSyncDaemonSwap::master \
            IPaddr2::10.100.100.105/8/eth0/10.255.255.255
    
    #gedit /etc/ha.d/authkeys
    Code:
    auth 3
    3 md5 hello
    
    #gedit /etc/ha.d/ldirectord.cf
    Code:
    # Global Directives
    checktimeout=10
    checkinterval=2
    autoreload=no
    logfile="local0"
    quiescent=yes
    
    virtual = 10.100.100.105:3306
            service = mysql
            real = 10.100.100.102:3306 gate
            real = 10.100.100.103:3306 gate
            checktype = negotiate
            login = "ldirector"
            passwd = "ldirectorpassword"
            database = "ldirectordb"
            request = "SELECT * FROM connectioncheck"
            scheduler = wrr
    
    #ip addr sh eth0
    Code:
    2: eth0: <BROADCAST,MULTICAST,UP> mtu 1500 qdisc pfifo_fast qlen 1000
        link/ether 00:90:27:a6:3e:49 brd ff:ff:ff:ff:ff:ff
        inet 10.100.100.104/8 brd 10.255.255.255 scope global eth0
        inet6 fe80::290:27ff:fea6:3e49/64 scope link
           valid_lft forever preferred_lft forever
    


    # ldirectord ldirectord.cf status
    Code:
    ldirectord is stopped for /etc/ha.d/ldirectord.cf
    

    #ipvsadm -L -n
    Code:
    IP Virtual Server version 1.2.1 (size=4096)
    Prot LocalAddress:Port Scheduler Flags
      -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
    
    #/etc/ha.d/resource.d/LVSSyncDaemonSwap master status
    Code:
    master stopped
    
     
  7. lychee_lch

    lychee_lch New Member

    And below is for node sql1 and node sql2:


    sql1 node configuration file:


    #gedit /etc/sysctl.conf
    Code:
    #
    # /etc/sysctl.conf - Configuration file for setting system variables
    # See sysctl.conf (5) for information.
    #
    # Be warned that /etc/init.d/procps is executed to set the following
    # variables.  However, after that, /etc/init.d/networking sets some
    # network options with builtin values.  These values may be overridden
    # using /etc/network/options.
    
    #kernel.domainname = example.com
    #net/ipv4/icmp_echo_ignore_broadcasts=1
    
    # Enable configuration of arp_ignore option
    net.ipv4.conf.all.arp_ignore = 1
    
    # When an arp request is received on eth0, only respond if that address is
    # configured on eth0. In particular, do not respond if the address is
    # configured on lo
    net.ipv4.conf.eth0.arp_ignore = 1
    
    # Ditto for eth1, add for all ARPing interfaces
    #net.ipv4.conf.eth1.arp_ignore = 1
    
    
    # Enable configuration of arp_announce option
    net.ipv4.conf.all.arp_announce = 2
    
    # When making an ARP request sent through eth0 Always use an address that
    # is configured on eth0 as the source address of the ARP request.  If this
    # is not set, and packets are being sent out eth0 for an address that is on
    # lo, and an arp request is required, then the address on lo will be used.
    # As the source IP address of arp requests is entered into the ARP cache on
    # the destination, it has the effect of announcing this address.  This is
    # not desirable in this case as adresses on lo on the real-servers should
    # be announced only by the linux-director.
    net.ipv4.conf.eth0.arp_announce = 2
    
    # Ditto for eth1, add for all ARPing interfaces
    #net.ipv4.conf.eth1.arp_announce = 2
    
    #gedit /etc/network/interfaces
    Code:
    # This file describes the network interfaces available on your system
    # and how to activate them. For more information, see interfaces(5).
    
    # The loopback network interface
    auto lo
    iface lo inet loopback
    
    # This is a list of hotpluggable network interfaces.
    # They will be activated automatically by the hotplug subsystem.
    mapping hotplug
    	script grep
    	map eth0
    
    # The primary network interface
    iface eth0 inet static
    address 10.100.100.102
    netmask 255.0.0.0
    gateway 10.100.100.1
    
     auto eth0
    
    
     auto lo:0	
     iface lo:0 inet static
     address 10.100.100.105
     netmask 255.0.0.0
     gateway 10.100.100.1
     pre-up sysctl -p > /dev/null
    
    

    sql2 node configuration file :

    #gedit /etc/sysctl.conf
    Code:
    #
    # /etc/sysctl.conf - Configuration file for setting system variables
    # See sysctl.conf (5) for information.
    #
    # Be warned that /etc/init.d/procps is executed to set the following
    # variables.  However, after that, /etc/init.d/networking sets some
    # network options with builtin values.  These values may be overridden
    # using /etc/network/options.
    
    #kernel.domainname = example.com
    #net/ipv4/icmp_echo_ignore_broadcasts=1
    
    # Enable configuration of arp_ignore option
    net.ipv4.conf.all.arp_ignore = 1
    
    # When an arp request is received on eth0, only respond if that address is
    # configured on eth0. In particular, do not respond if the address is
    # configured on lo
    net.ipv4.conf.eth0.arp_ignore = 1
    
    # Ditto for eth1, add for all ARPing interfaces
    #net.ipv4.conf.eth1.arp_ignore = 1
    
    
    # Enable configuration of arp_announce option
    net.ipv4.conf.all.arp_announce = 2
    
    # When making an ARP request sent through eth0 Always use an address that
    # is configured on eth0 as the source address of the ARP request.  If this
    # is not set, and packets are being sent out eth0 for an address that is on
    # lo, and an arp request is required, then the address on lo will be used.
    # As the source IP address of arp requests is entered into the ARP cache on
    # the destination, it has the effect of announcing this address.  This is
    # not desirable in this case as adresses on lo on the real-servers should
    # be announced only by the linux-director.
    net.ipv4.conf.eth0.arp_announce = 2
    
    # Ditto for eth1, add for all ARPing interfaces
    #net.ipv4.conf.eth1.arp_announce = 2
    
    #gedit /etc/network/interfaces
    Code:
    # This file describes the network interfaces available on your system
    # and how to activate them. For more information, see interfaces(5).
    
    # The loopback network interface
    auto lo
    iface lo inet loopback
    
    # This is a list of hotpluggable network interfaces.
    # They will be activated automatically by the hotplug subsystem.
    mapping hotplug
    	script grep
    	map eth0
    
    # The primary network interface
    iface eth0 inet static
    address 10.100.100.103
    netmask 255.0.0.0
    gateway 10.100.100.1
    
    auto eth0
    
    auto lo:0
    iface lo:0 inet static
      address 10.100.100.105
      netmask 255.0.0.0
      pre-up sysctl -p > /dev/null
    
    Everything run fine and perfectly until i ifup lo:0 the interfaces in sql1&2 node. The connection between sql1 and sql2 corrupt on the spot, and mysql management node show disconnected message for ndb node with no error.

    For additional information :
    1. I am sure that was no insufient of memory issue.
    2. I was albe to ping the virtual ip 10.100.100.105.
    3. No connection at all for sql1 & sql2 node.
    4. mysql-max-5.0.27-linux-i686-glibc23.tar.gz (newest version) used.
    5. If disable the newly added code in the /etc/network/interfaces sql1 & sql2 will regain mysql cluster connection. :confused:


    Sorry for the "eye toturing" coding :eek: , i am so desperate to find the mistake and afraid the information i provided no enough, so i try to output all the configuration file here, incase someone have the similar case also can give opinion and advise. Really thanks falko for his patient in helping me to solve my problem and mistake. ;)
     

Share This Page