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

[surfing86]

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

[falko]

Can you post the output of

netstat -tap

on datacluster1 and 2?

[surfing86]

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 ?

[surfing86]

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

[falko]

Are you using a firewall on the dataclusters?

[zer0]

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