Latest

Sunday, 2 October 2022

Setup MariaDB Database Server on Rocky Linux 9

Setup MariaDB Database Server on Rocky Linux 9

In this configuration guide, you will learn how to setup a MariaDB database server on Rocky Linux 9 and install phpMyAdmin for convenient MySQL database administration via GUI.

 

Table of Contents:

 

What is MariaDB?:

MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system (RDBMS), intended to remain free and open-source software under the GNU General Public License. Development is led by some of the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle Corporation in 2009.

MariaDB is intended to maintain high compatibility with MySQL, with library binary parity and exact matching with MySQL APIs and commands, allowing it in many cases to function as drop-in replacement for MySQL. However, new features are diverging. It includes new storage engines like Aria, ColumnStore, and MyRocks.

Its lead developer/CTO is Michael "Monty" Widenius, one of the founders of MySQL AB and the founder of Monty Program AB. On 16 January 2008, MySQL AB announced that it had agreed to be acquired by Sun Microsystems for approximately $1 billion. The acquisition completed on 26 February 2008. Sun was then bought the following year by Oracle Corporation. MariaDB is named after Widenius' younger daughter, Maria. (MySQL is named after his other daughter, My. (Source: Wikipedia)

Setup MariaDB Database Server on Rocky Linux 9

Environment Specification:

We are using a minimal Rocky Linux 9 virtual machine with following specifications.

  • CPU - 3.4 Ghz (2 cores)
  • Memory - 2 GB
  • Storage - 20 GB
  • Operating System - Rocky Linux release 9.0 (Blue Onyx)
  • Hostname – mariadb-01.centlinux.com
  • IP Address - 192.168.116.131/24

We are using a Lenovo - 2022 - IdeaPad 3i - Essential Laptop Computer - Intel Core i5 12th Gen - 15.6" FHD Display - 8GB Memory - 512GB Storage. You can also use the following link to purchase the same at very reasonable price from Amazon.com.

Lenovo Idealpad 3i

 

Updating your Linux Operating System:

By using a ssh client, login to your Rocky Linux server as root user.

Set the hostname of your machine as follows.

# hostnamectl set-hostname mariadb-01.centlinux.com

Refresh your yum cache with the help of dnf command.

# dnf makecache
Rocky Linux 9 - BaseOS                          275 kB/s | 1.7 MB     00:06
Rocky Linux 9 - AppStream                       655 kB/s | 6.0 MB     00:09
Rocky Linux 9 - Extras                          2.3 kB/s | 6.6 kB     00:02
Metadata cache created.

Update all software packages in your Linux operating system.

# dnf update -y

The above command may update your Linux Kernel. If it does, then restart your Linux machine before moving forward with your server configurations.

# reboot

After reboot, confirm the Linux operating system and Kernel versions, that are being used in this tutorial.

# cat /etc/rocky-release
Rocky Linux release 9.0 (Blue Onyx)

# uname -r
5.14.0-70.22.1.el9_0.x86_64

 

Installing MariaDB Official Yum Repository:

In Rocky Linux 9.0, MariaDB Server 10.5 is available in standard yum repositories, and you can easily install it by using dnf command.

# dnf info mariadb-server
Last metadata expiration check: 0:07:36 ago on Thu 29 Sep 2022 11:39:20 AM CDT.
Available Packages
Name         : mariadb-server
Epoch        : 3
Version      : 10.5.16
Release      : 2.el9_0
Architecture : x86_64
Size         : 9.4 M
Source       : mariadb-10.5.16-2.el9_0.src.rpm
Repository   : appstream
Summary      : The MariaDB server and related files
URL          : http://mariadb.org
License      : GPLv2 and LGPLv2
Description  : MariaDB is a multi-user, multi-threaded SQL database server. It
             : is a client/server implementation consisting of a server daemon
             : (mariadbd) and many different client programs and libraries. This
             : package contains the MariaDB server and some accompanying files
             : and directories. MariaDB is a community developed fork from
             : MySQL.

However, we are emphasizing on installing a latest version of MariaDB database, that usually isn't available in standard yum repositories.

Open URL https://mariadb.org/download/?t=repo-config in a web browser. It is the official MariaDB website and you can download latest versions of the open source database for your preferred operating systems.

Additionally, the MariaDB download page also provides the yum repository configurations for the various Linux distributions.

Select your Linux distribution and required MariaDB server version to generate the yum repository configurations.

It is worth noting that, currently, version 10.11 (Alpha) and 10.10 (RC) are available to install, but we are focusing on a stable release i.e. MariaDB 10.9.

Copy the yum repo configurations and use it to create a repo file within /etc/yum.repos.d directory. You can use vim text editor for this purpose.

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

Paste the MariaDB repo configurations in this file.

# MariaDB 10.9 RedHat repository list - created 2022-09-29 16:48 UTC
# https://mariadb.org/download/
[mariadb]
name = MariaDB
baseurl = https://mirrors.gigenet.com/mariadb/yum/10.9/rhel9-amd64
gpgkey=https://mirrors.gigenet.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1

Save and Exit.

Build the yum cache for newly added yum repository.

# dnf makecache
MariaDB                                          40 kB/s | 402 kB     00:10
Rocky Linux 9 - BaseOS                          797  B/s | 3.6 kB     00:04
Rocky Linux 9 - AppStream                       1.7 kB/s | 3.6 kB     00:02
Rocky Linux 9 - Extras                          1.1 kB/s | 2.9 kB     00:02
Metadata cache created.

 

Installing MariaDB Database Server:

Now, you can install the latest version of MariaDB server by executing following command.

# dnf install -y MariaDB-server MariaDB-client

Enable and start MariaDB service.

# systemctl enable --now mariadb.service
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.

Check the status of MariaDB service.

# systemctl status mariadb.service
● mariadb.service - MariaDB 10.9.3 database server
     Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor p>
    Drop-In: /etc/systemd/system/mariadb.service.d
             └─migrated-from-my.cnf-settings.conf
     Active: active (running) since Thu 2022-09-29 15:16:11 CDT; 16s ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 901 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_STA>
    Process: 902 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && V>
    Process: 923 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_ST>
   Main PID: 910 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 13 (limit: 5740)
     Memory: 187.2M
        CPU: 4.197s
     CGroup: /system.slice/mariadb.service
             └─910 /usr/sbin/mariadbd

Sep 29 15:16:11 mariadb-01.centlinux.com mariadbd[910]: 2022-09-29 15:16:11 0 [>
Sep 29 15:16:11 mariadb-01.centlinux.com mariadbd[910]: 2022-09-29 15:16:11 0 [>

Configure initial settings of your MariaDB database server as follows.

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

Enter current password for root (enter for none):
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] n
 ... skipping.

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

Login to your database server by using mysql command and execute some SQL commands thereon.

# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.9.3-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)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.093 sec)

MariaDB [(none)]> exit
Bye

If you planning to access your database server over the network then you need to allow the service/port in Linux firewall, otherwise you can skip this step.

# firewall-cmd --permanent --add-service=mysql
success

# firewall-cmd --reload
success

Your MariaDB database software has been installed successfully.

 

Installing phpMyAdmin - A MySQL Administration Tool:

phpMyAdmin is the most popular and de facto preferred tool for MySQL database administration. Just like MariaDB, it is also free and open source. As a portable web application written primarily in PHP, it has become one of the most popular MySQL administration tools, especially for web hosting services.

phpMyAdmin is a PHP web application, therefore, you need a LAMP or LEMP server to deploy it.

Here, we are using Apache based PHP server (i.e. LAMP), however you can also install a Nginx based PHP server (i.e. LEMP) according to your requirements.

Execute following dnf command to install Apache, PHP, PHP modules and other required software packages in one go.

# dnf install -y httpd php php-common php-opcache php-cli php-gd php-curl php-mysqlnd php-xml wget policycoreutils-python-utils

Enable and start Apache and PHP services.

# systemctl enable --now httpd.service php-fpm.service
Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service.
Created symlink /etc/systemd/system/multi-user.target.wants/php-fpm.service → /usr/lib/systemd/system/php-fpm.service.

Allow the http service in Linux Firewall.

# firewall-cmd --permanent --add-service=http
success

# firewall-cmd --reload
success

phpMyAdmin is a free software, therefore you can download it from their official website.

Or you can copy the link and use it with wget command to download phpMyAdmin straight from Linux commandline. Just as we did below.

# cd /tmp
# wget https://files.phpmyadmin.net/phpMyAdmin/5.2.0/phpMyAdmin-5.2.0-english.tar.gz
--2022-09-29 15:40:01--  https://files.phpmyadmin.net/phpMyAdmin/5.2.0/phpMyAdmin-5.2.0-english.tar.gz
Resolving files.phpmyadmin.net (files.phpmyadmin.net)... 212.102.56.179, 195.181.170.18, 156.146.33.27, ...
Connecting to files.phpmyadmin.net (files.phpmyadmin.net)|212.102.56.179|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 7731513 (7.4M) [application/octet-stream]
Saving to: ‘phpMyAdmin-5.2.0-english.tar.gz’

phpMyAdmin-5.2.0-en 100%[===================>]   7.37M  1.14MB/s    in 6.7s

2022-09-29 15:40:09 (1.10 MB/s) - ‘phpMyAdmin-5.2.0-english.tar.gz’ saved [7731513/7731513]

Extract the downloaded file in Apache document root by using tar command.

# tar xf phpMyAdmin-5.2.0-english.tar.gz --directory /var/www/html

Rename the phpMyAdmin software directory for easy accessibility.

# mv /var/www/html/phpMyAdmin-5.2.0-english/ /var/www/html/pma

phpMyAdmin software provides a SQL script to create its meta data repository. You can easily execute it by using mysql command as follows.

# mysql -u root -p < /var/www/html/pma/sql/create_tables.sql
Enter password:

Create a copy of phpMyAdmin configuration file in the application root.

# cp /var/www/html/pma/config.sample.inc.php /var/www/html/pma/config.inc.php

Open config.inc.php file in vim text editor.

# vi /var/www/html/pma/config.inc.php

Locate and set following directives in this file.

$cfg['blowfish_secret'] = 'I;Am;Fan;0f;0p3n;S0urc3;S0ftwar3'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
$cfg['Servers'][$i]['host'] = '127.0.0.1';

Create a temporary directory to cache templates by the web application.

# mkdir /var/www/html/pma/tmp

Set apache user as the owner of pma directory.

# chown -R apache:apache /var/www/html/pma/

Enable following SELinux Boolean to allow Apache to connect with your MariaDB Database.

# setsebool -P httpd_can_network_connect 1

Set SELinux permissions for tmp directory, to make it writable by the apache user.

# semanage fcontext -a -t httpd_sys_rw_content_t "/var/www/html/pma/tmp(/.*)?"
# restorecon -Rv /var/www/html/pma/tmp
Relabeled /var/www/html/pma/tmp from unconfined_u:object_r:httpd_sys_content_t:s0 to unconfined_u:object_r:httpd_sys_rw_content_t:s0

Create a Admin user in MariaDB database. You can use this user to login via phpMyAdmin administration panel.

# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.9.3-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 USER ahmer@localhost IDENTIFIED BY 'pma';
Query OK, 0 rows affected (0.025 sec)

MariaDB [(none)]> GRANT ALL ON *.* TO ahmer@localhost;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> exit
Bye

Open URL http://mariadb-01.centlinux.com/pma/ in a web browser.

Login as user 'ahmer'.

You are now at the dashboard of phpMyAdmin administration panel.

To effectively user your MySQL administration tool, you should read Mastering phpMyAdmin 3.4 for Effective MySQL Management by Packt Publishing.

Conclusion:

In this configuration guide, you have successfully setup MariaDB database server on Rocky Linux 9 along with phpMyAdmin administration panel.

If you find this article useful? Don't you mind support us by Buy Me A Coffee


No comments:

Post a Comment

© 2022 CentLinux. All Rights Reserved.