Setting up a Galera Cluster with Vagrant

Galera Cluster is an open-source, synchronous replication solution that improves the performance and availability of MySQL, MariaDB, and Percona XtraDB databases: Creating a Galera Cluster for high-availability MySQL replication can be streamlined using tools like Vagrant, MaxScale, and ClusterControl. This article provides a step-by-step guide to building such a cluster in a development environment.

What You Will Learn

Setting up a Galera Cluster with Vagrant see here about how to install vagrant : https://blog.aplikasi-pintar.id/how-to-install-vagrant-on-ubuntu/
Integrating MaxScale for load balancing (next step)
Managing the cluster with ClusterControl (nex step)

Tools Required

Vagrant: A tool for building and managing virtualized environments.
Galera Cluster: A synchronous multi-master replication plugin for MySQL.
MaxScale: A high-performance database proxy by MariaDB.
ClusterControl: A database management tool for monitoring and managing clusters.

Environment Setup

1. Prerequisites

Before starting, ensure the following:

Vagrant is installed on your machine.
VirtualBox or another supported provider is installed.
Basic familiarity with MySQL and Linux terminal commands.

2. Creating the Vagrantfile

The Vagrantfile is the configuration file where you define the virtual machines (VMs).

# -*- mode: ruby -*-
# vi: set ft=ruby :

ENV['VAGRANT_NO_PARALLEL'] = 'yes'

Vagrant.configure(2) do |config|

  # setting password for ssh (create bootstrap.sh file)
  config.vm.provision "shell", path: "bootstrap.sh"

  NodeCount = 4

  # Galera MariaDB Cluster
  (1..NodeCount).each do |i|
    vm_name = case NodeCount
              when 3
                i == NodeCount ? "galera-clusterControl" : "galera-node#{i}"
              when 4
                i == NodeCount ? "galera-maxScale" : "galera-node#{i}"
              else
                "galera-node#{i}"
              end
    config.vm.define vm_name do |node|
      node.vm.box = "bento/ubuntu-22.04"
      node.vm.hostname = "#{vm_name.downcase}.example.com"
      node.vm.network "private_network", ip: "192.168.59.1#{i}"
      node.vm.provider "virtualbox" do |v|
        v.name = vm_name
        v.memory = 2048
        v.cpus = 2
      end
    end
  end
end

Bootstrap.sh

#!/bin/bash

# Enable ssh password authentication
echo "[TASK 1] Enable ssh password authentication"
sed -i 's/^PasswordAuthentication .*/PasswordAuthentication yes/' /etc/ssh/sshd_config
echo 'PermitRootLogin yes' >> /etc/ssh/sshd_config

# Reload SSH service
echo "[TASK 2] Reload SSH service"
if systemctl reload ssh; then
    echo "SSH service reloaded successfully."
elif systemctl reload sshd; then
    echo "SSHD service reloaded successfully."
else
    echo "Failed to reload SSH service."
fi

# Set Root password
echo "[TASK 3] Set root password"
echo -e "galera\ngalera" | passwd root >/dev/null 2>&1

Save the above content as Vagrantfile.
Run vagrant up to create the VMs.

# create VM
vagrant up

# For destroy VM; -f mean force
vagrant destroy -f 

# This will re-create vm and run provision with latest script update
vagrant reload --provision

Now we have our VM and ready to build cluster.

192.168.59.11 galera-node1
192.168.59.12 galera-node2
192.168.59.13 galera-node3
192.168.59.14 galera-maxscale
192.168.59.15 clusterControl

# username: root
# password: galera

3. Setting Up Galera Cluster (all node)

Install MariaDB at galera-node1, galera-node-2, and galera-node3

# update package
apt update

# install dependencies
apt install curl software-properties-common dirmngr gnupg2 rsync lsof net-tools -y

# download installer mariadb
curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.6" --mariadb-maxscale-version="24.02.2" --os-type=ubuntu --os-version=jammy

# install mariadb
apt install mariadb-server mariadb-client -y

Check version of mariaDB in every node (all node)

mysql --version

# example output
# mysql  Ver 15.1 Distrib 10.6.19-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

Run mysql_secure_installation in every node (all node)

root@galera-node3:~# mysql_secure_installation

Enter current password for root (enter for none): <hit enter>
Change the root password? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y

# example output
# ...
# Cleaning up...
# All done!  If you've completed all of the above steps, your MariaDB
# installation should now be secure.
# Thanks for using MariaDB!

Create file called mariadb.log and mariadb-slow.log (all node)

# create file 
sudo touch /var/log/mariadb.log /var/log/mariadb-slow.log

# set permission to mysql
sudo chown mysql:mysql /var/log/mariadb.log /var/log/mariadb-slow.log

Stop mariadb service (all node)

systemctl stop mariadb

Edit or create file 50-server.cnf (all node)

cp /etc/mysql/mariadb.conf.d/50-server.cnf /etc/mysql/mariadb.conf.d/50-server.cnf.bak

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#

user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr


# lokasi datadir default
#datadir                = /var/lib/mysql

# lokasi datadir baru (move here)
datadir                 = /mnt/data/mysql

tmpdir                  = /tmp

# Skip DNS resolution to avoid connection delays
skip-name-resolve

# Bind address for local connections
bind-address            = 127.0.0.1

#
# * Fine Tuning
#

key_buffer_size         = 64M                 
max_allowed_packet      = 128M                
thread_cache_size       = 100                 
max_connections         = 200                 
interactive_timeout     = 300                 
wait_timeout            = 300                 

#
# * Query Cache Configuration
#

query_cache_size        = 128M
query_cache_limit       = 32M 
max_heap_table_size     = 16M 
tmp_memory_table_size   = 16M 
tmp_table_size          = 16M 

open_files_limit        = 30000
table_definition_cache  = 3000 
table_open_cache        = 10000
binlog_cache_size       = 512K 
thread_stack            = 512K 
join_buffer_size        = 512K 

myisam_sort_buffer_size = 32M  
sort_buffer_size        = 1M   
read_buffer_size        = 512K 
read_rnd_buffer_size    = 512K 

#
# * Logging and Replication
#

log_error               = /var/log/mariadb.log
slow_query_log          = ON
slow_query_log_file     = /var/log/mariadb-slow.log
long_query_time         = 30

# Log retention policy
expire_logs_days        = 10                  

#
# * SSL/TLS
#

# Uncomment and configure if SSL/TLS is required
#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 sets
#

character-set-server    = utf8mb4
collation-server        = utf8mb4_general_ci

#
# * InnoDB
#

innodb_file_per_table           = 1
innodb_buffer_pool_size         = 512M
innodb_buffer_pool_chunk_size   = 64M 
innodb_log_file_size            = 64M
innodb_io_capacity              = 2000
innodb_io_capacity_max          = 20000               
innodb_read_io_threads          = 4   
innodb_write_io_threads         = 4   

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.6 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.6]

move db directory (optional)

# create folder
sudo mkdir -p /mnt/data/mysql

# set owner to mysql
sudo chown -R mysql:mysql /mnt/data/mysql

# move data
sudo rsync -av /var/lib/mysql/ /mnt/data/mysql/

# re-initialize db
sudo mariadbd --initialize --user=mysql --datadir=/mnt/data/mysql

Galera setup

At VM galera-node1

cp /etc/mysql/mariadb.conf.d/60-galera.cnf /etc/mysql/mariadb.conf.d/60-galera.cnf.bak

#
# * Galera-related settings
#
# See the examples of server wsrep.cnf files in /usr/share/mysql
# and read more at https://mariadb.com/kb/en/galera-cluster/

[galera]
# Mandatory settings
wsrep_on                 = ON
wsrep_provider           = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_name       = "MariaDB Galera Cluster"
wsrep_cluster_address    = gcomm://192.168.59.11,192.168.59.12,192.168.59.13
binlog_format            = row
default_storage_engine   = InnoDB
innodb_autoinc_lock_mode = 2
wsrep_sst_method         = rsync
wsrep_node_address       = "192.168.59.11"  # sesuaikan di vm lainnya dengan "192.168.59.12" "192.168.59.13"
wsrep_node_name          = "galera-node1"   # sesuaikan di vm lainnya dengan "galera-node2" "galera-node3"

# Allow server to accept connections on all interfaces.
bind-address = 0.0.0.0

# Optional settings
#wsrep_slave_threads = 1
#innodb_flush_log_at_trx_commit = 0

At VM galera-node2

cp /etc/mysql/mariadb.conf.d/60-galera.cnf /etc/mysql/mariadb.conf.d/60-galera.cnf.bak

#
# * Galera-related settings
#
# See the examples of server wsrep.cnf files in /usr/share/mysql
# and read more at https://mariadb.com/kb/en/galera-cluster/

[galera]
# Mandatory settings
wsrep_on                 = ON
wsrep_provider           = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_name       = "MariaDB Galera Cluster"
wsrep_cluster_address    = gcomm://192.168.59.11,192.168.59.12,192.168.59.13
binlog_format            = row
default_storage_engine   = InnoDB
innodb_autoinc_lock_mode = 2
wsrep_sst_method         = rsync
wsrep_node_address       = "192.168.59.12"  # sesuaikan di vm lainnya dengan "192.168.59.12" "192.168.59.13"
wsrep_node_name          = "galera-node2"   # sesuaikan di vm lainnya dengan "galera-node2" "galera-node3"

# Allow server to accept connections on all interfaces.
bind-address = 0.0.0.0

# Optional settings
#wsrep_slave_threads = 1
#innodb_flush_log_at_trx_commit = 0

At VM galera-node3

cp /etc/mysql/mariadb.conf.d/60-galera.cnf /etc/mysql/mariadb.conf.d/60-galera.cnf.bak

#
# * Galera-related settings
#
# See the examples of server wsrep.cnf files in /usr/share/mysql
# and read more at https://mariadb.com/kb/en/galera-cluster/

[galera]
# Mandatory settings
wsrep_on                 = ON
wsrep_provider           = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_name       = "MariaDB Galera Cluster"
wsrep_cluster_address    = gcomm://192.168.59.11,192.168.59.12,192.168.59.13
binlog_format            = row
default_storage_engine   = InnoDB
innodb_autoinc_lock_mode = 2
wsrep_sst_method         = rsync
wsrep_node_address       = "192.168.59.13"  # sesuaikan di vm lainnya dengan "192.168.59.12" "192.168.59.13"
wsrep_node_name          = "galera-node3"   # sesuaikan di vm lainnya dengan "galera-node2" "galera-node3"

# Allow server to accept connections on all interfaces.
bind-address = 0.0.0.0

# Optional settings
#wsrep_slave_threads = 1
#innodb_flush_log_at_trx_commit = 0

Galera Cluster start

Make sure all mariadb service was stop at node1, node2, dan node3
run galera_new_cluster at galera-node1 (Make sure has no error)
Run systemctl start mariadb at another node node2 and node3 (Make sure has no error)

Verify connection between node (ESTABLISHED)

# node1
root@galera-node1:~# lsof -i:4567
    COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
    mariadbd 6626 mysql    8u  IPv4  43488      0t0  TCP *:4567 (LISTEN)
    mariadbd 6626 mysql   26u  IPv4  43707      0t0  TCP galera-node1:4567->192.168.59.13:50148 (ESTABLISHED)
    mariadbd 6626 mysql   50u  IPv4  43622      0t0  TCP galera-node1:4567->192.168.59.12:42378 (ESTABLISHED)

# node2
root@galera-node2:~# lsof -i:4567
    COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
    mariadbd 5522 mysql    8u  IPv4  41436      0t0  TCP *:4567 (LISTEN)
    mariadbd 5522 mysql    9u  IPv4  41438      0t0  TCP galera-node2:42378->192.168.59.11:4567 (ESTABLISHED)
    mariadbd 5522 mysql   15u  IPv4  42609      0t0  TCP galera-node2:4567->192.168.59.13:42828 (ESTABLISHED)

# node 3
root@galera-node3:~# lsof -i:4567
    COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
    mariadbd 5533 mysql    8u  IPv4  42099      0t0  TCP *:4567 (LISTEN)
    mariadbd 5533 mysql    9u  IPv4  42101      0t0  TCP galera-node3:50148->192.168.59.11:4567 (ESTABLISHED)
    mariadbd 5533 mysql   10u  IPv4  42102      0t0  TCP galera-node3:42828->192.168.59.12:4567 (ESTABLISHED)

Check sync status via mariadb console

SELECT * FROM information_schema.global_status WHERE variable_name IN ('WSREP_CLUSTER_STATUS','WSREP_LOCAL_STATE_COMMENT','WSREP_CLUSTER_SIZE','WSREP_EVS_REPL_LATENCY','WSREP_EVS_DELAYED','WSREP_READY','WSREP_CONNECTED');

root@galera-node1:~# mysql -U
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 35
    Server version: 10.6.19-MariaDB-ubu2204-log mariadb.org binary distribution

    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    MariaDB [(none)]> SELECT * FROM information_schema.global_status WHERE variable_name IN ('WSREP_CLUSTER_STATUS','WSREP_LOCAL_STATE_COMMENT','WSREP_CLUSTER_SIZE','WSREP_EVS_REPL_LATENCY','WSREP_EVS_DELAYED','WSREP_READY','WSREP_CONNECTED');
    +---------------------------+----------------+
    | VARIABLE_NAME             | VARIABLE_VALUE |
    +---------------------------+----------------+
    | WSREP_LOCAL_STATE_COMMENT | Synced         |
    | WSREP_EVS_DELAYED         |                |
    | WSREP_EVS_REPL_LATENCY    | 0/0/0/0/0      |
    | WSREP_CLUSTER_SIZE        | 3              |
    | WSREP_CLUSTER_STATUS      | Primary        |
    | WSREP_CONNECTED           | ON             |
    | WSREP_READY               | ON             |
    +---------------------------+----------------+
    7 rows in set (0.004 sec)

    MariaDB [(none)]> exit
    Bye