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