Learn how to - Install MariaDB / MySQL Database on Rocky Linux 9|AlmaLinux 9

Felix Williams AlmaLinux How to mariadb Rocky Linux May 28, 2023

Learn how to – Install MariaDB / MySQL Database on Rocky Linux 9|AlmaLinux 9.

Databases are considered one of the important services in a production environment. They are used to store and organize data. Depending on how data is stored and retrieved, databases can be categorized as:

Relational databases (RDBMS or SQL databases): where data is stored in tables and rows, the tables are then linked using keys. They include MySQL, MariaDB, PostgreSQL, SQLite e.t.c
Non-relational databases(NoSQL databases): here data is stored using a storage model optimized for specific requirements. They include Couchbase, Apache Cassandra, MongoDB, Redis, Apache HBase e.t.c

MySQL is a popular open-source developed and distributed by the Oracle Corporation. It was founded in 1995 in Sweden and went open-source in 2000. In the year 2002, another MySQL headquarters was created in USA. In 2003, MyQSL entered a partnership with SAP and many features were added. In 2008, MySQL was acquired by Sun Microsystems. In 2009, Sun Microsystems was acquired by Oracle making MySQL an Oracle project.

MariaDB is a free and open-source relational database fork of the MySQL database. It was developed by the original developers of the MySQL database after the Oracle Corporation intended to make MySQL and enterprise/paid version in 2010. MariaDB is known for its performance, stability, openness, and the MariaDB Foundation which accepts contributions on technical merit. The latest improvements are advanced clustering with Galera Cluster 4 and compatibility features with the Oracle Database.

This guide will provide all the required steps on how to install and configure MariaDB / MySQL Database on Rocky Linux 9 / Alma Linux 9.

#1. Install MariaDB / MySQL on Rocky Linux 9|AlmaLinux 9

Rocky Linux 9 and Alma Linux 9 released recently, provide MySQL 8 and MariaDB 10.5 in their default repositories. This makes it easy to install these latest release versions on your system. Install a preferred database on your system using the below steps:

Option 1. Install MariaDB on Rocky Linux 9 / AlmaLinux 9

MariaDB 10.5 though not the latest release version of MariaDB can be installed easily by running the command:

sudo dnf install mariadb-server mariadb

Dependency Tree:

Transaction Summary
================================================================================
Install  13 Packages

Total download size: 18 M
Installed size: 107 M
Is this ok [y/N]: y

Once installed, start and enable MariaDB the service:

sudo systemctl enable --now mariadb

Verify if the service is running:

$ systemctl status mariadb
● mariadb.service - MariaDB 10.5 database server
     Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
     Active: active (running) since Sat 2022-07-23 10:05:47 CEST; 2s ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 18153 ExecStartPre=/usr/libexec/mariadb-check-socket (code=exited, status=0/SUCCESS)
    Process: 18175 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir mariadb.service (code=exited, status=0/SUCCESS)
    Process: 20589 ExecStartPost=/usr/libexec/mariadb-check-upgrade (code=exited, status=0/SUCCESS)
   Main PID: 20448 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 12 (limit: 23441)
     Memory: 78.5M
        CPU: 268ms
     CGroup: /system.slice/mariadb.service
             └─20448 /usr/libexec/mariadbd --basedir=/usr

Option 2. Install MySQL Database on Rocky Linux 9 / AlmaLinux 9

MySQL 8.0 released on May 11, 2021, offers a lot of amazing features that include:

Transactional data dictionary.
Common Table Expressions.
Windows Functions.
InnoDB & XML enhancements.
Error log improvements such as error numbering & reduced verbosity.
Improved support for Native JSON data and document store functionality.

This version is provided in the default Rocky Linux 9 / AlmaLinux 9 repositories. It can be installed by issuing the command:

sudo dnf install mysql-server

Dependency Tree:

Transaction Summary
================================================================================
Install  8 Packages

Total download size: 20 M
Installed size: 176 M
Is this ok [y/N]: y

Once installed, start and enable the MySQL service:

sudo systemctl start mysqld 
sudo systemctl enable mysqld

Check if the service is running on your system.

$ systemctl status mysqld 
● mysqld.service - MySQL 8.0 database server
     Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
     Active: active (running) since Sat 2022-07-23 10:08:59 CEST; 8s ago
   Main PID: 31834 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 23441)
     Memory: 460.9M
        CPU: 2.865s
     CGroup: /system.slice/mysqld.service
             └─31834 /usr/libexec/mysqld --basedir=/usr

#2. Using MariaDB / MySQL on Rocky Linux 9|AlmaLinux 9

Once installed MariaDB/MySQL databases can be used. First, secure the installation by setting a password for the root user.

sudo mysql_secure_installation

Proceed as shown:

On MySQL

.....
Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: Y
....
There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
....
Please set the password for root here.

New password: 
Re-enter new password: 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
......

On MariaDB:

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): Press_Enter
OK, successfully used password, moving on...

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] y

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

Change the root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
.....
Remove anonymous users? [Y/n] y
....
Disallow root login remotely? [Y/n] y
...
Remove test database and access to it? [Y/n] y
....
Reload privilege tables now? [Y/n] y
All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

Now login using the created root password:

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 10
Server version: 8.0.28 Source distribution

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>

Check the version using the command:

mysql>  SELECT VERSION();

Sample Output:

Install MariaDB / MySQL Database on Rocky Linux 9|AlmaLinux 9 1

Create a User and Database:

To create a user and database in MariaDB/MySQL, use the commands below replacing where required:

CREATE DATABASE sampledb;
CREATE USER 'test_user'@'%' IDENTIFIED BY 'Passw0rd';
GRANT ALL ON sampledb.* TO 'test_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

After this, you will have a database named sampledb and a user test_user who can be accessed remotely created as swell.

Check the available databases:

mysql> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sampledb           |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>

View users:

mysql> SELECT User, Host FROM mysql.user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| test_user        | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

mysql>

To delete a user, use the command with the below syntax:

DROP USER 'username'@'host';

Create Tables

We can then create and insert data into a table. For example in the created table above:

USE sampledb;

CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);

Insert the data into the preferred table:

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

View the added data:

mysql> SELECT * FROM playground;
+----------+-------+--------+-----------+--------------+
| equip_id | type  | color  | location  | install_date |
+----------+-------+--------+-----------+--------------+
|        1 | slide | blue   | south     | 2017-04-28   |
|        2 | swing | yellow | northwest | 2018-08-16   |
+----------+-------+--------+-----------+--------------+
2 rows in set (0.00 sec)

mysql> exit

#3. Configure MariaDB / MySQL Database

Once installed, you can customize your MariaDB / MySQL Database as preferred. There are several configurations you can make by editing the configuration file stored at /etc/my.cnf.d/*.

First stop the service:

##MariaDB
sudo systemctl stop mariadb

##MySQL
sudo systemctl stop mysqld

Now proceed and make the desired configurations by editing files below:

##For MySQL
sudo vim /etc/my.cnf.d/mysql-server.cnf

##For MariaDB
sudo vim /etc/my.cnf.d/mariadb-server.cnf

Changing the MySQL/MariaDB Listening Address

By default, both MySQL and MariaDB listen on localhost, but if you want to access the database remotely, you need to allow it to listen to all IP addresses by adding/uncommenting the line:

[mysqld]
bind-address=0.0.0.0

Change the MySQL/MariaDB data directory

By default, MariaDB and MySQL store data at /var/lib/mysql/. You can configure the database to store its data in a custom path. For example at /mnt/datastore

sudo mkdir /mnt/datastore
sudo chmod -R 777 /mnt/datastore/
sudo chown -R mysql:mysql /mnt/datastore

With the custom path created, you need to copy the files in the old directory to the new path using the command:

sudo dnf install rsync -y
sudo rsync -av /var/lib/mysql/* /mnt/datastore

For the path to be accessible, configure SELinux as shown:

sudo semanage fcontext -a -t mysqld_db_t '/mnt/datastore(/.*)?'
sudo restorecon -Rv /mnt/datastore

Now open the configuration file and add the data directory as shown:

For MySQL

[mysqld]
datadir= /mnt/mysql
socket=  /mnt/mysql/mysql.sock
mysqlx_socket= /mnt/mysql/mysqlx.sock

[client]
socket=  /mnt/mysql/mysql.sock

For MariaDB you need to edit two files to accommodate the data directory:

sudo vim /etc/my.cnf.d/mariadb-server.cnf

Make the below changes to the file:

[mysqld]
datadir=  /mnt/mysql
socket=  /mnt/mysql/mysql.sock
mysqlx_socket=  /mnt/mysql/mysqlx.sock

Save the file and also edit the below file for the clients on MariaDB.

sudo vim /etc/my.cnf.d/client.cnf

Add the custom port and socket:

[client]
socket= /mnt/mysql/mysql.sock
# This group is not read by mysql client library,
# If you use the same .cnf file for MySQL and MariaDB,
# use it for MariaDB-only client options
[client-mariadb]
socket= /mnt/mysql/mysql.sock

Configure a Custom Port

The default port used by MySQL/MariaDB is 3306. However, you can configure the database to use another port. For example 4545

Open the configuration file and make these settings for the server and client connections.

For MySQL

[mysqld]
port=4545

[client]
port=4545

For MariaDB, you need to edit two files:

$ sudo vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
port=4545

Edit the client port.

$ sudo vim /etc/my.cnf.d/client.cnf
[client]
port=4545

[client-mariadb]
port=4545

Configure SELinux for the custom port:

sudo semanage port -a -t mysqld_port_t -p tcp 4545

Restart the Services

After the changes have been made, start the MySQL/MariaDB service:

##MariaDB
sudo systemctl start mariadb

##MySQL
sudo systemctl start mysqld

Verify the changes made:

$ sudo ss -plunt|grep 4545
tcp   LISTEN 0      151          0.0.0.0:4545       0.0.0.0:*    users:(("mysqld",pid=33606,fd=24))

Allow the set port through the firewall:

sudo firewall-cmd --add-port=<port>/tcp --permanent
sudo firewall-cmd --reload

Remember to replace the port appropriately.

Access MySQL/MariaDB Remotely

Once the listen address and port have been configured, you can access the database remotely from a MySQL client. For example, we will try accessing the database using the created user from the remote host:

mysql -h <IP_Address> -u <user> -p -P <port>

For example, using the default MySQL port to 3306, the command will be:

mysql -h 192.168.205.13 -u test_user -p -P 3306

Sample Output:

Install MariaDB / MySQL Database on Rocky Linux 9|AlmaLinux 9 2

After configuring a custom port, we will have the command as shown.

Install MariaDB / MySQL Database on Rocky Linux 9|AlmaLinux 9 3

MariaDB/MySQL Performance Tuning and Optimization

When dealing with a large environment, the MariaDB / MySQL Database can have performance problems. To improve database performances, you may be required to tune the options. SQL performance tuning can be defined as the process of maximizing query speeds on a relational database.

Before that, you need to evaluate a few resources:

Storage: if you are using a traditional hard disk, it is recommended that you upgrade to a solid-state drive (SSD) for improved performance. You can use tools like iotop or sar from the sysstatpackage to check the disk input/output rates
Processor: The top command can be used to measure how fast your system is. You may be required to check the MySQL processes and the percentage processor use.
Memory: it is possible to improve the memory cache of MySQL to improve performance. This however requires you to have enough memory on your system
Network: The network can also play part in low performances. You need to ensure that the network is sufficient to manage the load.

The SQL performance tuning might involve several techniques such as:

Writing more efficient database queries
Structuring the database to retrieve data more efficiently.
Tweaking the MySQL configuration files.

Tweaking MySQL configuration files involves editing the /etc/mysql/my.cnf.d/* files. There are several configs defined here:

query_cache_size: this is the size of the cache of MySQL queries waiting to run. It is recommended that the values be small, around 100-200MB
max_connection: the number of connections allowed into the database
innodb_buffer_pool_size: this config is used to allocate system memory as a data cache for your database
innodb_io_capacity: It is used to set the rate for input/output from your storage device. This directly relates to the type and speed of your storage drive.
max_heap_table_size: is the limit on an ENGINE=MEMORY table you create
LEAST(max_heap_table_size, tmp_table_size): is the cap on how big to let certain implicit temp tables to get

These values can be defined as desired. For example:

[mysqld]
tmp_table_size=64M
max_heap_table_size=64M

After making the changes, restart the service:

##MariaDB
sudo systemctl restart mariadb

##MySQL
sudo systemctl restart mysqld

That is it!

We have successfully installed and configured MariaDB / MySQL Database on Rocky Linux 9 / Alma Linux 9. I hope this was informative.

See more:

Cart

Learn how to – Install MariaDB / MySQL Database on Rocky Linux 9|AlmaLinux 9