Galera Cluster

Setup the Cluster

Install the packages

On each node do as root:

• Add sources

# cat > /etc/apt/sources.list.d/mariadb.list << EOF
# MariaDB Server
# To use a different major version of the server, or to pin to a specific minor version, change URI below.
deb [arch=amd64] http://downloads.mariadb.com/MariaDB/mariadb-10.5/repo/ubuntu $(lsb_release -cs) main

deb [arch=amd64] http://downloads.mariadb.com/MariaDB/mariadb-10.5/repo/ubuntu $(lsb_release -cs) main/debug

# MariaDB MaxScale
# To use the latest stable release of MaxScale, use "latest" as the version
# To use the latest beta (or stable if no current beta) release of MaxScale, use "beta" as the version
deb [arch=amd64] https://dlm.mariadb.com/repo/maxscale/latest/apt $(lsb_release -cs) main

# MariaDB Tools
deb [arch=amd64] http://downloads.mariadb.com/Tools/ubuntu $(lsb_release -cs) main
EOF

• Install the packages

# apt update
# apt install mariadb-server mariadb-backup galera-4

Setup certificates for the cluster comunication

Make a CA certificate

Make a CA certificate with a very long lifetime as you dont want to make normal certificate updates at this point.

$ subject='/C=DE/ST=Hamburg/L=Hamburg/O=Organisation/OU=Databases/CN=Galera Cluster'
$ openssl req -new -x509 -nodes -days 365000 -newkey rsa:4096 -sha256 -keyout ca-key.pem -out ca-cert.pem -batch -subj "${subject}"

Create a certificate for each cluster node

$ for node in {1..4}
do
  emailAddress="dbadmin@server.de"
  servername="maria-${node}.server.de"
  subject="/C=DE/ST=Hamburg/L=Hamburg/O=Organisation/OU=Databases/CN=${servername}/emailAddress=${emailAddress}"
  openssl req  -newkey rsa:4096 -nodes -keyout ${servername}-key.pem    -out ${servername}-req.pem -batch -subj "${subject}"
  openssl x509 -req -days 365000 -set_serial $(printf "%02d" "${node}") -in  ${servername}-req.pem -out ${servername}-cert.pem -CA ca-cert.pem -CAkey ca-key.pem
done

Copy keys and certificates to the nodes

Copy the specific keys and certs to each node:

$ sudo mkdir --mode=0700 /etc/mysql/priv # put in here: maria-${node}.server.de-key.pem
$ sudo mkdir --mode=0750 /etc/mysql/cert # put in here: maria-${node}.server.de-cert.pem , ca-cert.pem

Configure the MariaDB Galera Cluster

Create a mariabackup user on each node

# mariadb
MariaDB [(none)]> grant reload, process, lock tables, replication client on *.* to 'mariabackup'@'localhost'         identified by 'the_very_secret_mariabackup_password';
MariaDB [(none)]> grant reload, process, lock tables, binlog monitor     on *.* to 'mariabackup'@'maria-1.server.de' identified by 'the_very_secret_mariabackup_password'; 
MariaDB [(none)]> grant reload, process, lock tables, binlog monitor     on *.* to 'mariabackup'@'maria-2.server.de' identified by 'the_very_secret_mariabackup_password'; 
MariaDB [(none)]> grant reload, process, lock tables, binlog monitor     on *.* to 'mariabackup'@'maria-3.server.de' identified by 'the_very_secret_mariabackup_password'; 
MariaDB [(none)]> grant reload, process, lock tables, binlog monitor     on *.* to 'mariabackup'@'maria-4.server.de' identified by 'the_very_secret_mariabackup_password'; 
MariaDB [(none)]> flush privileges;
MariaDB [(none)]>

Galera settings

This file is equal on all nodes:

/etc/mysql/mariadb.conf.d/zz-galera.cnf

[galera]
# Cluster Configuration
wsrep_provider           = /usr/lib/galera/libgalera_smm.so
# gcomm://{ comma seperated list of all cluster node IPs }
wsrep_cluster_address    = gcomm://10.33.6.1,10.33.6.2,10.33.6.3,10.33.6.4
wsrep_cluster_name       = MariaDB Galera Cluster
wsrep_on                 = ON

# Snapshot state transfer (SST): copy entire database, when new node joins
wsrep_sst_method = mariabackup

# set the the_very_secret_mariabackup_password to your real mariabackup password
wsrep_sst_auth = mariabackup:the_very_secret_mariabackup_password

[mariadb]
binlog_format            = ROW
innodb_autoinc_lock_mode = 2

This file is different per node (here for node1 IP 10.33.6.1): /etc/mysql/mariadb.conf.d/zz-node.cnf

[mariadb]
bind-address                    = 10.33.6.1

ssl_cert                        = /etc/mysql/cert/maria-1.server.de-cert.pem
ssl_key                         = /etc/mysql/priv/maria-1.server.de-key.pem
ssl_ca                          = /etc/mysql/cert/ca-cert.pem

[sst]
encrypt                         = 4
tkey                            = /etc/mysql/priv/maria-1.server.de-key.pem
tcert                           = /etc/mysql/cert/maria-1.server.de-cert.pem
tca                             = /etc/mysql/cert/ca-cert.pem

[galera]
wsrep_node_address              = 10.33.6.1
wsrep_node_incoming_address     = 10.33.6.1
wsrep_sst_receive_address       = 10.33.6.1
wsrep_provider_options          = "gcache.size = 1G; gcache.recover = yes; socket.ssl_key=/etc/mysql/priv/maria-1.server.de-key.pem;socket.ssl_cert=/etc/mysql/cert/maria-1.server.de-cert.pem;socket.ssl_ca=/etc/mysql/cert/ca-cert.pem ; gmcast.listen_addr = ssl://10.33.6.1:4567"

If you have something running on the default port 4567, you can change the base_port like this (here to 5000):

wsrep_provider_options          = "base_port = 5000; gcache.size = 1G; gcache.recover = yes; socket.ssl_key=/etc/mysql/priv/maria-1.server.de-key.pem;socket.ssl_cert=/etc/mysql/cert/maria-1.server.de-cert.pem;socket.ssl_ca=/etc/mysql/cert/ca-cert.pem ; gmcast.listen_addr = ssl://10.33.6.1:5000"

Do not forget to change the gmcast.listen_addr at the end.

Get knowledge about your Cluster

Show wsrep_provider_options

$ mariadb -NBABe 'show variables like "wsrep_provider_options"' | awk '{gsub(/$/,":\n",$1); gsub(/(;|$)/,";\n"); printf $0; }'

haproxy

Hosts:

• haproxy 10.42.42.11
• galera-ham-1 10.42.42.41
• galera-ham-2 10.42.42.42
• galera-muc-1 10.130.5.65
• galera-muc-2 10.130.5.66

/etc/haproxy/haproxy.cfg

defaults
        timeout connect 5000
        timeout client  50000
        timeout server  50000

global
	log /dev/log	local0
	log /dev/log	local1 notice
	chroot /var/lib/haproxy
	stats socket /run/haproxy/admin.sock mode 660 level admin expose-fd listeners
	stats timeout 30s
	user haproxy
	group haproxy
	daemon

	# Default SSL material locations
	ca-base /etc/ssl/certs
	crt-base /etc/ssl/private

	# See: https://ssl-config.mozilla.org/#server=haproxy&server-version=2.0.3&config=intermediate
    ssl-default-bind-ciphers ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384
    ssl-default-bind-ciphersuites TLS_AES_128_GCM_SHA256:TLS_AES_256_GCM_SHA384:TLS_CHACHA20_POLY1305_SHA256
    ssl-default-bind-options ssl-min-ver TLSv1.2 no-tls-tickets

frontend mysqld_listen
	bind 10.42.42.11:3306
	bind 127.0.0.1:3306
	mode tcp
	log global
	option  dontlognull
	option tcplog
	use_backend galera_cluster

# Load Balancing for Galera Cluster
backend galera_cluster
	balance leastconn
	#balance leastconn
	#balance roundrobin
	mode tcp
	log global
	option tcpka
	option log-health-checks
	option mysql-check user haproxy post-41
	option allbackups
	default-server inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxconn 1024 maxqueue 128 weight 100
	server galera-ham-1 10.42.42.41:3306 check send-proxy-v2 
	server galera-ham-2 10.42.42.42:3306 check send-proxy-v2 
	server galera-muc-1 10.130.5.65:3306 check send-proxy-v2 backup
	server galera-muc-2 10.130.5.66:3306 check send-proxy-v2 backup

Grant

On the galera cluster you need the haproy user:

MariaDB [(none)]> GRANT USAGE ON *.* TO `haproxy`@`10.42.42.11` identified by '';