I'm new to this whole thing, so please be nice...
I'm using HAProxy as a proxy for multiple things, but the issue that keeps coming back is that I want to use a proxy to connect to a Mariadb Galera cluster. I'm using keepalived to assign a virtual IP, on which the proxy should then listen and balance the connections to the databases. Current status is:
3 machines, each running a set of (slightly modified, only the necesary stuff) keepalived, HAProxy and MariaDB (with Galera enabled).
Their IPs are 10.0.0.1, 10.0.0.2 and 10.0.0.3, virtual IP assigned by keepalived is 10.0.0.9.
HAProxy listens on 3310, then balances to 3306 to the other machines (since the machines running HAProxy also run the database, that's why I use non-default port).
ISSUE: I'm using a no-password test user "lol" with no privileges, connecting from 10.0.0.1 (which also has 10.0.0.9 assigned atm). I'll post the rest below.
mysql -u lol #connects
mysql -P 3310 -u lol #connects
mysql -h 10.0.0.9 -u lol #connects
mysql -h 10.0.0.9 -u lol #from 10.0.0.2, connects
mysql -h 10.0.0.9 -P 3310 -u lol #hangs a while, then spits out this message:
ERROR 2013 (HY000): Lost connection to MySQL server at 'handshake: reading initial communication packet', system error: 11
Why does this happen? My firewall is (for now) completely open, so that should not be the issue. I am including config files for MariaDB, keepalived and HAProxy below. (I purposefuly deleted unnecesary comments from the config files)
/etc/mysql/mariadb.conf.d/50-server.cnf (due to my way of installing, there are no other meaningful config files for MariaDB, but feel free to ask)
[server]
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /run/mysqld/mysqld.pid
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
lc-messages = en_US
skip-external-locking
#skip-name-resolve
bind-address = 0.0.0.0
#
# * Fine Tuning
#
#key_buffer_size = 128M
#max_allowed_packet = 1G
#thread_stack = 192K
#thread_cache_size = 8
#myisam_recover_options = BACKUP
#max_connections = 100
#table_cache = 64
#
# * Logging and Replication
#
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
log_error = /var/log/mysql/error.log
# Enable the slow query log to see queries with especially long duration
#slow_query_log_file = /var/log/mysql/mariadb-slow.log
#long_query_time = 10
#log_slow_verbosity = query_plan,explain
#log-queries-not-using-indexes
#min_examined_row_limit = 1000
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
#max_binlog_size = 100M
#
# * SSL/TLS
#
#ssl-ca = /etc/mysql/cacert.pem
#ssl-cert = /etc/mysql/server-cert.pem
#ssl-key = /etc/mysql/server-key.pem
#require-secure-transport = on
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
#
# * InnoDB
#
#innodb_buffer_pool_size = 8G
[embedded]
[mariadb]
[mariadb-10.5]
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.0.0.1,10.0.0.2,10.0.0.3"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# any cluster name
wsrep_cluster_name="MariaDB_Cluster"
# own IP address
wsrep_node_address="10.0.0.1"
/etc/keepalived/keepalived.conf (I only included the meaningful part, there are other VRRPs set up, also other machines are configured as slaves with lower priorities, keepalived shouldn't be the issue here, just adding it to clarify)
...
vrrp_instance VRRP2 {
state MASTER
interface ens19
virtual_router_id 111
priority 200
advert_int 1
authentication {
auth_type PASS
auth_pass 1011
}
virtual_ipaddress {
10.0.0.9/24
}
}
...
/etc/haproxy/haproxy.cfg (other things are set up here as well, excluding those)
global
log /dev/log local0
log /dev/log local1 notice
stats timeout 30s
user haproxy
group haproxy
daemon
# Default SSL material locations
ca-base /etc/ssl/certs
crt-base /etc/ssl/private
defaults
log global
mode http
option httplog
option dontlognull
timeout connect 5000
timeout client 50000
timeout server 50000
frontend galera
bind :3310
default_backend mariadb
backend mariadb
balance leastconn
option mysql-check user lol
server srv1 10.0.0.1:3306 check
server srv2 10.0.0.2:3306 check
server srv3 10.0.0.3:3306 check
I have the answer. The issue was, that I needed to add mode tcp to both the frontend and backend of the database. Now it's working as expected, if there will be any additional issues, I will comment on this answer. There are more things that depend on this.