How To Install MariaDB on RockyLinux 9

MariaDB is a popular, free and open-source database management system.

Create a MariaDB Repository File

To install the MariaDB server, you need to create a MariaDB repository configuration file mariadb.repo manually with this path /etc/yum.repos.d/, To create a MariaDB repository file, you can use the following command,

vi /etc/yum.repos.d/mariadb.repo

This command will create a new repository file, Once it is created, you need to add the following configuration in that file,

# MariaDB 10.11 RedHatEnterpriseLinux repository list - created 2023-10-30 14:19 UTC
# https://mariadb.org/download/
[mariadb]
name = MariaDB
# rpm.mariadb.org is a dynamic mirror if your preferred mirror goes offline. See https://mariadb.org/mirrorbits/ for details.
# baseurl = https://rpm.mariadb.org/10.11/rhel/$releasever/$basearch
baseurl = https://mirror.23m.com/mariadb/yum/10.11/rhel/$releasever/$basearch
module_hotfixes = 1
# gpgkey = https://rpm.mariadb.org/RPM-GPG-KEY-MariaDB
gpgkey = https://mirror.23m.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck = 1

Save and exit file.

Installing MariaDB Server

dnf install MariaDB-server MariaDB-client

You will be presented with a series of prompts. Here's what you'll be asked and how to respond:

Rocky Linux 8 - AppStream                       1.6 MB/s | 1.6 kB     00:00
Importing GPG key 0x6D745A60:
 Userid     : "Release Engineering <infrastructure@rockylinux.org>"
 Fingerprint: 7051 C470 A929 F454 CEBE 37B7 15AF 5DAC 6D74 5A60
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-rockyofficial
Is this ok [y/N]: y
Key imported successfully
MariaDB                                          18 kB/s |  15 kB     00:00
Importing GPG key 0x1BB943DB:
 Userid     : "MariaDB Package Signing Key <package-signing-key@mariadb.org>"
 Fingerprint: 1993 69E5 404B D5FC 7D2F E43B CBCB 082A 1BB9 43DB
 From       : https://mirror.23m.com/mariadb/yum/RPM-GPG-KEY-MariaDB
Is this ok [y/N]: y
Key imported successfully
Importing GPG key 0xC74CD1D8:
 Userid     : "MariaDB Signing Key <signing-key@mariadb.org>"
 Fingerprint: 177F 4010 FE56 CA33 3630 0305 F165 6F24 C74C D1D8
 From       : https://mirror.23m.com/mariadb/yum/RPM-GPG-KEY-MariaDB
Is this ok [y/N]: y
Key imported successfully

Once the installation is complete, you can verify the version of the MariaDB server by using the following command:

mysql -V

Output:

[root@vps ~]# mysql -V
mysql  Ver 15.1 Distrib 10.11.5-MariaDB, for Linux (x86_64) using readline 5.1
[root@vps ~]#

Now, enable MariaDB (to start automatically upon system boot), start the MariaDB and verify the status using the commands below.

systemctl enable mariadb

systemctl start mariadb

systemctl status mariadb

Output:

[root@vps etc]# systemctl status mariadb
● mariadb.service - MariaDB 10.11.5 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
       └─migrated-from-my.cnf-settings.conf
Active: active (running) since Mon 2023-10-30 14:56:47 UTC; 5s ago
 Docs: man:mariadbd(8)
       https://mariadb.com/kb/en/library/systemd/
Process: 12854 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCC>
Process: 12826 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/g>
Process: 12824 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCE>
Main PID: 12836 (mariadbd)
Status: "Taking your SQL requests now..."
Tasks: 15 (limit: 23238)
Memory: 83.5M
CGroup: /system.slice/mariadb.service
       └─12836 /usr/sbin/mariadbd

Secure your MariaDB server

You can secure your MariaDB installation by following these steps

Start the MariaDB shell:

mysql

Change the root user's password:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';

Replace 'your_new_password' with the password you want to set for the root user.

Remove anonymous users:

DELETE FROM mysql.user WHERE User='';

Disallow remote root login. This ensures that the root user can only log in from the localhost:

DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

Remove the test database:

DROP DATABASE IF EXISTS test;

Reload the privileges to apply the changes:

FLUSH PRIVILEGES;

Exit the MariaDB shell:

EXIT;

Now, log in to the MariaDB server.

To login to the MariaDB server, enter the following command with the password set previously,

mysql -u root -p

Output:

[root@vps ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.11.5-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)]>

Create new DataBase and User

Here, we'll look into creating new database, new database user and add the user to database. (eg: Here we'll cerate database called "crowncloud" and the new user called "ccuser1"),

First, log in as root user:

mysql -u root -p

CREATE DATABASE crowncloud;

CREATE user ccuser1;

GRANT ALL ON crowncloud.* TO ccuser1@localhost IDENTIFIED BY 'secretePasswordHere';

Output:

[root@vps ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.11.5-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)]> CREATE DATABASE crowncloud;
Query OK, 1 row affected (0.002 sec)

MariaDB [(none)]> CREATE user ccuser1;
Query OK, 0 rows affected (0.004 sec)

MariaDB [(none)]> GRANT ALL ON crowncloud.* TO ccuser1@localhost IDENTIFIED BY 'secretePasswordHere';
Query OK, 0 rows affected (0.004 sec)

Accessing the database

Now, we will access MariaDB with the newly created Database and User as shown below,

mysql -u ccuser1 -p'secretePasswordHere' crowncloud

show databases;

Output:

[root@vps ~]# mysql -u ccuser1 -p'secretePasswordHere' crowncloud
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.11.5-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 [crowncloud]> show databases;
+--------------------+
| Database           |
+--------------------+
| crowncloud         |
| information_schema |
+--------------------+
2 rows in set (0.002 sec)

MariaDB [crowncloud]>