MariaDB

This document describes how to install MariaDB on a control node and synchronize at the database layer. Logpresso Sonar uses MariaDB to store data and configuration, and MariaDB Galera Cluster is required for control nodes A and B to synchronize data with each other.

Installing the MariaDB package: Installs the MariaDB server package.
Changing the data directory: Changes the MariaDB data directory (optional).
Work on node A: Starts the MariaDB service, applies security settings, and configures the Galera Cluster on node A.
Work on node B: Joins the Galera Cluster and starts the service on node B.
Verifying Galera Cluster operation: Verifies that the Galera Cluster works correctly.
Creating the account and database: Creates the database and account for Logpresso Sonar.

Installing the MariaDB package

When internet access is available

On the official MariaDB mirror server, identify the repository path that matches the MariaDB version and Linux distribution you want to install. For example, the repository path for the latest MariaDB 11.8.x version for Red Hat Enterprise Linux 9 is http://mirror.mariadb.org/yum/11.8/rhel9-amd64.
Based on the path you identified, create the /etc/yum.repos.d/MariaDB.repo file. Creating the file requires the privileges of an account in the wheel group.
```
[mariadb]
name = MariaDB
baseurl = http://mirror.mariadb.org/yum/11.8/rhel9-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
```
In an arbitrary directory (for example, $HOME), run the following command to install the MariaDB package.
```
sudo dnf update && \
sudo dnf install -y MariaDB-server MariaDB-backup
```

In an air-gapped environment

In an air-gapped environment you cannot access external repositories, so you must download the packages required for installation in an environment with internet access, then bring them into the air-gapped environment and install them.

In an environment with internet access, perform installation steps 1 and 2.
In an arbitrary directory (for example, $HOME), run the following command to download the packages required to install MariaDB.
```
sudo dnf update && \
sudo dnf install -y \
     --downloadonly \
     --downloaddir=$HOME \
     --resolve \
     MariaDB-server MariaDB-backup
```
- --downloadonly: Downloads only, without installing.
- --downloaddir: Specifies the save path.
- --resolve: Downloads dependency packages together.

After verifying that the RPM files are prepared correctly, compress them all. The following is an example list of downloaded RPM files (the 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 created archive file to the working directory on the server where you will install MariaDB, using a USB drive or an internal transfer network.

Now, on the server where you will install MariaDB, extract and install with the following command.

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

Changing the data directory (optional)

The default MariaDB data directory is /var/lib/mysql. To store data on a separate partition, you must change the data directory before starting the service. Apply this configuration to both node A and node B.

Note

Logpresso Sonar stores tickets, audit logs, event metadata, configuration, and more in MariaDB. Because data accumulates continuously in a production environment, we recommend using a separate partition, taking disk capacity into account.

Create the directory to store the data and set its 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 content to the [mysqld] section.
```
[mysqld]
datadir=/data/mysql
```

Work on node A

Changing the character encoding

Change the MariaDB character encoding to utf8. Open the /etc/my.cnf.d/server.cnf file and add the following content 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 works correctly.

sudo systemctl start mariadb && systemctl status mariadb

If it works correctly, you can see content like 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/

Applying security settings

Run the following command to perform the basic security configuration. During command execution, you are prompted with questions about the items to configure. Enter an appropriate answer to each question and press the Enter key.

sudo mariadb-secure-installation

1. Enter the root password

The initial root password is not set, so press the Enter key at the first question.

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 use unix_socket authentication for the root account. If you use unix_socket authentication, every user in the wheel group can log into the database with root privileges after logging in to the system.

If there are no unnecessary accounts in the wheel group, press the Enter key.
If there are other accounts in the wheel group, enter n and press the Enter key. In this case, you must restrict MariaDB connections through the firewall policy.

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 the root password

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

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

If you do not want to make the control node redundant, press the Enter key and proceed with setting the password.
To configure redundant control nodes, enter n and press the Enter key. You change the root account password during the Galera Cluster configuration step.

4. Remove the anonymous account

Remove the automatically created anonymous account. Press the Enter key.

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 remote root access

Restrict remote access for the root account. Press the Enter key.

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 the Enter key.

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 the changes

Apply the changes to the system. Press the Enter key.

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

Reload privilege tables now? [Y/n]

Configuring the Galera Cluster

To make the Logpresso Sonar control node redundant, you need MariaDB Galera Cluster. When you perform both analysis and collection on a single node, skip this part.

Setting up the account

Identify the IP address ranges of 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
# - Run this script locally with sudo mysql or root privileges.
# - It disables the MariaDB history for the current session only.
# - SST_HOST pattern examples: '10.0.0.%', '192.168.1.%'
# - Run it identically on all Galera nodes.

# Disable MariaDB history
export MYSQL_HISTFILE=/dev/null

# Function to read and confirm a password
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 "The passwords do not match or are empty. Enter them again."
    fi
  done
}

# Enter 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

# Validate the SST_HOST input
while true; do
    read -p "Enter the host pattern for the database network (for example, '192.168.1.%'): " SST_HOST
    
    # 0. First remove single quotes (') and double quotes (") from the input (to prevent user mistakes)
    SST_HOST="${SST_HOST//[\'\"]/}"

    # 1. Check whether the value is empty
    if [[ -z "$SST_HOST" ]]; then
        echo "Error: you must enter a host pattern."
        continue
    fi
    
    # 2. Validate (check after the quotes have been 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 the passwords to prevent SQL injection
# In SQL, a single quote (') must be written as ('') to be recognized as a string
ROOT_PW_SQL="${ROOT_PW//\'/\'\'}"
SST_PW_SQL="${SST_PW//\'/\'\'}"

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

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

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

FLUSH PRIVILEGES;
SQL

# Remove the password information from memory
unset ROOT_PW SST_PW ROOT_PW_SQL SST_PW_SQL

echo "---"
echo "Done: setup of the SST account ('sst'@'${SST_HOST}') is complete."

Note

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

Configuring the Galera Cluster

To configure the Galera Cluster on the server that will run node A, follow these steps.

Stop MariaDB and check its status.

sudo systemctl stop mariadb && systemctl status mariadb

Identify the path of 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: The IP address of node A. It must be an address that belongs to the SST_HOST network range you entered when setting up the Galera Cluster account.
NODE_B_ADDR: The IP address of node B. It must be an address that belongs to the SST_HOST network range you entered when setting up the Galera Cluster account.
SST_PW: Enter the SST_PW value you entered when setting up the Galera Cluster account.

The description of each option is as follows.

Option	Description	Note
wsrep_on	Whether Galera Cluster operates (ON, OFF)
wsrep_provider	Galera library path	Path of the `libgalera_smm.so` file
wsrep_node_address	Address of the current server
wsrep_node_name	Node identifier	A name that does not duplicate other nodes; the host name is recommended
wsrep_cluster_address	Addresses of the other nodes in the cluster	Enter in the order node B, node A
wsrep_cluster_name	Cluster name	Enter the same name on 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	row is required in Galera
default_storage_engine	MariaDB storage engine	Uses InnoDB
innodb_autoinc_lock_mode	How InnoDB manages autoincrement values	0: traditional, 1: consecutive, 2: interleaved
wsrep_provider_options	Galera cluster internal communication and replication settings

Starting the 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

When you run this command, you can see a result 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

Run the galera_new_cluster command only on the very first node started in the cluster. If you also run the galera_new_cluster command on other nodes, the cluster does not work correctly.

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.

Work on node B

When you perform both analysis and collection on a single node, skip this part.

Changing the character encoding

On the server that will run node A, change the MariaDB character encoding to utf8. Open the /etc/my.cnf.d/server.cnf file and add the following content to the [mysqld] section.

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

Configuring the Galera Cluster

To configure the Galera Cluster on the server that will run 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 the sst account configured on node A are synchronized automatically, so no separate account setup is required.

Identify the path of 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 items according to the values you entered in the script during the account setup step.
- NODE_A_ADDR: The IP address of node A that belongs to the SST_HOST network range.
- NODE_B_ADDR: The IP address of node B that belongs to the SST_HOST network range.
- SST_PW: The value you entered for SST_PW.

Starting the service

Run the following command to start MariaDB and verify that wsrep_start_position has the same value as on node A.
```
sudo systemctl start mariadb && \
ps -ef | grep mysql
```
When you run this command, you can see a result 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

Because node B joins a Galera Cluster that has already been started, you must not run the galera_new_cluster command. Run the galera_new_cluster command only on the very first node started in the cluster. If you also run the galera_new_cluster command on other nodes, the cluster does not work correctly.

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 is about the redundant configuration of control nodes. When you perform both analysis and collection on a single node, the Galera Cluster is not used, so skip this part.

If the Galera Cluster works correctly, you should be able to connect to MariaDB on node B with the database account you configured on node A.

On node A and node B, connect to MariaDB with the database root account.
```
mysql -u root -p
```
When you run the command, the following prompt appears. Enter the root account password you set on node A and press the Enter key.
```
Enter password:
```
In the MariaDB shell of node A and node B, run the following query to check the Galera Cluster operation status.
```
show status like 'wsrep_%';
```
When you run this command, you can see a result like the following.
```
... (content omitted) ...
wsrep_local_state_comment : Synced
wsrep_cluster_size : 2
wsrep_connected : ON
wsrep_ready : ON
... (content omitted) ...
```

Creating the account and database

To create the database and account required to install Logpresso Sonar, follow these steps. Here, you disable history so that the new account's password is not left in the SQL history, and then run the MariaDB shell.

On one of control nodes A and B, run the following command to disable SQL history and run the MariaDB shell.
```
export MYSQL_HISTFILE=/dev/null && mysql -u root -p
```
When you run the command, the following prompt appears. Enter the password and press the Enter key.
```
Enter password:
```

Run the following SQL statements to create the sonar database and the 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

You can change the database and account names to match your operating environment.

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

On the other control node, connect to the MariaDB shell and run the following command to verify that the sonar database was created automatically.

SHOW DATABASES;

When you run this command, you can see a result like the following.

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