HAProxy MySQL/MariaDB Load Balancing on AlmaLinux 9

Use HAProxy to load-balance MySQL or Percona XtraDB Cluster connections.

Step 1 – Create the HAProxy config section

listen mysql-cluster
    bind *:3306
    mode    tcp
    option  mysql-check user haproxy_check
    balance leastconn

    server db1 192.168.1.10:3306 check weight 1
    server db2 192.168.1.11:3306 check weight 1
    server db3 192.168.1.12:3306 check weight 1

Step 2 – Create the HAProxy check user on each DB node

CREATE USER 'haproxy_check'@'%' IDENTIFIED BY '';
-- No password needed for mysql-check; just the user must exist.

For PXC (check if node is in Primary state):

CREATE USER 'clustercheck'@'localhost' IDENTIFIED BY 'clustercheck';
GRANT PROCESS ON *.* TO 'clustercheck'@'localhost';
FLUSH PRIVILEGES;

Use clustercheck script from Percona:

option  httpchk GET /
server db1 192.168.1.10:9200 check

(Run clustercheck as a xinetd/systemd socket service on port 9200.)

Step 3 – Validate and reload

haproxy -c -f /etc/haproxy/haproxy.cfg
systemctl reload haproxy

Step 4 – Test

mysql -h 127.0.0.1 -P 3306 -u appuser -p -e "SELECT @@hostname;"

Repeat to see requests round-robin across nodes.