How To Set Up A Load-Balanced MySQL Cluster With MySQL 5.1 - Page 4

7 Start The Load Balancer And Do Some Testing

Now we can start our two load balancers for the first time:

mysql-lb1.example.com / mysql-lb2.example.com:

/etc/init.d/ldirectord stop
/etc/init.d/heartbeat start

If you don't see errors, you should now reboot both load balancers.If you do see errors go to the end of this tutorial I might know whats the problem :) [Chapter 8]

mysql-lb1.example.com / mysql-lb2.example.com:

shutdown -r now

After the reboot we can check if both load balancers work as expected :

mysql-lb1.example.com / mysql-lb2.example.com:

ip addr sh eth0

The active load balancer should list the virtual IP address (10.0.1.10):

2: eth0: <BROADCAST,MULTICAST,UP> mtu 1500 qdisc pfifo_fast qlen 1000
    link/ether 00:16:3e:45:fc:f8 brd ff:ff:ff:ff:ff:ff
    inet 10.0.1.31/24 brd 192.168.0.255 scope global eth0
    inet 10.0.1.10/24 brd 192.168.0.255 scope global secondary eth0

The hot-standby should show this:

2: eth0: <BROADCAST,MULTICAST,UP> mtu 1500 qdisc pfifo_fast qlen 1000
    link/ether 00:16:3e:16:c1:4e brd ff:ff:ff:ff:ff:ff
    inet 10.0.1.32/24 brd 192.168.0.255 scope global eth0

mysql-lb1.example.com / mysql-lb2.example.com:

ldirectord ldirectord.cf status

Output on the active load balancer:

ldirectord for /etc/ha.d/ldirectord.cf is running with pid: 1603

Output on the hot-standby:

ldirectord is stopped for /etc/ha.d/ldirectord.cf

mysql-lb1.example.com / mysql-lb2.example.com:

ipvsadm -L -n

Output on the active load balancer:

IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  10.0.1.10:3306 wrr
  -> 10.0.1.33:3306               Route   1      0          0
  -> 10.0.1.34:3306               Route   1      0          0

Output on the hot-standby:

IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn

mysql-lb1.example.com / mysql-lb2.example.com:

/etc/ha.d/resource.d/LVSSyncDaemonSwap master status

Output on the active load balancer:

master running
(ipvs_syncmaster pid: 1766)

Output on the hot-standby:

master stopped
(ipvs_syncbackup pid: 1440)

If your tests went fine, you can now try to access the MySQL database from a totally different server in the same network (10.0.1.x) using the virtual IP address 10.0.1.10:

mysql -h 10.0.1.10 -u ldirector -p

(Please note: your MySQL client must at least be of version 4.1; older versions do not work with MySQL 5.)

You can now switch off one of the MySQL cluster nodes for test purposes; you should then still be able to connect to the MySQL database.

 

8 Annotations and hints

Errors with ldirectord:

/etc/init.d/ldirectord stop
/etc/init.d/heartbeat start
Stopping ldirectord Error [] reading file /etc/ha.d/ldirectord.cf at line 8: >Unknown command service=mysql

This error is due to formatting, it took me some time to figure it out.
The serivce= mysql is probably connected to /etc/services which is correct.
If you have libmysqlclient15 or 14 installed, fixing right formatting will do the trick like here:

vi /etc/heartbeat/ldirectord.cf

# Global Directives 
checktimeout=10 
checkinterval=2 
autoreload=no 
logfile="local0" 
quiescent=yes 
#after virtual=... make TABS (or double spaces) in every line
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 = "ldirector" 
  database = "ldirectordb" 
  request = "SELECT * FROM connectioncheck" 
  scheduler = wrr

And that should be it.

There are some important things to keep in mind when running a MySQL cluster:

1.If you have your databases and you want to use them in MySQL cluster please read this as it will guide you through how to change ENGINE=MyISAM to NDBCLUSTER.

2. Adding user to mysql database and changing GRANTS must be done on all data nodes since mysql database is MyISAM/InoDB. You can of course convert it's engine.

3.All data is stored in RAM! as a default, but Therefore you need lots of RAM on your cluster nodes. The formula how much RAM you need on each node goes like this:

(SizeofDatabase × NumberOfReplicas × 1.1 ) / NumberOfDataNodes

So if you have a database that is 1 GB of size, you would need 1.1 GB RAM on each node!

4.The cluster management node listens on port 1186, and anyone can connect. So that's definitely not secure, and therefore you should run your cluster in an isolated private network! Since it would be harder to manage it may be a good idea to make changes to /etc/hosts.deny or prepare iptables based firewall.

5.It's a good idea to have a look at the MySQL Cluster FAQ: http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-faq.html and also at the MySQL Cluster documentation: http://dev.mysql.com/doc/refman/5.1/en/ndbcluster.html

 

Links

Share this page:

6 Comment(s)

Add comment

Comments

From: Rico at: 2009-07-21 11:43:43

je n'ai jamais réussi a faire fonctionner le cluster avec ce tutorial :

 debian1:/usr/src/mysql-5.1.35# /usr/src/mysql-5.1.35/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/
Installing MySQL system tables...
090721 13:30:36 [ERROR] /usr/local/mysql/libexec/mysqld: unknown option '--skip-bdb'
090721 13:30:36 [ERROR] Aborting

090721 13:30:36 [Warning] Forcing shutdown of 1 plugins
090721 13:30:36 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete

 

les options de mysql ont du changer. Avec la 5.1.35 ca ne marche plus !

Tutoriel intéressant tout de même. Merci

 

Cordialement.

From: at: 2013-12-18 23:12:36

The problem with ndbd not found is due to not setting the PATH enviroment (which is a step in this tutorial). Since PATH may be a problem I have updated the howto with the full path to:

/usr/local/mysql/libexec/ndbd --initial

The error with --skip-bdb is easy to fix just edit /etc/mysql/my.cnf or your my.cnf file in other localisation and put a # before skip-bdb like this:

#skip-bdb. Restart MySQL and it should start without any problems.

From: Michael at: 2009-10-14 11:03:13

Hey,

 So I have folled this through and am stuck with the following block.

mkdir /usr/local/mysql/var/mysql-cluster
mkdir /usr/local/mysql/var/mysql-cluster/backup
cd /var/lib/mysql-cluster
ndbd --initial
/etc/init.d/mysql.server start 

 

The folder /var/lib/mysql-cluster does not exists and the command ndbd is not present in the build.  I am using version 5.1.39.

Is there something different between the two versions?  

I would apprechiate it if someone would come backt o me with an answer on how to complete the build.

 

Regards,

Michael

From: Ron at: 2010-09-20 20:38:24

Michael,

 I am having the exact same problem, I am using version 5.1.50.  Did you every find the solution?  Can anyone please help?

I have no directory called:  /var/lib/mysql-cluster

 Thanks.

From: at: 2008-06-20 08:54:26

the command CREATE DATABASE in a cluster mysql is a command that is already propagete in all the node. You dont need to create database in every node.

the rest is great!

 

From: Chris D. at: 2008-10-09 23:54:40

Your article is supposed to show how to set this up on CentOS 5.1 however the load balancing section seems to be using a debian based setup.  Is this correct?  Can you update this to reflect the use of CentOS 5.1 as referenced in the title?  I have everything working up to the point of the load balancers but now I am stuck because I do not have a server based on debianor ubuntu.