How To Set Up A Load-Balanced MySQL Cluster =>problem with virtual address

I have followed the tutorial "How To Set Up A Load-Balanced MySQL Cluster".
I encountered problems with connection to Data cluster from the load balancer :

My configuration :
load-balancer1 = 192.168.1.11
load-balancer2 = 192.168.1.12
datacluster1 = 192.168.1.21
datacluster2 = 192.168.1.22
virtual address of data cluster = 192.168.1.100

lb1:/home#mysql -h 192.168.1.100 -u ldirector
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.100'

lb1:/home# mysql -h 192.168.1.21 -u ldirector
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4882
Server version: 5.0.32-Debian_7etch5 Debian etch distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

This is my /etc/ha.d/ldirectord.cf :

# Global Directives
checktimeout=10
checkinterval=2
autoreload=no
logfile="local0"
quiescent=yes
virtual = 192.168.1.100:3306
service = mysql
real = 192.168.1.21:3306 gate
real = 192.168.1.22:3306 gate
checktype = negotiate
login = "ldirector"
database = "ldirectordb"
request = "SELECT * FROM connectioncheck"
scheduler = wrr

This is my /etc/ha.d/haresources :

lb2 \
ldirectord::ldirectord.cf \
LVSSyncDaemonSwap::master \
IPaddr2::192.168.1.100/24/eth0/192.168.1.255

OUTput of command ip addr :

lb2:/etc/ha.d# ip addr sh eth0
2: eth0: <BROADCAST,MULTICAST,UP,10000> mtu 1500 qdisc pfifo_fast qlen 1000
link/ether 42:82:4e:d1:ac:56 brd ff:ff:ff:ff:ff:ff
inet 192.168.1.12/24 brd 192.168.1.255 scope global eth0
inet 192.168.1.100/24 brd 192.168.1.255 scope global secondary eth0

Can you help me, please? Thank you

 

Can you post the output of

Code:

netstat -tap

on datacluster1 and 2?

 

Output on data1:

(none):~# netstat -tap
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 *:mysql *:* LISTEN 1088/mysqld
tcp 0 0 data1:mysql 192.168.1.11:56897 TIME_WAIT -
tcp 0 0 data1:mysql 192.168.1.11:56939 TIME_WAIT -
tcp 0 0 data1:mysql 192.168.1.11:56949 TIME_WAIT -
tcp 0 0 data1:mysql 192.168.1.12:51725 TIME_WAIT -
tcp 0 0 data1:mysql 192.168.1.11:56885 TIME_WAIT -
tcp 0 0 data1:mysql 192.168.1.11:56905 TIME_WAIT -
tcp 0 0 data1:mysql 192.168.1.11:56901 TIME_WAIT -
tcp 0 0 data1:mysql 192.168.1.12:51743 TIME_WAIT -
tcp 0 0 data1:40804 management:1186 ESTABLISHED1088/mysqld
tcp 0 0 data1:mysql 192.168.1.12:51721 TIME_WAIT -
tcp 0 0 data1:39640 data2:57594 ESTABLISHED1088/mysqld
tcp 0 0 data1:mysql 192.168.1.12:51745 TIME_WAIT -
tcp 31 0 data1:40806 management:1186 ESTABLISHED1088/mysqld
tcp 0 0 data1:mysql 192.168.1.11:56863 TIME_WAIT -
tcp 0 0 data1:mysql 192.168.1.11:56943 TIME_WAIT -
tcp 0 0 data1:mysql 192.168.1.11:56947 TIME_WAIT -
tcp 0 0 data1:mysql 192.168.1.12:51703 TIME_WAIT -
tcp 0 0 data1:mysql 192.168.1.11:56957 TIME_WAIT -
tcp 0 0 data1:mysql 192.168.1.11:56951 TIME_WAIT -

Output on data2 :

(none):~# netstat -tap
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 data2:50377 *:* LISTEN 992/ndbd
tcp 0 0 *:mysql *:* LISTEN 1096/mysqld
tcp 0 0 data2:57594 *:* LISTEN 992/ndbd
tcp 0 0 data2:mysql 192.168.1.12:59409 TIME_WAIT -
tcp 0 0 data2:57594 data1:39640 ESTABLISHED992/ndbd
tcp 0 0 data2:48438 management:1186 ESTABLISHED1096/mysqld
tcp 0 0 data2:48439 management:1186 ESTABLISHED992/ndbd
tcp 0 0 data2:mysql 192.168.1.11:48733 TIME_WAIT -
tcp 0 0 data2:59397 data2:50377 ESTABLISHED1096/mysqld
tcp 0 0 data2:mysql 192.168.1.11:48809 TIME_WAIT -
tcp 0 0 data2:48441 management:1186 ESTABLISHED1096/mysqld
tcp 0 0 data2:mysql 192.168.1.11:48739 TIME_WAIT -
tcp 0 0 data2:50377 data2:59397 ESTABLISHED992/ndbd
tcp 0 0 data2:48437 management:1186 ESTABLISHED992/ndbd

Which is the meaning of TIME_WAIT ?

 

output on data 2:

mysql> show processlist;
+----+----------------------+--------------------+------+---------+------+------
-+------------------+
| Id | User | Host | db | Command | Time | State
| Info |
+----+----------------------+--------------------+------+---------+------+------
-+------------------+
| 21 | root | localhost | NULL | Query | 0 | NULL
| show processlist |
| 34 | root | management:47837 | NULL | Sleep | 26 |
| NULL |
| 35 | root | management:47838 | NULL | Sleep | 26 |
| NULL |
| 37 | root | management:47839 | NULL | Sleep | 16 |
| NULL |
| 38 | root | management:47840 | NULL | Sleep | 16 |
| NULL |
| 39 | root | management:47841 | NULL | Sleep | 16 |
| NULL |
| 41 | root | management:47842 | NULL | Sleep | 6 |
| NULL |
| 42 | unauthenticated user | 192.168.1.11:33114 | NULL | Connect | NULL | login
| NULL |
| 43 | unauthenticated user | 192.168.1.11:33116 | NULL | Connect | NULL | login
| NULL |
| 44 | unauthenticated user | 192.168.1.12:33070 | NULL | Connect | NULL | login
| NULL |
+----+----------------------+--------------------+------+---------+------+------
-+------------------+

I get this message of unauthenticated user and therefore load balancer (.12 and .11) can't connect to data2.
All connections to 192.168.1.100 (virtual address ) are redirect on data2 and are not balanced between data1 and data2....

 

Are you using a firewall on the dataclusters?

 

i've solved the problem....the thing was with the ldirectordb on the data nodes.
just have to set up correct username and passwd on the ldirectord.cf and that was it