MariaDB

This document describes the process of installing MariaDB on a control node and synchronizing at the database layer. MariaDB is used by Logpresso Sonar to store data and configuration settings, and MariaDB Galera Cluster is required for control nodes A and B to synchronize data with each other.

Installing MariaDB packages: Install the MariaDB server packages.
Changing the data directory: Change the MariaDB data directory (optional).
Node A tasks: Start the MariaDB service, configure security settings, and set up Galera Cluster on node A.
Node B tasks: Join Galera Cluster and start the service on node B.
Verifying Galera Cluster operation: Verify that Galera Cluster is operating correctly.
Creating accounts and databases: Create a database and account for Logpresso Sonar.

Installing MariaDB packages

When internet access is available

Check the repository path for the MariaDB version and Linux distribution you want to install from the MariaDB official mirror server. For example, the repository path for the latest MariaDB 11.8.x for Red Hat Enterprise Linux 9 is http://mirror.mariadb.org/yum/11.8/rhel9-amd64.

Create the /etc/yum.repos.d/MariaDB.repo file based on the path you identified. You need wheel group account privileges to create the file.

[mariadb]
name = MariaDB
baseurl = http://mirror.mariadb.org/yum/11.8/rhel9-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Run the following command in any directory (e.g., $HOME) to install the MariaDB packages.
```
sudo dnf update && \
sudo dnf install -y MariaDB-server MariaDB-backup
```

When on an air-gapped network

In an air-gapped environment, you cannot access external repositories. You must download the required packages from an internet-connected environment and then transfer them to the air-gapped network for installation.

Complete steps 1 and 2 from the internet-connected environment instructions.
Run the following command in any directory (e.g., $HOME) to download the packages required for MariaDB installation.
```
sudo dnf update && \
sudo dnf install -y \
     --downloadonly \
     --downloaddir=$HOME \
     --resolve \
     MariaDB-server MariaDB-backup
```
- --downloadonly: Download only without installing
- --downloaddir: Specify the download path
- --resolve: Download dependency packages as well

Verify that the RPM files are properly prepared, then compress them all. The following is an example list of downloaded RPM files (your actual file list may differ from the example).

MariaDB-backup-11.8.6-1.el9.x86_64.rpm
MariaDB-client-11.8.6-1.el9.x86_64.rpm
MariaDB-common-11.8.6-1.el9.x86_64.rpm
MariaDB-server-11.8.6-1.el9.x86_64.rpm
MariaDB-shared-11.8.6-1.el9.x86_64.rpm
MariaDB-client-compat-11.8.6-1.el9.noarch.rpm
MariaDB-server-compat-11.8.6-1.el9.noarch.rpm
liburing-2.12-1.el9.x86_64.rpm
galera-4-26.4.24-1.el9.x86_64.rpm
socat-1.7.4.1-8.el9_6.1.x86_64.rpm

tar -czvf mariadb11_rpms.tar.gz *.rpm

Upload the compressed file to the working directory on the server where MariaDB will be installed, using a USB drive or an internal transfer network.

Now run the following command on the server where MariaDB will be installed to extract and install the packages.

# Run the command in the directory containing the mariadb11_rpms.tar.gz file
tar -xzvf mariadb11_rpms.tar.gz && sudo dnf localinstall -y *.rpm

Changing the data directory (optional)

The default data directory for MariaDB is /var/lib/mysql. If you want to store data on a separate partition, you must change the data directory before starting the service. This setting must be applied to both node A and node B.

Note

Logpresso Sonar stores tickets, audit logs, event metadata, configurations, and more in MariaDB. Since data continuously accumulates in a production environment, it is recommended to use a separate partition considering disk capacity.

Create the directory for data storage and set permissions.

sudo mkdir -p /data/mysql
sudo chown mysql:mysql /data/mysql
sudo chmod 750 /data/mysql

Open the /etc/my.cnf.d/server.cnf file and add the following to the [mysqld] section.
```
[mysqld]
datadir=/data/mysql
```

Node A tasks

Changing character encoding

Change the MariaDB character encoding to utf8. Open the /etc/my.cnf.d/server.cnf file and add the following to the [mysqld] section.

[mysqld]
character-set-server=utf8
skip-character-set-client-handshake

Starting the service

Start the MariaDB service and verify that it is running correctly.

sudo systemctl start mariadb && systemctl status mariadb

If it is running correctly, you will see output similar to the following.

● mariadb.service - MariaDB 11.8.3 database server
      Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; preset: disabled)
      Drop-In: /etc/systemd/system/mariadb.service.d
               └─migrated-from-my.cnf-settings.conf
      Active: active (running) since Wed 2025-06-18 14:44:00 KST; 5s ago
         Docs: man:mariadbd(8)
               https://mariadb.com/kb/en/library/systemd/

Security settings

Run the following command to proceed with the default security configuration. During command execution, you will be prompted with questions about settings to configure. Enter the appropriate answer for each question and press Enter.

sudo mariadb-secure-installation

1. Enter root password

Since no initial root password is set, press Enter at the first prompt.

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
    SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):

2. Choose whether to use unix_socket authentication

Choose whether to enable unix_socket authentication for the root account. If unix_socket authentication is enabled, all users belonging to the wheel group can log in to the database with root privileges after logging into the system.

If there are no unnecessary accounts in the wheel group, press Enter.
If there are other accounts in the wheel group, type n and press Enter. In this case, you must restrict MariaDB access through firewall policies.

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n]

3. Choose whether to change root password

When the following prompt appears, decide whether to change the root account password.

... (omitted) ...
Change the root password? [Y/n]

If you do not plan to set up control node redundancy, press Enter and proceed with the password setup.
If you plan to configure redundant control nodes, type n and press Enter. The root account password will be changed during the Galera Cluster configuration step.

4. Remove the anonymous account

Remove the automatically created anonymous account. Press Enter.

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]

5. Restrict root remote access

Restrict remote access for the root account. Press Enter.

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]

6. Remove the test database

Remove the automatically created test database. Press Enter.

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]

7. Apply changes

Apply the changes to the system. Press Enter.

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]

Galera Cluster configuration

MariaDB Galera Cluster is required to set up redundancy for Logpresso Sonar control nodes. If you are running both control and data operations on a single node, skip this section.

Account setup

Identify the IP address range for nodes A and B, then use the shell script below to set up the cluster synchronization account.

#!/usr/bin/env bash
set -euo pipefail

# Description
# - This script must be run locally with sudo mysql or root privileges.
# - Disables MariaDB history for the current session.
# - SST_HOST pattern examples: '10.0.0.%', '192.168.1.%'
# - Run this script identically on all Galera nodes.

# Disable MariaDB history
export MYSQL_HISTFILE=/dev/null

# Password input and confirmation function
read_password() {
  local prompt_msg=$1
  local password_var=$2
  local pass1 pass2

  while true; do
    read -s -p "${prompt_msg}: " pass1
    echo
    read -s -p "Re-enter ${prompt_msg}: " pass2
    echo

    if [[ "$pass1" == "$pass2" && -n "$pass1" ]]; then
      printf -v "$password_var" "%s" "$pass1"
      break
    else
      echo "Passwords do not match or are empty. Please try again."
    fi
  done
}

# Input passwords and host pattern
ROOT_PW=""
SST_PW=""

read_password "password to assign to the root account" ROOT_PW
read_password "password to assign to the sst account" SST_PW

# SST_HOST input validation
while true; do
    read -p "Enter the host pattern for the database network (e.g., '192.168.1.%'): " SST_HOST

    # 0. Remove single quotes (') and double quotes (") from input (to prevent user errors)
    SST_HOST="${SST_HOST//[\'\"]/}"

    # 1. Check if the value is empty
    if [[ -z "$SST_HOST" ]]; then
        echo "Error: A host pattern must be entered."
        continue
    fi

    # 2. Validate (check after quotes are removed)
    if [[ "$SST_HOST" =~ ^[0-9a-zA-Z\.\%\-_]+$ ]]; then
        break
    else
        echo "Error: Invalid host pattern. (Only IP, %, and domain formats are allowed)"
    fi
done

# Escape passwords for SQL injection prevention
# In SQL, single quotes (') must be written as ('') to be recognized as strings
ROOT_PW_SQL="${ROOT_PW//\'/\'\'}"
SST_PW_SQL="${SST_PW//\'/\'\'}"

# 5. Connect to MariaDB and execute configuration
# REPLICA MONITOR is available from MariaDB 10.5+. For older versions, use REPLICATION CLIENT
sudo mysql -u root <<SQL
-- Set local root password
ALTER USER 'root'@'localhost' IDENTIFIED BY '${ROOT_PW_SQL}';

-- Create SST user
CREATE USER IF NOT EXISTS 'sst'@'${SST_HOST}' IDENTIFIED BY '${SST_PW_SQL}';

-- Grant privileges (for MariaDB 10.5 and above)
GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR, REPLICA MONITOR ON *.* TO 'sst'@'${SST_HOST}';

FLUSH PRIVILEGES;
SQL

# Clear password information from memory
unset ROOT_PW SST_PW ROOT_PW_SQL SST_PW_SQL

echo "---"
echo "Complete: SST account ('sst'@'${SST_HOST}') setup is finished."

Note

This script is used to create the root account and sst account so that passwords are not recorded and exposed in the SQL history.

Galera Cluster settings

To configure Galera Cluster on the server running node A, follow these steps.

Stop MariaDB and check its status.

sudo systemctl stop mariadb && systemctl status mariadb

Verify the path to the libgalera_smm.so file.
```
sudo find / -name libgalera_smm.so
```
The following is an example of the identified file path.
```
/usr/lib64/galera-4/libgalera_smm.so
```
Change the permissions of the /etc/my.cnf.d/server.cnf file to 600.
```
sudo chmod 600 /etc/my.cnf.d/server.cnf
```

Open the /etc/my.cnf.d/server.cnf file and configure the [galera] section as follows.

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_node_address=NODE_A_ADDR
wsrep_node_name=c1
wsrep_cluster_address="gcomm://NODE_B_ADDR,NODE_A_ADDR"
wsrep_cluster_name=galera
wsrep_sst_auth=sst:SST_PW
wsrep_sst_method=mariabackup
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_provider_options="pc.bootstrap=YES;pc.weight=2;pc.recovery=TRUE"

NODE_A_ADDR: IP address of node A. This must be an address within the SST_HOST network range entered during the Galera Cluster account setup.
NODE_B_ADDR: IP address of node B. This must be an address within the SST_HOST network range entered during the Galera Cluster account setup.
SST_PW: Enter the SST_PW value that was entered during the Galera Cluster account setup.

The descriptions for each option are as follows.

Option	Description	Notes
wsrep_on	Galera Cluster operation (ON, OFF)
wsrep_provider	Galera library path	Path to the `libgalera_smm.so` file
wsrep_node_address	Address of the current server
wsrep_node_name	Node identifier	A unique name not duplicated with other nodes. Hostname recommended
wsrep_cluster_address	Addresses of other nodes in the cluster	Enter in order of node B, then node A
wsrep_cluster_name	Cluster name	Enter the same name on both node A and node B
wsrep_sst_auth	Galera Cluster synchronization account
wsrep_sst_method	Data synchronization method when a new node joins	Uses mariabackup
binlog_format	Binary log format	Must be row for Galera
default_storage_engine	MariaDB storage engine	Uses InnoDB
innodb_autoinc_lock_mode	InnoDB autoincrement value management mode	0: traditional, 1: consecutive, 2: interleaved
wsrep_provider_options	Galera Cluster internal communication and replication settings

Starting Galera Cluster

Run the following command to start MariaDB Galera Cluster and check the wsrep_start_position value.

sudo galera_new_cluster && \
ps -ef | grep mysql

Running this command will produce output similar to the following.

mysql    3785680       1  2 10:46 ?        00:00:00 /usr/sbin/mariadbd --wsrep-new-cluster --wsrep_start_position=c6609d0e-091a-11f0-86bb-3e7cc9ee21e7:1

Caution

The galera_new_cluster command must only be run on the first node to start in the cluster. Running the galera_new_cluster command on other nodes will cause the cluster to malfunction.

Note

If the wsrep_start_position value is "00000000-0000-0000-0000-000000000000", stop MariaDB with the systemctl stop mariadb command and then run the galera_new_cluster command again.

Node B tasks

If you are running both control and data operations on a single node, skip this section.

Changing character encoding

Change the MariaDB character encoding to utf8 on the server running node B. Open the /etc/my.cnf.d/server.cnf file and add the following to the [mysqld] section.

[mysqld]
character-set-server=utf8
skip-character-set-client-handshake

Galera Cluster configuration

To configure Galera Cluster on the server running node B, follow these steps. When node B joins the Galera Cluster, it replicates node A's database through SST (State Snapshot Transfer). Therefore, the root account and sst account configured on node A are automatically synchronized, and no separate account setup is required.

Verify the path to the libgalera_smm.so file.
```
sudo find / -name libgalera_smm.so
```
The following is an example of the identified file path.
```
/usr/lib64/galera-4/libgalera_smm.so
```

Open the /etc/my.cnf.d/server.cnf file and configure the [galera] section as follows.

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_node_address=NODE_B_ADDR
wsrep_node_name=c2
wsrep_cluster_address="gcomm://NODE_A_ADDR,NODE_B_ADDR"
wsrep_cluster_name=galera
wsrep_sst_auth=sst:SST_PW
wsrep_sst_method=mariabackup
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_provider_options="pc.weight=1;pc.recovery=TRUE"

Enter the following values to match what was entered in the script during the account setup step.
- NODE_A_ADDR: IP address of node A within the SST_HOST network range
- NODE_B_ADDR: IP address of node B within the SST_HOST network range
- SST_PW: The value entered for SST_PW

Starting the service

Run the following command to start MariaDB and verify that the wsrep_start_position has the same value as node A.
```
sudo systemctl start mariadb && \
ps -ef | grep mysql
```
Running this command will produce output similar to the following.
```
mysql    1141195       1  1 Apr15 ?        22:50:53 /usr/sbin/mariadbd --wsrep_start_position=c6609d0e-091a-11f0-86bb-3e7cc9ee21e7:40512
```
Caution

Node B is joining an already started Galera Cluster, so you must not run the galera_new_cluster command. The galera_new_cluster command must only be run on the first node to start in the cluster. Running the galera_new_cluster command on other nodes will cause the cluster to malfunction.

Note

If the wsrep_start_position value is "00000000-0000-0000-0000-000000000000", run the systemctl restart mariadb command.

Verifying Galera Cluster operation

This task pertains to the redundancy configuration of control nodes. If you are running both control and data operations on a single node, Galera Cluster is not used, so skip this section.

If Galera Cluster is operating correctly, you should be able to log in to MariaDB from node B using the database account configured on node A.

Log in to MariaDB with the database root account on both node A and node B.
```
mysql -u root -p
```
Running the command will display a prompt like the one below. Enter the root account password that was configured on node A and press Enter.
```
Enter password:
```
Run the following query in the MariaDB shell on both node A and node B to check the Galera Cluster operation status.
```
show status like 'wsrep_%';
```
Running this command will produce output similar to the following.
```
... (omitted) ...
wsrep_local_state_comment : Synced
wsrep_cluster_size : 2
wsrep_connected : ON
wsrep_ready : ON
... (omitted) ...
```

Creating accounts and databases

To create the database and account required for the Logpresso Sonar installation, follow these steps. Here, we disable the history to prevent the new account password from being recorded in the SQL history, and then launch the MariaDB shell.

Run the following command on one of the control nodes A or B to disable the SQL history and launch the MariaDB shell.
```
export MYSQL_HISTFILE=/dev/null && mysql -u root -p
```
Running the command will display a prompt like the one below. Enter the password and press Enter.
```
Enter password:
```

Run the following SQL statements to create the sonar database and sonar account. In these SQL statements, you must replace SONAR_PASSWORD with a sufficiently strong password.

CREATE DATABASE IF NOT EXISTS `sonar` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci */;

CREATE USER IF NOT EXISTS 'sonar'@'localhost' IDENTIFIED BY 'SONAR_PASSWORD';

GRANT
SELECT, EXECUTE, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE,
CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT,
REFERENCES, TRIGGER, UPDATE, LOCK TABLES
ON `sonar`.*
TO 'sonar'@'localhost' WITH GRANT OPTION;

EXIT

Note

The database and account names can be changed to suit your operating environment.

Use the following command to restore the MYSQL_HISTFILE environment variable to its original state.
```
unset MYSQL_HISTFILE
```

Connect to the MariaDB shell on the other control node and run the following command to verify that the sonar database was automatically created.

SHOW DATABASES;

Running this command will produce output similar to the following.

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sonar              |
+--------------------+
4 rows in set (0.001 sec)