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?
- Environment Specification
- Updating your Linux Operating System
- Installing MariaDB Official Yum Repository
- Installing MariaDB Database Server
- Installing phpMyAdmin - A MySQL Administration Tool
- Conclusion
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)
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
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.
No comments:
Post a Comment