You are here

Home » Blogs » Shinguz's blog

Galera Cluster for MySQL and hardware load balancer

Wed, 2013-08-14 21:30 — Shinguz

Our bigger customers where we help to deploy Galera Cluster for MySQL set-ups have some commercial hardware (e.g. F5 or Cisco) for load balancing instead of software load balancers.

For those hardware load balancer it is not possible to see if a Galera node is available or not because the MySQL daemon is still running and responding on port 3306 but the service is not available nonetheless.
So the load balancer still serves the Galera node while he feeds for example a joiner node with a SST. This would lead to application errors which is unlovely.

One can try somehow to teach the load balancer to find out if a Galera Cluster node is really available or not. But this requires a more sophisticated load balancer, know-how how to teach the load balancer the new behaviour and possible interaction between the MySQL node and the load balancer. See our other discussion for this mater.

An other concept we hit on this week is that we could also block the port 3306 of the MySQL node with firewall rules (iptables). Then the hardware load balancer does not see anybody listening on port 3306 any more and assumes that this IP address should not be served any more.

We also learned this week that the REJECT rule is better than the DROP rule when we want to have fast response time for immediate elimination of traffic.

The script block_galera_node.sh (newest version can be found in actual MyEnv) has to be hooked as before into the wsrep_notify_cmd variable and an additional sudoers rule has to be added for the mysql user.

#
# /etc/sudoers.d/mysql
# chmod 0440
#
mysql ALL = (root) NOPASSWD: /sbin/iptables
< br />

We are interested to hear your experience and your opinion about this approach.

Comments

Hello Shinguz,

Pay attention: Your script is a bit too restrictive. It will IMHO also close the port for the SST when you use the wsrep_sst_methode = mysqldump. Possibly you should restrict it to the Load Balancer IP or allow access at least from the possible donors.

Cheers,
Oli

olicomment Sat, 2013-08-17 11:39 

[07:51:52] shyperson: I would suggest to --insert the rule instead of --apendING
[07:52:15] shyperson: Just to make sure there are no other rules allowing mysqlport before
olicomment Wed, 2013-08-21 07:57

I've setup an F5 to actually do a select and check wsrep_ready. F5 BigIP has an builtin mysql client. For more details see this article on f5 devcentral.
dveedencomment Mon, 2013-09-02 20:13

This script probably will not work on Linux systems with systemd (CentOS 7, Ubuntu 16.04, etc.). Systemd cages a process and it seems like sudo rules do not apply any more for a process started with systemd. We are investigating in this...
Shinguzcomment Thu, 2017-01-05 10:18