Skip to content

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

Alt text

ifdown ens192
ifup ens192

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

nano /etc/hosts

Alt text

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

Alt text

Stop Mariadb services on both nodes

Alt text

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)

Alt text

Alt text

Make bootstrap of new cluster (on db1)

sudo galera_new_cluster

Alt text

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

sudo systemctl start mariadb.service

Verify that the database status has been received

Alt text

2.Check GUI

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

db1:

Alt text

db2:

Alt text

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