MariaDB Galera Cluster – Ways of the nix's

Running a cluster takes care of replication of data between several nodes. This example is running a three node cluster.

Start by installing a base container with Debian 10 buster.

# lxc-create -n mariadb1 -t download -- -d debian -r buster -a amd64

Don’t forget to set fixed ip-addresses on your containers, i use dnsmasq to assign static leases based on hostname.

Install MariaDB

# lxc-start -n mariadb1
# lxc-attach -n mariadb1 -- apt update
# lxc-attach -n mariadb1 -- apt upgrade
# lxc-attach -n mariadb1 -- apt-get install mariadb-server mariadb-client
# lxc-attach -n mariadb1 -- mysql_secure_installation

Configure the /etc/hosts file

# lxc-attach -n mariadb1 -- vim /etc/hosts

Don’t forget to comment out or remove your hostname reference to 127.0.1.1

127.0.0.1	localhost
::1		localhost ip6-localhost ip6-loopback
ff02::1		ip6-allnodes
ff02::2		ip6-allrouters

10.10.0.137 mariadb1
10.10.0.138 mariadb2
10.10.0.139 mariadb3

No limits on MariaDB

# lxc-attach -n mariadb1 -- vim /etc/security/limits.conf

mysql soft nofile 65535
mysql hard nofile 65535

change the LimitNOFILE parameter to infinity

# lxc-attach -n mariadb1 -- vim /etc/systemd/system/mysql.service

LimitNOFILE=infinity

Reload configuration

# lxc-attach -n mariadb1 -- systemctl daemon-reload

Create a second and third container based on the first

# lxc-stop -n mariadb1
# lxc-copy -n mariadb1 -N mariadb2
# lxc-copy -n mariadb1 -N mariadb3

Set container hostnames

lxc-copy doen not change the hostname inside the container so that need to be fixed manually

# echo mariadb2 > mariadb2/rootfs/etc/hostname
# echo mariadb3 > mariadb3/rootfs/etc/hostname

Do not bind MariaDB to localhost

MariaDB service binds to localhost by default, comment out the bind line on the configuration file /etc/mysql/mariadb.conf.d/50-server.cnf

# sed -e '/^bind-address/ s/^#*/#/' -i mariadb1/rootfs/etc/mysql/mariadb.conf.d/50-server.cnf
# sed -e '/^bind-address/ s/^#*/#/' -i mariadb2/rootfs/etc/mysql/mariadb.conf.d/50-server.cnf
# sed -e '/^bind-address/ s/^#*/#/' -i mariadb3/rootfs/etc/mysql/mariadb.conf.d/50-server.cnf

Spin up your containers and check that they are running

# lxc-start -n mariadb1
# lxc-start -n mariadb2
# lxc-start -n mariadb3

# lxc-ls -f
    mariadb1 RUNNING 0 - 10.10.0.137 -    true
    mariadb2 RUNNING 0 - 10.10.0.138 -    true
    mariadb3 RUNNING 0 - 10.10.0.139 -    true

Configure the cluster nodes

# lxc-attach -n mariadb1 -- vim /etc/mysql/mariadb.conf.d/50-server.cnf
# lxc-attach -n mariadb2 -- vim /etc/mysql/mariadb.conf.d/50-server.cnf
# lxc-attach -n mariadb3 -- vim /etc/mysql/mariadb.conf.d/50-server.cnf

Paste the following information at the bottom of each file and don’t forget to change “wsrep_node_address=” on second and third node.

[galera]
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_address="gcomm://mariadb1,mariadb2,mariadb3"
wsrep_cluster_name="galera_cluster"

# Galera Node Configuration
wsrep_node_address="mariadb1"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

Start your first node

The first node needs to bee initialised with the “galera_new_cluster” command.

# lxc-attach -n mariadb1 -- galera_new_cluster

And then start the second and third node.

# lxc-attach -n mariadb2 -- systemctl start mariadb
# lxc-attach -n mariadb3 -- systemctl start mariadb

Check status of your nodes

# mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

In case all of your nodes go down…

Safe to Bootstrap

Step 1

Check if “safe_to_bootstrap=1” is set in any of the servers, if it is then start with that server first.

# grep safe_to_bootstrap /var/lib/mysql/grastate.dat

If one of them is set to “1” then start that one first.

# galera_new_cluster

Step 2

Check which server has the highest sequence number

# grep seqno /var/lib/mysql/grastate.dat

Change to “safe_to_bootstrap=1” on the server with the highest sequence number and try running “galera_new_cluster” on that server.