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 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; }'