4 Nov 2022

Seamless failover with Galera Load Balancer

Continuing on from Setting up Galera Load Balancer (GLB), wouldn’t it be nice to ensure that Galera Load Balancer (GLB) handles failover, seamlessly?

Router:
------------------------------------------------------
        Address       :   weight   usage    map  conns
188.166.179.177:3306  :    1.000   0.500    N/A      1
  165.22.50.152:3306  :    1.000   0.500    N/A      1
   165.22.49.92:3306  :    1.000   0.000    N/A      0
------------------------------------------------------
Destinations: 3, total connections: 2 of 493 max

Pool: connections per thread:     1     1     0     0     0     0

Here is an example of a Galera Load Balancer (GLB) handling 3 backend MySQL servers, and it currently has 2 connections, evenly distributed between 2 hosts.

We can verify this by doing: select @@hostname; which shows:

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| galera     |
+------------+
1 row in set (0.01 sec)

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| galera-02  |
+------------+
1 row in set (0.00 sec)

To simulate, one can shutdown in this case, galera-02.

One can then verify it as such:

mysql> show status like  'wsrep_cluster_size';
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> show status like  'wsrep_cluster_size';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1355
Current database: *** NONE ***

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 8     |
+--------------------+-------+
1 row in set (0.03 sec)

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| galera-03  |
+------------+
1 row in set (0.00 sec)

You’ll notice that it reconnects since the server has gone away and we have now moved to a new host. It remains in the host routing table, but it doesn’t get sent new connections.

Router:
------------------------------------------------------
        Address       :   weight   usage    map  conns
188.166.179.177:3306  :    1.000   0.500    N/A      1
  165.22.50.152:3306  :    1.000   0.000    N/A      0
   165.22.49.92:3306  :    1.000   0.500    N/A      1
------------------------------------------------------
Destinations: 3, total connections: 2 of 493 max

Pool: connections per thread:     1     1     0     0     0     0

Galera Load Balancer (GLB) as you can see automatically rerouted the connections without issue. If we dropped yet another server, you will see:

  INFO: glb_pool.c:687: Async connection to 188.166.179.177:3306 failed: 111 (Connection refused)
   INFO: glb_pool.c:699: Reconnecting to 165.22.50.152:3306
   INFO: glb_pool.c:687: Async connection to 165.22.50.152:3306 failed: 111 (Connection refused)
   INFO: glb_pool.c:699: Reconnecting to 165.22.49.92:3306
Router:
------------------------------------------------------
        Address       :   weight   usage    map  conns
188.166.179.177:3306  :    1.000   0.000    N/A      0
  165.22.50.152:3306  :    1.000   0.000    N/A      0
   165.22.49.92:3306  :    1.000   0.667    N/A      2
------------------------------------------------------
Destinations: 3, total connections: 2 of 493 max

Pool: connections per thread:     1     1     0     0     0     0

The reconnecting happens automatically, and in this case is sending all connections to one node. You’ll notice that there is also a little more verbose information to tell you that Galera Load Balancer (GLB) couldn’t make the connection (connection refused) and it made another connection to ensure that the client still got a sensible backend node.

When we bring the other nodes back up, naturally, new connections get sent to the other nodes, but connections do not get redistributed. This can be seen as below (since we made a 3rd connection):

Router:
------------------------------------------------------
        Address       :   weight   usage    map  conns
188.166.179.177:3306  :    1.000   0.500    N/A      1
  165.22.50.152:3306  :    1.000   0.000    N/A      0
   165.22.49.92:3306  :    1.000   0.667    N/A      2
------------------------------------------------------
Destinations: 3, total connections: 3 of 493 max

Pool: connections per thread:     1     1     1     0     0     0

So here we have demonstrated seamless failover. Your application just needs to have 1 IP address (that of the Galera Load Balancer (GLB)), and as nodes within the routing table come and go, Galera Load Balancer (GLB) handles everything for you, the user. Naturally, if you’re doing server updates or migrations, having Galera Load Balancer (GLB) in the front is also very useful. Clearly it is not just for High Availability!

Remember, please request for binaries via contacting sales@galeracluster.com. Once you have access to the package repository, you’ll have access to RPMs.