Connection issue with MariaDB and HAProxy (cluster)
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 1the 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?
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!
- ERROR 1071 (42000) : Specified key was too long; max key length is 3072 bytes
- Does using ASCII/Latin Charset speed up the database?
- What difference does WITH GRANT OPTION make?
- Columns to rows in one scan and one query
- MaxScale doesn't connect to Galera Cluster
- Enable password and unix_socket authentication for MariaDB root user?
- How can I access ddev's database with external database browsers or similar tools?
- Innacurate query with LIKE statement
- PHP XAMPP Config
- Using float columns in Laravel 11 migrations
- Dropping WP database, when not visible anywhere
- python inserting into Mariadb
- How to order by SUM() DESC when using WITH ROLLUP?
- Character and integer performance differences in MySQL clustered index
- How can I create and load a second database in ddev?
- How to acquire exclusive lock in replicated mysql and clustered servers?
- Docker Alpine: Error loading MySQLdb module
- SQL how to sort by three column but in tree like structure
- Mariadb in Docker: MariaDB Connector/Python requires MariaDB Connector/C >= 3.2.4, found version 3.1.16
- Delete all Occurences of a Substring in SQL-Statement in Python
- ERROR 1045 (28000): Access denied for user 'root'@'localhost'
- Unable to Initialize and Start MySQL in XAMPP on Windows 11 - Can't Connect to MySQL Server (10061)
- Get all french departement codes including Corsica
- Insert decimal values into MySQL with C#
- How to find the average of two columns but if one value is null, then only show me the other value?
- Map MariaDB geometry Point to custom Hibernate type
- Importing information of just one column from another table
- Extract Table Data and Content from mariadb SQL-Statements with Python and sqlite3?
- Select script in specific table having large data responding too slow, even delete duplicates not working
- Multiple UUID casting in an SQL Alchemy select statement