How To Set Up A Load-Balanced MySQL Cluster

sszxf

6 Configure The Load Balancers
　　Our MySQL cluster is finished now, and you could start using it now. However, we don't have a single IP address that we can use to access the cluster, which means you must configure your applications in a way that a part of it uses the MySQL cluster node 1 (sql1.example.com), and the rest uses the other node (sql2.example.com). Of course, all your applications could just use one node, but what's the point then in having a cluster if you do not split up the load between the cluster nodes? Another problem is, what happens if one of the cluster nodes fails? Then the applications that use this cluster node cannot work anymore.
　　The solution is to have a load balancer in front of the MySQL cluster which (as its name suggests) balances the load between the MySQL cluster nodes. The load blanacer configures a virtual IP address that is shared between the cluster nodes, and all your applications use this virtual IP address to access the cluster. If one of the nodes fails, then your applications will still work, because the load balancer redirects the requests to the working node.
　　Now in this scenario the load balancer becomes the bottleneck. What happens if the load balancer fails? Therefore we will configure two load balancers (loadb1.example.com and loadb2.example.com) in an active/passive setup, which means we have one active load balancer, and the other one is a hot-standby and becomes active if the active one fails. Both load balancers use heartbeat to check if the other load balancer is still alive, and both load balancers also use ldirectord, the actual load balancer the splits up the load onto the cluster nodes. heartbeat and ldirectord are provided by the Ultra Monkey package that we will install.
　　It is important that loadb1.example.com and loadb2.example.com have support for IPVS (IP Virtual Server) in their kernels. IPVS implements transport-layer load balancing inside the Linux kernel.

6.1 Install Ultra Monkey
　　Ok, let's start: first we enable IPVS on loadb1.example.com and loadb2.example.com:
　　loadb1.example.com / loadb2.example.com:
modprobe ip_vs_dh
modprobe ip_vs_ftp
modprobe ip_vs
modprobe ip_vs_lblc
modprobe ip_vs_lblcr
modprobe ip_vs_lc
modprobe ip_vs_nq
modprobe ip_vs_rr
modprobe ip_vs_sed
modprobe ip_vs_sh
modprobe ip_vs_wlc
modprobe ip_vs_wrr
　　In order to load the IPVS kernel modules at boot time, we list the modules in /etc/modules:
　　loadb1.example.com / loadb2.example.com:
vi /etc/modules

ip_vs_dh ip_vs_ftp ip_vs ip_vs_lblc ip_vs_lblcr ip_vs_lc ip_vs_nq ip_vs_rr ip_vs_sed ip_vs_sh ip_vs_wlc ip_vs_wrr

　　Now we edit /etc/apt/sources.list and add the Ultra Monkey repositories (don't remove the other repositories), and then we install Ultra Monkey:
　　loadb1.example.com / loadb2.example.com:
vi /etc/apt/sources.list

deb http://www.ultramonkey.org/download/3/ sarge main deb-src http://www.ultramonkey.org/download/3 sarge main

apt-get update
apt-get install ultramonkey libdbi-perl libdbd-mysql-perl libmysqlclient14-dev
　　Now Ultra Monkey is being installed. If you see this warning:

¦ libsensors3 not functional                                               ¦   ¦                                                                          ¦   ¦ It appears that your kernel is not compiled with sensors support. As a   ¦   ¦ result, libsensors3 will not be functional on your system.               ¦   ¦                                                                          ¦   ¦ If you want to enable it, have a look at "I2C Hardware Sensors Chip      ¦   ¦ support" in your kernel configuration.                                   ¦

　　you can ignore it.
　　Answer the following questions:
Do you want to automatically load IPVS rules on boot?
<-- No
Select a daemon method.
<-- none
　　The libdbd-mysql-perl package we've just installed does not work with MySQL 5 (we use MySQL 5 on our MySQL cluster...), so we install the newest DBD::mysql Perl package:
　　loadb1.example.com / loadb2.example.com:
cd /tmp
wget http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-3.0002.tar.gz
tar xvfz DBD-mysql-3.0002.tar.gz
cd DBD-mysql-3.0002
perl Makefile.PL
make
make install
　　We must enable packet forwarding:
　　loadb1.example.com / loadb2.example.com:
vi /etc/sysctl.conf

# Enables packet forwarding net.ipv4.ip_forward = 1