EN Mariadb galera - titandc/titan-sc-documentation GitHub Wiki

Preface

MariaDB Galera is a multi-master clustering solution allowing writing to any node making up the cluster.

MariaDB Galera is based on the RAFT protocol for internal decision inside the cluster, it is recommended to have an odd number of nodes.

This documentation has been tested on:

  • Debian 11 (bullseye)
  • MariaDB 10.6

Architecture

The proposed architecture is the following :

A MariaDB Galera cluster is formed on backend servers. Each machine hosting websites will be able to fetch/read data on one of the three database servers. A haproxy service is installed on each front end machine. Thus, if one of the members of the MariaDB Galera cluster becomes unavailable, the haproxy service will detect it and no longer use the member in question to read/write data.

To interconnect all the servers, the 192.168.0.0/24 network will be used with the following configuration:

MariaDB Galera server (backend):

  • Hostname galera-1 : 192.168.0.253/24
  • Hostname galera-2 : 192.168.0.252/24
  • Hostname galera-3 : 192.168.0.251/24

Web server (frontend):

  • Hostname web-1 : 192.168.0.2/24

Network configuration

All frontend and backend machines must be able to talk to each other, other than through their public IP addresses.

On the SmallCloud interface, in the "Networks" part, you must connect all the web servers (frontend) using the MariaDB Galera cluster as well as the database servers on the same switch.

Once connected, the enp9s0 interface will then be created on the machines. The next step is to configure this interface. On each machine, configure the interface with the correct IP address:

ip addr add <IP>/<NETMASK> dev enp9s0
ip link set enp9s0 up

Check that the machines are pinging each other.

Configuring the MariaDB Galera cluster

All members of the MariaDB Galera cluster must have the same version of the mariadb-server package.

The rest of the actions in this chapter should be done on the database servers.

Installation

The latest version of MariaDB as of this writing is version 10.6.

The following commands come from the download page of MariaDB.

apt-get install software-properties-common dirmngr apt-transport-https
apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
add-apt-repository 'deb [arch = amd64, i386, arm64, ppc64el] https://ftp.igh.cnrs.fr/pub/mariadb/repo/10.6/debian bullseye main'

Once the key and the repository are installed, it is possible to install MariaDB server:

apt-get update
apt-get install mariadb-server

These commands have to be executed on all the database servers.

Configuration

Cluster initialization will be done on a single node only. The other nodes of the cluster will join it after.

However, the configuration remains the same on all the nodes that will compose the cluster. The following configuration is therefore to be applied on all the nodes.

First, it is necessary to stop the MariaDB server:

systemctl stop mariadb

Open the file /etc/mysql/mariadb.conf.d/60-galera.cnf to add this (example with the server galera-1, it must be adapted for each database server):

binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address="192.168.0.253" # --- To change

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

# Galera Cluster Configuration
wsrep_cluster_name="cluster_galera"
wsrep_cluster_address="gcomm://192.168.0.253,192.168.0.252,192.168.0.251" # --- To change

# Galera Node Configuration
wsrep_node_address="192.168.0.253" # --- To change
wsrep_node_name="galera-1" # --- To change

In the bind-address field, it is strongly recommended to not use the public IP address of the server! Otherwise, the mariadb server will be exposed on the internet. Which is strongly not recommended!

Note: It is possible to have more logs than what provides the command journalctl -fu mariadb by uncommenting/modifying the following line in the file /etc/mysql/mariadb.conf.d/50 -server.cnf:

log_error = / var / log / mysql / error.log

Pay attention to the volume that this can represent.

Cluster initialization

On one of the cluster nodes (only one), you must initialize the MariaDB Galera cluster using the following command, for example the galera-1 server:

galera_new_cluster

Using this command (which is a simple bash script) will pass the --wsrep-new-cluster option when starting MariaDB, which will initialize the cluster.

Regarding the other two cluster members, they just have to join the cluster.

Join a cluster

This chapter is valid from the moment a node has already initialized the cluster using the galera_new_cluster command. In the case of the creation of the cluster, the following command must be executed on the other members of the cluster: galera-2 and galera-3. It can be used to add new members to the cluster:

systemctl start mariadb

MariaDB will try to connect to one of the servers in its configuration wsrep_cluster_address=. If at least one of the servers is reachable, it will then be able to connect to it and retrieve the list of all the nodes of the cluster from it and join the cluster (if it is accepted by its peers). If no node is available, refer to the chapter which talks about losing the entire cluster or initializing the cluster.

It is possible to check, from any node of the cluster, the state of the cluster with the following command (to be adapted as needed):

mysql -u root -p -e "show status like 'wsrep_cluster%';"

According to the documentation, MariaDB Galera uses 4 ports:

  • 3306
  • 4567: Galera replication port. Uses TCP and UDP
  • 4568: Incremental State Transfer List
  • 4444: State Snapshot Transfer (SST)

Loss of a node

The loss of a node is not a big deal as long as others members of the cluster are reachable, but it is preferable not to leave the cluster in this state for too long as decision making may be affected as the quorum is no longer reached.

To verify that a node has disappeared from the cluster, the following command is used to view the number of nodes present in the cluster from a healthy node:

mysql -u root -e "show status like 'wsrep_cluster%';"

To find out the name of the remaining or absent node(s), read the log file /var/log/mysql/error.log (if enabled). This kind of lines will be present and will indicate the remaining nodes:

2021-10-28 16:13:32 0 [Note] WSREP: Deferred close timer started for socket with remote endpoint: tcp: //192.168.0.252: 40522
2021-10-28 16:13:32 0 [Note] WSREP: forgetting 32dae230-94a6 (tcp: //192.168.0.252: 4567)
2021-10-28 16:13:32 0 [Note] WSREP: Deferred close timer handle_wait Operation aborted. for 0x562f393f5840
2021-10-28 16:13:32 0 [Note] WSREP: Deferred close timer destruct

[...]

2021-10-28 16:13:32 2 [Note] WSREP: =================================== ==============
View:
  id: 42d895db-37c3-11ec-bf4d-3e726e21fed5: 44
  status: primary
  protocol_version: 4
  capabilities: MULTI-MASTER, CERTIFICATION, PARALLEL_APPLYING, REPLAY, ISOLATION, PAUSE, CAUSAL_READ, INCREMENTAL_WS, UNORDERED, PREORDERED, STREAMING, NBO
  final: no
  own_index: 0
  members (2):
        0: 0a07c4c2-37f3-11ec-bf5c-af4c93cfb413, galera-1
        1: 346df9ad-37f9-11ec-bd58-5a6cfee821df, galera-3
==================================================

We can see here that the server galera-2 is missing.

In all cases, it will be necessary to (re-)start the service on the server being reported missing. A full synchronization will be done with its peers once the fallen node has joined the cluster.

systemctl restart mariadb.service

Complete cluster loss

When all the nodes in the cluster have fallen, you must find the node that fell last to restart it first. This is potentially the one that will have the most up-to-date data.

In the same way as when launching the cluster for the first time, you must use the following command on the node that fell last:

galera_new_cluster

If this command is used on a node that is not the last to shutdown, the start of the MariaDB server service will fail and the following error will be in the logs:

[ERROR] WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1.

If the systemctl start mariadb.service command is used when the cluster has not been initialized, the service start will fail, indicating in the logs that it is unable to connect to other nodes in the cluster.

Once the cluster has been initialized using the galera_new_cluster command, it is possible to (re)start the MariaDB service on the other servers:

systemctl start mariadb.service

The cluster will then be up.

HAProxy

When the MariaDB cluster is up and running, it is necessary, on each of the front-ends, to set up an HAProxy service. This service will make it possible to distribute the requests on one of the database servers.

All of the actions presented below have to be done exclusively on the front-end.

Installation

The HaProxy package is present on Debian repositories:

apt-get install haproxy --no-install-recommends

Configuration

The haproxy.cfg configuration file to modify is located in the /etc/haproxy/ directory. Add the following lines:

listen galera
    bind 127.0.0.1:3306
    roundrobin balance

    tcp mode
    tcpka option
    mysql-check user haproxy option

    # To be duplicated by the number of servers that can be requested
    server galera-1 192.168.0.253:3306 check weight 1
    server galera-2 192.168.0.252:3306 check weight 1
    server galera-3 192.168.0.251:3306 check weight 1

The bind directive must listen on the local interface. It is strongly discouraged to listen to HAProxy on the public interface !

The balance directive can be changed to:

  • leastconn: connects to the server with the least connection. If all the servers have the same load, the roundrobin mode will be used.
  • roundrobin: the database servers will be queried in turn.

As the configuration file indicates, a user will be used by the haproxy service to verify that the MariaDB Galera cluster members are available. The haproxy user must be present on the Galera cluster. Type the following commands on one of the nodes of the cluster:

CREATE USER 'haproxy'@'%';
FLUSH PRIVILEGES;

Then restart HAProxy to apply the new configuration and thus be able to use the MariaDB Galera cluster:

systemctl restart haproxy.service

The log file is located in /var/log/haproxy.log. The logs contain changes in the state of the database servers present in the HAProxy configuration.

To test the connection on the web-1 web server:

mysql -u <user> -p -h 127.0.0.1

Site configuration

The configuration of the websites will now be done using the address 127.0.0.1.

⚠️ **GitHub.com Fallback** ⚠️