How to install and Configure Mariadb 10 in Rocky Linux/Alma Linux 8

In this guide we will learn how to install and configure MariaDB in Rocky Linux/Alma Linux 8.

MariaDB is an open-source one of the most popular relational database management system (RDBMS) that is a highly compatible drop-in replacement of MySQL. It is built upon the values of performance, stability, and openness, and MariaDB Foundation ensures contributions will be accepted on technical merit.

MariaDB was developed as a software fork of MySQL in 2009 in response to Oracle’s acquisition of MySQL. MariaDB intends to remain free and open-source software under the GNU General Public License. It is part of most cloud offerings and the default in most Linux distributions.

Prerequisites

To follow along, ensure you have:

An up to date Rocky Linux server/workstation
Root access to the server or user with Sudo access
Access to the internet from the server

Table of content

Ensure that the system is up to date
Installing Mariadb in Rocky Linux 8
Starting and enabling mariadb service
Configuring Mariadb in Rocky Linux 8
Testing MariaDB

1. Ensuring that the system is up to date

Before proceeding let us ensure that our server is up to date. Use this command to ensure that the server is up to date

sudo dnf update -y

2. Installing mariadb in Rocky Linux 8

Mariadb can found in the default repos for Rocky Linux. Use this command to install the database server. You can confirm by searching with this command:

sudo dnf search mariadb-server

Install Mariadb using this command

sudo dnf install -y mariadb-server

Use this command to get info about the installed mariadb server

$ rpm -qi mariadb-server
Name        : mariadb-server
Epoch       : 3
Version     : 10.3.28
Release     : 1.module+el8.4.0+427+adf35707
Architecture: x86_64
Install Date: Tue 11 Jan 2022 05:58:19 AM UTC
Group       : Unspecified
Size        : 85968333
License     : GPLv2 with exceptions and LGPLv2 and BSD
Signature   : RSA/SHA256, Mon 31 May 2021 12:18:29 AM UTC, Key ID 15af5dac6d745a60
Source RPM  : mariadb-10.3.28-1.module+el8.4.0+427+adf35707.src.rpm
Build Date  : Sun 30 May 2021 11:56:08 PM UTC
Build Host  : ord1-prod-x86build003.svc.aws.rockylinux.org
Relocations : (not relocatable)
Packager    : infrastructure@rockylinux.org
Vendor      : Rocky
URL         : http://mariadb.org
Summary     : The MariaDB server and related files
Description :
MariaDB is a multi-user, multi-threaded SQL database server. It is a
client/server implementation consisting of a server daemon (mysqld)
and many different client programs and libraries. This package contains
the MariaDB server and some accompanying files and directories.
MariaDB is a community developed branch of MySQL.

3. Starting and enabling Mariadb

Mariadb will not be started by default. Use this command to start:

sudo systemctl start mariadb

Confirm that the service is up and running by checking its status

$ sudo systemctl status mariadb
● mariadb.service - MariaDB 10.3 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
   Active: active (running) since Tue 2022-01-11 05:58:54 UTC; 25s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 40325 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS)
  Process: 40191 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mariadb.service (code=exited, status=0/SUCCESS)
  Process: 40166 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
 Main PID: 40293 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 30 (limit: 23176)
   Memory: 83.4M
   CGroup: /system.slice/mariadb.service
           └─40293 /usr/libexec/mysqld --basedir=/usr

Jan 11 05:58:54 rockysrv.citizix.com mysql-prepare-db-dir[40191]: See the MariaDB Knowledgebase at http://mariadb.com/kb or the
Jan 11 05:58:54 rockysrv.citizix.com mysql-prepare-db-dir[40191]: MySQL manual for more instructions.
Jan 11 05:58:54 rockysrv.citizix.com mysql-prepare-db-dir[40191]: Please report any problems at http://mariadb.org/jira
Jan 11 05:58:54 rockysrv.citizix.com mysql-prepare-db-dir[40191]: The latest information about MariaDB is available at http://mariadb.org/.
Jan 11 05:58:54 rockysrv.citizix.com mysql-prepare-db-dir[40191]: You can find additional information about the MySQL part at:
Jan 11 05:58:54 rockysrv.citizix.com mysql-prepare-db-dir[40191]: http://dev.mysql.com
Jan 11 05:58:54 rockysrv.citizix.com mysql-prepare-db-dir[40191]: Consider joining MariaDB's strong and vibrant community:
Jan 11 05:58:54 rockysrv.citizix.com mysql-prepare-db-dir[40191]: https://mariadb.org/get-involved/
Jan 11 05:58:54 rockysrv.citizix.com mysqld[40293]: 2022-01-11  5:58:54 0 [Note] /usr/libexec/mysqld (mysqld 10.3.28-MariaDB) starting as process 40293 ...
Jan 11 05:58:54 rockysrv.citizix.com systemd[1]: Started MariaDB 10.3 database server.

Now that it is started, let us enable start on boot with this:

<meta charset="utf-8">sudo systemctl enable mariadb

3. Configuring MariaDB

For new MariaDB installations, the next step is to run the included security script. This script changes some of the less secure default options. We will use it to block remote root logins and to remove unused database users.

Run the security script:

sudo mysql_secure_installation

This will take you through a series of prompts where you can make some changes to your MariaDB installation’s security options. The first prompt will ask you to enter the current database root password. Since we have not set one up yet, press ENTER to indicate “none”.

The next prompt asks you whether you’d like to set up a database root password. Type N and then press ENTER. The root account for MariaDB is tied closely to automated system maintenance, so we should not change the configured authentication methods for that account. Doing so would make it possible for a package update to break the database system by removing access to the administrative account. Later, we will cover how to optionally set up an additional administrative account for password access if socket authentication is not appropriate for your use case.

From there, you can press Y and then ENTER to accept the defaults for all the subsequent questions. This will remove some anonymous users and the test database, disable remote root logins, and load these new rules so that MariaDB immediately respects the changes you have made.

This is my server’s output

$ sudo mysql_secure_installation

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
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

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

Set root password? [Y/n]
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!


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]
 ... Success!

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]
 ... Success!

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]
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

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

Reload privilege tables now? [Y/n]
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

5. Testing MariaDB

Now that mariadb is all set up and is running, we need to confirm that it can accept connections.

To test, connect to mariadb with root user – mysql -h 127.0.0.1 -u root -p

Output:

$ mysql -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.3.28-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Check mariadb version:

MariaDB [(none)]> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 10.3.28-MariaDB |
+-----------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

For an additional check, you can try connecting to the database using the mysqladmin tool, which is a client that lets you run administrative commands. For example, this command says to connect to MariaDB as root and return the version using the Unix socket:

sudo mysqladmin version

You should receive output similar to this:

~> sudo mysqladmin version
mysqladmin  Ver 9.1 Distrib 10.5.13-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version		10.5.13-MariaDB
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/run/mysql/mysql.sock
Uptime:			7 min 34 sec

Threads: 2  Questions: 23  Slow queries: 0  Opens: 21  Open tables: 14  Queries per second avg: 0.050

This means that MariaDB is up and running and that your user is able to authenticate successfully.

Conclusion

In this guide you installed MariaDB to act as an SQL server. During the installation process you also secured the server.