Haproxy with MySQL Cluster Setup

Question

I have 2 MySQL servers with master-master replication between them. The replication is working fine.

I need to configure high availability between them so that if either one of them goes down, the other takes up.

I'm following this tutorial https://towardsdatascience.com/high-availability-mysql-cluster-with-load-balancing-using-haproxy-and-heartbeat-40a16e134691

Problem starts when I try to add theuser will be used by HAProxy for checking the health status of MySQL servers server1# mysql -u root -p mysql> CREATE USER 'haproxy_check'@'%';

I get a password policy error here. Since this is a production server hosting medical data, due to HIPAA compliance, I cannot have a user without a password in the MySQL database server. What can be the alternative approach to this.

Answer 1

I would not recommend using the architecture or systems described in that tutorial. Heartbeat has been EOL for nearly a decade, two-node master-master clusters only have a hope of resilience if thy employ highly effective fencing (as there is no hope of quorum in two nodes), which is not even touched on in that tutorial.

Further, there's very little point to a highly active cluster on two nodes - the goal is to be able to lose a node, so if the system gets "balanced" across two read/write replicas, both systems should enforce a maximum 40% load utilization. It's much more sensible and less overhead to use an active/passive system that describes a single primary read/write replica, with a read-only replica standing by that can be promoted.

It is also important to establish stronger cluster stability guarantees than a total lack of fencing or quorum can. Rather than express this with Heartbeat, this could be expressed in Pacemaker and Corosync. A third (very small) node can be used to establish quorum - either with Galera, or via Corosync. I would suggest both.

As an alternative to Galera, replicating the underlying storage and running only one SQL server instance is a very fast, low-overhead option. This can be accomplished via DRBD, Pacemaker, and Corosync. Managing MySQL clusters in Pacemaker is something that's been solved for over a decade, and works very reliably.

Answer 2

Your options at least include:

1. Check only that MySQL is listening on the TCP port. In other words, remove the option mysql-check from the HAProxy config. A TCP check will tell HAProxy whether MySQL is listening on port 3306 (which implies that it is running) but not whether it is possible to successfully log in.
2. On each MySQL server, run xinetd and write a script that handles the login, which is from the local machine. This is fairly simple. Something like the following, although note that this is not intended for use as-is; the MySQL command should be customized, password should be stored in some secure way, etc. This is essentially a simplified version of what the clustercheck script does in Percona XtraDB. HAProxy would do a http-check on the port exposed in xinetd and look at the response code.

#!/bin/sh
SEC=`mysql -u haproxy_check -pxxxxx -e "show slave status\G" 2>/dev/null | grep "Seconds_Behind_Master" | awk '{print $2}'`
if [ "${SEC}" == "" ] || [ ${SEC} -gt XX ]; then
  echo -en "HTTP/1.1 503 Service Unavailable\r\n"
  echo -en "Content-Type: text/plain\r\n"
  echo -en "Connection: close\r\n"
  echo -en "\r\n"
  echo -en "Unavailable.\r\n"
  sleep 0.1
  exit 1
else
  echo -en "HTTP/1.1 200 OK\r\n"
  echo -en "Content-Type: text/plain\r\n"
  echo -en "Connection: close\r\n"
  echo -en "\r\n"
  echo -en "Available.\r\n"
  sleep 0.1
  exit 1
fi

3. Use a full cluster (3 nodes) with Galera and make use of the clustercheck script.
4. Accept that the check user will not have a password.

Answer 3

just try "CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';"