Connection issue with MariaDB and HAProxy (cluster)

Question

I have a problem with the connection of my vm where haproxy is running, and the clusters. Let me explain the issue. I’ve setup 2 galera cluster machine with MariaDB (IPs 192.168.0.1 and 192.168.0.2) and a haproxy host (IP 192.168.0.3). I’ve followed almost all tutorial I’ve found in internet but I have always the same problem: I can’t connect and perform any query from the haproxy

Actual state:

galera work fine

root@db1:# mysql -u root -pPASSWORD -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'
+--------------+
| cluster size |
+--------------+
| 2            |
+--------------+

haproxy is well installed, and a user with all privileges is present in the database. The config file for haproxy is the following:

global
   log 127.0.0.1   local0                                                                  
   log 127.0.0.1   local1 notice                                                           
   maxconn 1024                                                                            
   user haproxy                                                                            
   group haproxy                                                                           
   daemon                                                                                  

   stats socket /var/lib/haproxy/stats.sock mode 600 level admin                           
   stats timeout 2m                                                                        

defaults                                                                                        
   log     global                                                                          
   mode    http                                                                            
   option  tcplog                                                                          
   option  dontlognull                                                                     
   retries 3                                                                               
   option redispatch                                                                       
   maxconn 1024                                                                            
   timeout connect 5000ms                                                                  
   timeout client  50000ms                                                                 
   timeout server  50000ms                                                                 

listen haproxy-monitoring                                                                       
   bind *:80                                                                               
   mode http                                                                               
   stats enable                                                                            
   stats show-legends                                                                      
   stats refresh   5s                                                                      
   stats uri       /                                                                       
   stats realm     Statistics                                                              
   stats auth      User:Password                                                         
   stats admin     if TRUE                                                                 

frontend lb1db                                                                                  
   bind *:3306                                                                             
   default_backend         galera-cluster                                                  

backend galera-cluster                                                                          
   balance roundrobin                                                                      
   server db1      192.168.0.1:3306 check weight 1                                        
   server db2      192.168.0.2:3306 check weight 1

the screen of stats show the vm up and running

The my.cnf is the following:

[galera]                                                                                        
wsrep_on=ON                                                                                     
wsrep_provider=/usr/lib/galera/libgalera_smm.so                                                 
wsrep_cluster_address=gcomm://192.168.0.94,192.168.0.93                                         
wsrep_node_addres=192.168.0.1. #vm1 IP; in the other config is present the vm2 IP                                                                  
wsrep_node_name=db1                                                                             
binlog_format=row                                                                               
default_storage_engine=InnoDB                                                                   
innodb_autoinc_lock_mode=2                                                                      

wsrep_cluster_name="Cluster_name"                                                                    
wsrep_sst_method=rsync                                                                          
bind-address=192.168.0.1  #vm1 IP; in the other config is present the vm2 IP

When I try to execute a query in the haproxy I have the following result:

mysql -u root -pPASSWORD --host=192.168.0.3 --port=3306
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.3' (111 "Connection refused")

As I wrote I followed several tutorial and the problem is always the same. Can anyone help me?

@Michael-sqlbot the current log file is empty, in the other 3 files (zipped) there are only this line: haproxy-systemd-wrapper[4414]: haproxy-systemd-wrapper: exit, haproxy RC=0 — S. Galati, Jun 05 '18 at 11:14

Brād Axe · Accepted Answer · 2020-10-01T17:41:11.800

The command you are using is trying to connect to the database service on the HAProxy server. As far as I can tell from your description, there is no database service running on this server(192.168.0.3).

If you would want to connect to the database service, you would need to use the IP address or hostname of one of the servers in the Galera cluster - e.g. 192.168.0.1 or 192.168.0.2.

You would have to create a user in the database service to authorize this kind of access (It does not seem like you did that, from reading your post). Secondly, it is not really recommended to have any remote access as 'root' enabled. You could create a remote user in the database cluster and grant some permissions. Login to one of the servers in the cluster and login to the database as root:

# mysql -u root -p

Then create the user with remote access and grant some privileges;

MariaDB [(none)]> create user 'haproxy'@'192.168.0.3'identified by 'aPassword';
MariaDB [(none)]> GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'haproxy'@'192.168.0.3';
MariaDB [(none)]> FLUSH PRIVILEGES;

And then try using the account 'haproxy' from the HAProxy server:

# mysql -u haproxy --host 192.168.0.1 -p

And this should now work. One thing I also noticed is that in your my.cnf for the cluster, your line with the cluster servers contains addresses other than those of the actual servers:

wsrep_cluster_address=gcomm://192.168.0.94,192.168.0.93

If this is actually not an artifact of copying and pasting, please make sure these are consistent with your configuration.

Good luck!

Connection issue with MariaDB and HAProxy (cluster)

1 Answers1