Shinguz: MySQL and Galera Load Balancer (GLB)

Shinguz: MySQL and Galera Load Balancer (GLB):
When you install a Galera Cluster for MySQL for High Availability (HA) it is not enough to install the Database Cluster to achieve this goal. You also have to make the application aware of this HA functionality. This is typically done with some kind of load balancing mechanism between the database and the application.
We have several possibilities how to make such a load balancing possible:

• We build such a load balancing mechanism directly into the application.


• When we use Java or PHP we can use the fail-over functionality of the connectors (Connector/J, mysqlnd-ms).


• If we cannot touch the application we can put a load balancing mechanism between the application and the database. This can be done with:
  
  
  
  • a Hardware Load Balancer,
  
  
  • a Software Load Balancer (Pen, GLB, LVS, HAProxy and others...),
  
  
  • or with MySQL-Proxy.
  
  
  
  

Building the Galera Load Balancer

As an example we look at the Galera Load Balancer (GLB). The documentation about it you can find in the README file.
It can be built as follows:

wget http://www.codership.com/files/glb/glb-0.7.4.tar.gz
tar xf glb-0.7.4.tar.gz
cd glb-0.7.4
./configure
make
make install

Starting the Galera Load Balancer

The Galera Load Balancer will be started as follows:

./glbd --daemon --threads 6 --control 127.0.0.1:4444 127.0.0.1:3306 \
192.168.56.101:3306:1 192.168.56.102:3306:1 192.168.56.103:3306:1
Incoming address:       127.0.0.1:3306 , control FIFO: /tmp/glbd.fifo
Control  address:        127.0.0.1:4444
Number of threads: 6, source tracking: OFF, verbose: OFF, daemon: YES
Destinations: 3
   0:  192.168.56.101:3306 , w: 1.000
   1:  192.168.56.102:3306 , w: 1.000
   2:  192.168.56.103:3306 , w: 1.000

Querying the Galera Load Balancer

It can be queried as follows:

echo getinfo | nc -q 1 127.0.0.1 4444
Router:
----------------------------------------------------
        Address       :   weight   usage   conns
 192.168.56.101:3306  :    1.000   0.667     2
 192.168.56.102:3306  :    1.000   0.500     1
 192.168.56.103:3306  :    1.000   0.500     1
----------------------------------------------------
Destinations: 3, total connections: 4

and

echo getstats | nc -q 1 127.0.0.1 4444
in: 37349 out: 52598 recv: 89947 / 1989 send: 89947 / 1768 conns: 225 / 4
poll: 1989 / 0 / 1989 elapsed: 76.59987

Draining nodes with Galera Load Balancer

Let's assume, we want to take out node 192.168.56.101 from the Load Balancer for maintenance purposes, this can be done as follows:

echo 192.168.56.101:3306:0 | nc -q 1 127.0.0.1 4444
echo getinfo | nc -q 1 127.0.0.1 4444
Router:
----------------------------------------------------
        Address       :   weight   usage   conns
 192.168.56.101:3306  :    0.000   1.000     0
 192.168.56.102:3306  :    1.000   0.667     2
 192.168.56.103:3306  :    1.000   0.667     2
----------------------------------------------------
Destinations: 3, total connections: 4

Removing and adding nodes from Galera Load Balancer

If you want to shrink or grow your database cluster, removing and adding nodes works as follows:

echo 192.168.56.103:3306:-1 | nc -q 1 127.0.0.1 4444

echo 192.168.56.103:3306:2 | nc -q 1 127.0.0.1 4444

And now have fun playing around with your Galera Load Balancer...

PlanetMySQL Voting:
Vote UP /
Vote DOWN