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
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
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.
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();
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>
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';
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:
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
After configuring a custom port, we will have the command as shown.
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
sysstatpackage to check the disk input/output rates
- Processor: The
topcommand 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.