MySQL Clustering; Load balancing is not working.
This is a project of personal experimentation and learning; in other
words, if I goof something up real bad, oh well. I can reinstall.
I wanted to learn more about MySQL clustering so I looked around and
found this guide [1]. It was the newest guide I found (8 months old), so
I decided to give it a shot. I went through the guide with a little
deviation and I end up with mostly the same results as the author. Data
written to one node, shows up real quick on the other node. The
management interface all gives the same results as the author too.
However, the big problem (and reason for my post) is the virtual IP for
the load balancing doesn't work right.
When I try connect to the virtual IP:
$ mysql -h 10.0.1.10 -u ldirector -p
Enter password:
It will sit here for a really long time before giving:
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.1.10' (111)
Which, if I understand right, means the client isn't getting data back
from the MySQL server. However, if I try to connect directly to one of
the nodes, I get success!
So I started to think about it. What gives? What did I do wrong? Then I
went back step by step to see where I went different from the author.
Step 1) I obviously have different hardware, and the author uses Etch
while I used Lenny (fresh net-install today so it is current stable).
Step 2) Author uses MySQL 5.1.24 compiled from source. I used apt (I am
not afraid to compile but I didn't because apt is awesome :).
$ apt-cache show mysql-server | grep -i version
Version: 5.0.51a-24
Well, I guess I can see the versioning being a problem. Maybe I should
update and compile a newer version. ??
Step 3, 4, & 5) I get the exact same results as author with exception of
trivial things (like our PID's don't match. Whoop-de-do. Of course they
won't. Doesn't affect the outcome.)
Step 6.1) For whatever reason, I can't update the GPG key for
ultramonkey, but I just hit y to install anyway on apt. Also, he
installs libdbd-mysql-perl from a download he gives from CPAN. I had
errors with his commands. Turns out you have to install
libmysqlclient15-dev (not 14) and you have to install make too! Then his
link to the DBD-mysql-3.0002.tar.gz didn't work for me. However, the
link to the newest (I believe) DBD-mysql-3.0008.tar.gz, _did_ work. So I
installed that one. So again, versioning issues?
Step 6.2 - 7) Everything works exactly as the author suggests it should,
/except/ for the last line of:
`mysql -h 10.0.1.10 -u ldirector -p`
As stated before, this fails. I can ping 10.0.1.10. I can even ssh into
10.0.1.10. They both redirect me to the (current) primary load balancer.
If I turn off the primary, I go to the secondary. Also, if I run
`ipvsadm -L -n` I can see the connections show up, even though they time
out. So I don't think it is the virtual part. I think it is the
redirection to the MySQL session.
The only thing I have found in the log files that doesn't look right, is
every once in a while this line is put into the /var/log/ldirectord.log:
Exiting with exit_status 2: configu_error: Configuration Error
The thing is, from everything I can find, it is correct. I posted at the
end of the email.
I would appreciate any feedback people may have.
Thanks!
~Stack~
[1]
http://www.howtoforge.com/setting-up-a-loadbalanced-mysql-cluster-with-m
ysql5.1
$cat /etc/ha.d/ldirector.cf
# Global Directives
checktimeout=10
checkinterval=2
autoreload=no
logfile="local0"
quiescent=yes
virtual=10.0.1.10:3306
service=mysql
real=10.0.1.33:3306 gate
real=10.0.1.34:3306 gate
checktype=negotiate
login="ldirector"
passwd="ldirectorpassword"
database="ldirectordb"
request="SELECT * FROM connectioncheck"
scheduler=wrr
Reply to: