I'm new to MariaDB galera cluster load balancing and I'm trying to use HAProxy to do the load balancing. I have set up the MariaDB Galera cluster and it works perfectly.
In Haproxy, I have created a VIP for the DB cluster and if I look at the HAProxy statistics page it shows the VIP and all three nodes are green. This is how I have it configured:
#---------------------------------------------------------------------
# frontend_db_domain.com
#---------------------------------------------------------------------
frontend db_domain.com
bind ip:3306
mode tcp
option mysql-check user haproxy_check
default_backend back_db_domain.com
#---------------------------------------------------------------------
# backend_db_domain.com
#---------------------------------------------------------------------
backend back_db_domain.com
balance roundrobin
server db01.domain.com ip:3306 check
server db02.domain.com ip:3306 check
server db03.domain.com ip:3306 check
I configured a wordpress site to use the VIP address which is db.domain.com and wordpress shows Error establishing a database connection. If I use the db hostname or ip that hits the nodes directly everything works fine.
This is what shows in the HAProxy logs:
Mar 24 04:07:27 localhost haproxy[22096]: 1.2.3.4:56022 [24/Mar/2022:04:07:16.987] domain.com~ back_domain.com/nginx01.domain.com 68/0/1/10095/10164 500 2842 - - ---- 3/1/0/0/0 0/0 "GET /webdesigns/website1/ HTTP/1.1"
This is the nginx logs:
45.77.206.174 - - [24/Mar/2022:04:07:27 +0000] "GET /webdesigns/website1/ HTTP/1.1" 500 2539 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:98.0) Gecko/20100101 Firefox/98.0" "1.2.3.4"
There is no firewall in place that would block anything between all these hosts. They can all connect to each other. This is how the db nodes are configured:
[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
#bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://10.1.96.12,10.1.96.13,10.1.96.14"
# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="10.1.96.14"
wsrep_node_name="db03"
Not sure what I am missing but I think I have a misconfiguration on the HAProxy side but not sure what it can be. I have other VIPs but they're for web servers and stuff like that which all work fine. This the first VIP I have issues with. I would appreciate any and all help.
Thank you!
So, this is what I had to do to fix this.
From reading around, it seems for the MariaDB cluster to work in HAProxy you would have to do so in TCP mode. The original haproxy pair I had is in HTTP mode so the MariaDB cluster wasn't going to work.
I had to build a new haproxy pair and configure it in TCP mode and the cluster worked in HAProxy as intended. Unfortunately, it seems you can't mix modes in haproxy.
If anyone finds a way to mix modes or if it comes up in a future version that would be awesome.
Thank you!