How to create a MariaDB cluster for CYBERQUEST operation

Introduction

CYBERQUEST use MariaDB database for solution configuration data.

For architectures High Availability MariaDB nodes are configured in cluster.

The purpose of this document is to technically describe the MariaDB cluster configuration process.

MariaDB Galera Cluster is used to create the cluster.

Configuring the MariaDB cluster

1.Configuring the network interface

Make sure there are fixed ips on all nodes

ifdown ens192

ifup ens192

Add in /etc/hosts node records (on all nodes, in this case db1 and db2)

nano /etc/hosts

2.Configuring the 60-galera.conf file

The 60-galera.cnf clustering configuration file is specific to each node and is created manually - if not found in /etc/mysql/mariadb.conf.d/

Configuration file for 192.168.200.139 (db1)

# * 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 = 
#wsrep_cluster_address = 
#binlog_format = row 
#default_storage_engine = InnoDB 
#innodb_autoinc_lock_mode = 2 

# 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 


[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/lib/galera/libgalera_smm.so 

# Galera Cluster Configuration 
wsrep_cluster_name="CQ_CLUSTER" 
wsrep_cluster_address="gcomm://192.168.200.139,192.168.200.140" 

# Galera Synchronization Configuration 
wsrep_sst_method=rsync 

# Galera Node Configuration 
wsrep_node_address="192.168.200.139" 
wsrep_node_name="db1"

Configuration file for 192.168.200.140 (db2)

# * 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 = 
#wsrep_cluster_address = 
#binlog_format = row 
#default_storage_engine = InnoDB 
#innodb_autoinc_lock_mode = 2 

# 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 


[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/lib/galera/libgalera_smm.so 

# Galera Cluster Configuration 
wsrep_cluster_name="CQ_CLUSTER" 
wsrep_cluster_address="gcomm://192.168.200.139,192.168.200.140" 

# Galera Synchronization Configuration 
wsrep_sst_method=rsync 

# Galera Node Configuration 
wsrep_node_address="192.168.200.140" 
wsrep_node_name="db2"

3.Final configuration

Copy the configuration file to /home/superadmin on both nodes

Stop Mariadb services on both nodes

Copy the configuration file to each node separately

sudo cp /home/superadmin/60-galera.cnf /etc/mysql/mariadb.conf.d/ 

Cluster verification

1.Checking the configurations

Check the configuration in the file (on each node)

Make bootstrap of new cluster (on db1)

sudo galera_new_cluster

Starting the service on db2 (which is already configured):

sudo systemctl start mariadb.service

Verify that the database status has been received

2.Check GUI

Check the replication when accessing the web interface. The configurations must be identical for the two CYBERQUEST machines.

db1:

db2:

You can see the identical configuration of dashgroups: Events, Users, Asset Dashboards.