[MariaDb]Setup galera cluster

What’s MariaDb Galera Cluster

According to the Mariadb web site: MariaDB Galera Cluster is a virtually synchronous multi-primary cluster for MariaDB. It is available on Linux only, and only supports the InnoDB storage engine (although there is experimental support for MyISAM and, from MariaDB 10.6, Aria. See the wsrep_replicate_myisam system variable, or, from MariaDB 10.6, the wsrep_mode system variable).

Simply said Mariadb will allow you to have a balance of load and a stability at the level of your database in environments of overexploitation.

Prerequisites

• Get Ubuntu server 20.04 LTS running ;
• Get 3 servers(mariadb1, mariadb2, mariadb3) run in Ubuntu server 20.04 LTS ;
• Config ip address server

mariadb1:

network:
 ethernets:
 enp0s3:
 addresses: [192.168.100.11/24]
 gateway4: 192.168.100.1
 nameservers:
 addresses: [8.8.8.8, 8.8.4.4]
 dhcp4: false
 dhcp6: false
 version: 2

mariadb2:

network:
 ethernets:
 enp0s3:
 addresses: [192.168.100.12/24]
 gateway4: 192.168.100.1
 nameservers:
 addresses: [8.8.8.8, 8.8.4.4]
 dhcp4: false
 dhcp6: false
 version: 2

mariadb3:

network:
network:
 ethernets:
 enp0s3:
 addresses: [192.168.100.13/24]
 gateway4: 192.168.100.1
 nameservers:
 addresses: [8.8.8.8, 8.8.4.4]
 dhcp4: false
 dhcp6: false
 version: 2

• Install mariaDb on All server apt install mariadb-server

1- Configure Galera Cluster

in your first node or server create galera.cnf in /etc/mysql/conf.d

[mysqld]
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/lib64/galera-4/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="mariadb_cluster"
wsrep_cluster_address="gcomm://192.168.100.11,192.168.100.12,192.168.100.13"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="192.168.100.11"
wsrep_node_name="mariadb1"

make same thing on node 2 and 3 like this:

server 2

[mysqld]
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/lib64/galera-4/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="mariadb_cluster"
wsrep_cluster_address="gcomm://192.168.100.11,192.168.100.12,192.168.100.13"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="192.168.100.12"
wsrep_node_name="mariadb2"

server 3

[mysqld]
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/lib64/galera-4/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="mariadb_cluster"
wsrep_cluster_address="gcomm://192.168.100.11,192.168.100.12,192.168.100.13"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="192.168.100.13"
wsrep_node_name="mariadb3"

2- Start Galera Cluster

stop mariadb in all server systemctl stop mariadb

go to the server 1 start Galera with this commande

galera_new_cluster

look at the number of nodes connected to the cluster

mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'" 

ouput

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+

On node 2 run mariadb systemctl start mariadb

look at the number of nodes connected to the cluster

mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

ouput

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+

On node 3 run mariadb systemctl start mariadb

look at the number of nodes connected to the cluster.

mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

ouput

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

Congratulations, you have just deployed a mariadb cluster.

reference:

Galera Cluster Recovery

Crash Recovery

https://www.digitalocean.com/community/tutorials/how-to-configure-a-galera-cluster-with-mariadb-on-centos-7-servers