Galera cluster load balancer deployment

We have multiple website connecting to a single server running MariaDB - 10.3.11and due to heavy load, we decided to implement Galera Cluster replication, which has been implemented and we got the desired result when records updated within databases.

We need to distribute traffics between those Galera Cluster nodes (in place of single server connection) so we need to deploy Galera Cluster Load Balancer. We installed it over a new server with the configuration in /etc/default/glbd as shown below: (All servers running over Ubuntu 16.04.)

# This is a configuration file for glbd service script
#
# On Red Hat and derivatives it should be placed in /etc/sysconfig/glbd
#
# On Debian and derivatives it should be placed in /etc/default/glbd
#
# All settings besides LISTEN_ADDR are optional.
#
# Address to listen for client connections at. Mandatory parameter.
# To bind to all interfaces only port should be specified.
LISTEN_ADDR="3306"
# Address for controlling connection. Mandatory part is port.
# If not specified control socket will not be opened
CONTROL_ADDR="4444"
# Control FIFO name. It is opened always. glbd will refuse to start if
# this file already exists.
CONTROL_FIFO="/var/run/glbd.fifo"
# Number of threads (connection pools) to use. It is always a good idea
# to have a few per CPU core.
THREADS="3"
# Maximum connections. System open files limit will be modified to accommodate
# at least that many client connections provided sufficient privileges.
# Normally you should set it if you plan to go above 500.
#MAX_CONN=
# Target servers for client connections, space separated.
# Target specification: IP[:PORT[:WEIGHT]] (WEIGHT and PORT optional)
# WEIGHT defaults to 1, PORT defaults to LISTEN_ADDR port.
DEFAULT_TARGETS="node1_ip:3306:1 node2_ip:3306:1 node3_ip:3306:1"
# Other glbd options if any as they would appear on the command line.
OTHER_OPTIONS="--round"

We assumed LISTEN_ADDR to be 3306 as we need to switch connections of database with new server having Galera Cluster Load Balancer installed & listening on 3306 in place of single server (running MariaDB). Please correct us if our guess on port is wrong.

Section A defines current architecture and Section B defines proposed architectural change in following figure:

Here is the log of Galera Cluster Load Balancer when the service was started.

Jan 24 12:49:54 galera-lb-server systemd[1]: Starting LSB: run glbd daemon...
Jan 24 12:49:54 galera-lb-server glb[12112]: [Thu Jan 24 12:49:54 UTC 2019] glbd: starting...
Jan 24 12:49:54 galera-lb-server glb[12112]: INFO: glb_daemon.c:44: Changing effective user to 'daemon'
Jan 24 12:49:54 galera-lb-server glb[12112]: glb v1.0.1 (epoll)
Jan 24 12:49:54 galera-lb-server glb[12112]: Incoming address: 0.0.0.0:3306, control FIFO: /var/run/glbd.fifo
Jan 24 12:49:54 galera-lb-server glb[12112]: Control address: 127.0.0.1:4444
Jan 24 12:49:54 galera-lb-server glb[12112]: Number of threads: 3, max conn: 493, nodelay: ON, keepalive: ON, defer accept: OFF, linger: OFF, daemon: YES, lat.count: 0, policy: 'round-robin', top: NO, verbose: NO
Jan 24 12:49:54 galera-lb-server glb[12112]: Destinations: 3
Jan 24 12:49:54 galera-lb-server glb[12112]: 0: node1_ip:3306 , w: 1.000
Jan 24 12:49:54 galera-lb-server glb[12112]: 1: node2_ip:3306 , w: 1.000
Jan 24 12:49:54 galera-lb-server glb[12112]: 2: node3_ip:3306 , w: 1.000
Jan 24 12:49:54 galera-lb-server glb[12112]: glb v1.0.1 (epoll)
Jan 24 12:49:54 galera-lb-server glb[12112]: Incoming address: 0.0.0.0:3306, control FIFO: /var/run/glbd.fifo
Jan 24 12:49:54 galera-lb-server glb[12112]: Control address: 127.0.0.1:4444
Jan 24 12:49:54 galera-lb-server glb[12112]: Number of threads: 3, max conn: 493, nodelay: ON, keepalive: ON, defer accept: OFF, linger: OFF, daemon: YES, lat.count: 0, policy: 'round-robin', top: NO, verbose: NO
Jan 24 12:49:54 galera-lb-server glb[12112]: Destinations: 3
Jan 24 12:49:54 galera-lb-server glb[12112]: 0: node1_ip:3306 , w: 1.000
Jan 24 12:49:54 galera-lb-server glb[12112]: 1: node2_ip:3306 , w: 1.000
Jan 24 12:49:54 galera-lb-server glb[12112]: 2: node2_ip:3306 , w: 1.000
Jan 24 12:49:54 galera-lb-server glbd[12133]: glb_main.c:194: Started.
Jan 24 12:49:54 galera-lb-server glb[12112]: [Thu Jan 24 12:49:54 UTC 2019] glbd: started, pid=12133
Jan 24 12:49:54 galera-lb-server systemd[1]: Started LSB: run glbd daemon.

But we noticed error in log as below:

Jan 24 13:36:58 galera-lb-server glbd[12133]: glb_pool.c:687: Async connection to node1_ip:3306 failed: 111 (Connection refused)
Jan 24 13:36:58 galera-lb-server glbd[12133]: glb_pool.c:699: Reconnecting to node1_ip:3306

Here is the output of service glb getinfo

root@galera-lb-server:/# service glb getinfo
Router:
------------------------------------------------------ Address : weight usage map conns node1_ip:3306 : 1.000 0.000 N/A 0 node2_ip:3306 : 1.000 0.000 N/A 0 node3_ip:3306 : 1.000 0.000 N/A 0
------------------------------------------------------
Destinations: 3, total connections: 0 of 493 max

We can directly connect different website groups to any one of the node from replication but that won't be correct way. Please advise us how can we deploy and let us know if we are missing anything here.

1 Answer

We have bound IP address within all of our nodes that was same for all.

[mysqld]
bind-address = IP

Granted privileges for all users of MariaDB to load balancer IP and changed connection for all websites. There was silly mistake of setting correct MariaDB user access. Answer seems silly but hopefully will be helpful to other users.