HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials - mysql-cluster load balancing problem !!

HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials (http://www.howtoforge.com/forums/index.php)

- Server Operation (http://www.howtoforge.com/forums/forumdisplay.php?f=5)

- - mysql-cluster load balancing problem !! (http://www.howtoforge.com/forums/showthread.php?t=11966)

mysql-cluster load balancing problem !!

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 .....

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

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 !

Quote:

Originally Posted by lychee_lch

What do you mean "which distribution" ?

Debian Sarge, Debian Etch, Ubuntu, Fedora, Mandriva, SuSE, ...?

Quote:

Originally Posted by lychee_lch

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 !

Can you post all configuration files here that are mentioned in the tutorial?

I am using Ubuntu 5.10.

Quote:

Can you post all configuration files here that are mentioned in the tutorial?

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)

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

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. ;)