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!
- Distinguish between two maximum values in a column with Power Query
- Power Query Conditional JOIN - JOIN with WHERE clause
- Calculate combinations for a range of text values in excel
- How to update a column's values in Power BI M Language
- What's the difference between DAX and Power Query (or M)?
- Sorting issue when consolidating monthly data into a year to date file. Using query, sorting by day, time, and letter
- Power Query `List.Generate` runs too slow
- Custom Colum based on specific dates in powerquery
- How do I properly use table.group in a PowerQuery query to dynamically summarize different rows and columns?
- Excel Power Query syntax issue transform multiple columns of records
- How to capitalize only first letter of a sentence
- Power Query Import For Power BI with group by on SelectRows
- Get data from config table cell when loading data source with power query
- Translate Excel Formula into Power Query
- Counting matching pairs of letters in lists of bigrams in Power Query M
- Bigrams of a string in Power Query M
- Power Query code to refer to another query (and how buffering works)
- M code for the equivalent of of Left (string, len(string)-11)
- How to add a missing column to the Transform Sample File
- How to add or insert ' (single quotes) for every string in a list in which strings are separated by commas in Power Query
- Power BI M code Split column from Lowercase to Uppercase won't include diacritics (accents)
- M Code(Power Query) to Remove Empty columns that runs fast
- How to find the value with date closest to another date
- Error when build AOSP 14 - internal error: undefined variable CTS_TEST_SUITES_DEFAULT
- Call a table using a dynamic identifier
- Problem getting the value from a cell using M in excel
- How to rename columns based on the data within them in Power Query M?
- Power Query return a field name based on parameter
- Power Query: after combining 2 tables got something strange
- Token Literal Expected Error in Power Query M Code using try and List.MatchesAny