Install MariaDB 11.0 With phpMyAdmin on Rocky / AmaLinux

MariaDB is one of the popular relational database management systems (RDBMS). It was created as a fork of MySQL after its owners wanted to make it paid software. Over the years with continuous development, MariaDB users have the flexibility to select the version that best suits their needs and is in line with their business applications. There are several other reasons why MariaDB is preferred, some of which include:

• Open Source: This means it is freely available for use, modification, and distribution. Its open-source nature encourages community involvement, fosters innovation, and enables rapid development cycles. It also provides greater transparency, security, and flexibility compared to proprietary database systems.
• High Availability and Replication: It also includes robust features for ensuring high availability and data redundancy. It supports various replication methods, including master-slave replication and multi-source replication, enabling the creation of reliable and fault-tolerant database clusters. This ensures continuous availability of critical data and minimizes the risk of data loss.
• Active Community and Support: It has a large community of users, developers, and contributors. This community provides valuable support, regular updates, bug fixes, and feature enhancements. Users can access extensive documentation, forums, and resources, making it easier to resolve issues and stay up to date with the latest developments.
• Performance and Scalability: It has several performance enhancements and optimizations over its predecessors. It provides improved query optimization, faster data processing, and efficient use of system resources. Additionally, MariaDB offers scalability features such as parallel processing, multi-threading, and support for distributed computing, enabling it to handle large datasets and high workloads.
• Compatibility: It is designed to be highly compatible with MySQL. That is to say that, applications and systems developed for MySQL can seamlessly transition to MariaDB. This compatibility ensures a smooth migration path for businesses using MySQL and allows them to leverage the benefits of MariaDB without extensive modifications.

Currently, the latest release is MariaDB 11.0. However, this version is still RC and not recommended for Production environments.

This latest update brings significant enhancements to the Optimizer in MariaDB. One notable change is the removal of the InnoDB Change Buffer, which had a significant impact on performance. Additionally, several variables have been deprecated, indicating that they are no longer recommended for use. These deprecated variables include innodb_defragment, innodb_defragment_n_pages, innodb_defragment_stats_accuracy, innodb_defragment_fill_factor_n_recs, innodb_defragment_fill_factor, innodb_defragment_frequency, innodb_file_per_table, and innodb_flush_method.

Furthermore, the update also removes the following deprecated variables: innodb_change_buffer_max_size and innodb_change_buffering. These variables have been eliminated as they are no longer considered necessary or beneficial for optimal database operations.

These changes reflect the continuous effort to improve the performance and stability of MariaDB, ensuring that users have access to a streamlined and efficient database system. By deprecating and removing outdated features and variables, MariaDB remains focused on delivering an optimized and future-proof solution for managing relational databases.

PhpMyAdmin is a web-based application developed in PHP. This tool allows users to manage databases, specifically MariaDB and MySQL using an intuitive interface. This free and open-source tool allows users to perform various other database management tasks that include, executing queries, creating and modifying tables, importing and exporting data, managing user permissions, and much more, all through a user-friendly web interface.

Today we will learn how to install MariaDB 11.0 With phpMyAdmin on Rocky / AmaLinux.

Step 1: Install MariaDB 11.0 on Rocky / AmaLinux

To be able to install MariaDB 11.0 on Rocky / AmaLinux, you need to add the repositories to the system.

curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version=11.1

Once added, reset the AppStream MariaDB repository with the commands:

sudo dnf -qy module disable mariadb
sudo dnf module reset mariadb -y

Now install MariaDB 11 and the required dependencies:

sudo dnf install MariaDB-server MariaDB-client MariaDB-backup vim

Dependency Tree:

.....
Transaction Summary
==============================================================================================
Install  9 Packages

Total download size: 65 M
Installed size: 321 M
Is this ok [y/N]: y

Once complete, verify with the command:

$ mariadb -V
mariadb from 11.0.1-MariaDB, client 15.2 for Linux (x86_64) using readline 5.1

Start and enable the service:

sudo systemctl enable --now mariadb

Check if the service is running:

$ systemctl status mariadb
● mariadb.service - MariaDB 11.0.1 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 Sun 2023-05-21 05:14:12 EDT; 4s ago
     Docs: man:mariadbd(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 9808 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 9787 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_STA>
  Process: 9785 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 9797 (mariadbd)
   Status: "Taking your SQL requests now..."
    Tasks: 11 (limit: 23505)
   Memory: 197.5M
   CGroup: /system.slice/mariadb.service
           └─9797 /usr/sbin/mariadbd
...

Harden MariaDB 11.0

After the installation, you need to secure the instance.

sudo mariadb-secure-installation

Proceed as shown:

Enter current password for root (enter for none): Press Enter
OK, successfully used password, moving on...
.....
Switch to unix_socket authentication [Y/n] y
...
Change the root password? [Y/n] y
New password: Set root password
Re-enter new password: Re-enter the 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
 ... Success!
...
Thanks for using MariaDB!

Create a database for PhpMyAdmin

We need to create a database to be used by PhpMyAdmin. First, access the shell using the created root password:

mysql -u root -p

Now create the database, user and password with the command:

CREATE DATABASE phpmyadmin CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE USER 'phpmyadmin'@'%' IDENTIFIED BY 'Passw0rd';
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'%';
FLUSH PRIVILEGES;
exit

Step 2: Install PHP and Required Extensions

PhpMyAdmin requires PHP and several other modules to run. PHP can be installed on Rocky Linux/ AmaLinux using any of the below guides:

• How To Install PHP on Rocky Linux 8 / AlmaLinux 8

You can also install the default available version and the required dependencies with the command:

sudo dnf -y install php php-{cli,common,fpm,curl,gd,mbstring,process,snmp,xml,zip,memcached,mysqlnd,json,mbstring,pdo,pdo-dblib,xml}

Verify the installation:

$ php --version
PHP 8.2.6 (cli) (built: May  9 2023 06:25:31) (NTS gcc x86_64)
Copyright (c) The PHP Group
Zend Engine v4.2.6, Copyright (c) Zend Technologies
    with Zend OPcache v8.2.6, Copyright (c), by Zend Technologies

Configure your TimeZone:

$ sudo vim /etc/php.ini
date.timezone = Africa/Nairobi

You also need to edit PHP-FPM as shown:

sudo vim /etc/php-fpm.d/www.conf

Make the below changes:

user = nginx

; RPM: Keep a group allowed to write in log dir.
group = nginx

;listen = 127.0.0.1:9000
listen = /run/php-fpm/www.sock

listen.owner = nginx
listen.group = nginx
listen.mode = 0660

Now start and enable PHP-FPM:

sudo systemctl enable php-fpm
sudo systemctl restart php-fpm

Install Nginx:

sudo dnf install nginx -y

You also need to start and enable NGINX

sudo systemctl start nginx
sudo systemctl enable nginx

Step 3: Install PhpMyAdmin on Rocky / AmaLinux

PhpMyAdmin does not exist in the default Rocky / AmaLinux. To verify that, issue the command below:

$ dnf whatprovides phpmyadmin
Error: No Matches found

Now to install and use PhpMyAdmin, download the latest version from the phpMyAdmin downloads page. You can pull the latest version Wget:

wget https://www.phpmyadmin.net/downloads/phpMyAdmin-latest-all-languages.tar.gz

Create a directory for PhpMyAdmin

sudo mkdir /usr/share/nginx/phpmyadmin

Extract the file to the directory:

sudo tar xzf phpMyAdmin-latest-all-languages.tar.gz -C /usr/share/nginx/phpmyadmin --strip-components=1

Create a sample config:

sudo cp /usr/share/nginx/phpmyadmin/config{.sample,}.inc.php

Create a secret, you can use blowfish secret online, then add the secret to the file:

sudo vim /usr/share/nginx/phpmyadmin/config.inc.php

Make the below change:

$cfg['blowfish_secret'] = 'k[a9LgO=Yo:n1ayWfi:UcR=sDx;vceBl';

Set the required permissions for the file:

sudo chown -R nginx:nginx  /var/lib/php/session/
sudo chown -R nginx:nginx /usr/share/nginx/phpmyadmin

Configure the required SELinux contexts:

sudo yum -y install policycoreutils-python-utils
sudo semanage fcontext -a -t httpd_sys_rw_content_t "/usr/share/nginx/phpmyadmin(/.*)?"
sudo restorecon -Rv /usr/share/nginx/phpmyadmin

Step 4: Configure Nginx server for phpMyAdmin

To be able to access phpMyAdmin, we need to create a virtualhost file as shown:

sudo vim /etc/nginx/conf.d/phpmyadmin.conf

In the file add the lines below:

server {
    listen       80;
    server_name  phpmyadmin.geeksforgeeks.org;
    root         /usr/share/nginx/phpmyadmin;
    
    access_log /var/log/nginx/phpmyadmin_access.log;
    error_log /var/log/nginx/phpmyadmin_error.log;

    index   index.php;

    location / {
        try_files    $uri $uri/ /index.php?$args;
    }
    location ~ \.php$ {
         try_files $uri =404;
         fastcgi_intercept_errors on;
         include        fastcgi_params;
         fastcgi_param  SCRIPT_FILENAME  $document_root$fastcgi_script_name;
         fastcgi_pass unix:/run/php-fpm/www.sock;
     }
}

Check the syntax of the config:

$ sudo nginx -t
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful

Restart Nginx:

sudo systemctl restart nginx

Allow the service through the firewall:

sudo firewall-cmd --zone public --add-service http
sudo firewall-cmd --permanent --zone public --add-service http
sudo firewall-cmd --reload

Step 5: Access and Use phpMyAdmin

Now you can access phpMyAdmin via the browser using the URL http://domain_name

Log in using the created user and password or the root user for admin privileges on MariaDB earlier. Once authenticated, you will see this:

Now you can use phpMyAdmin to manage MariaDB 11.0. First, create a test database.

You can then create tables into it:

Recommended MySQL / MariaDB books:

• Best Books To Learn MySQL / MariaDB Databases

Verdict

That is the end of this guide on how to install MariaDB 11.0 With phpMyAdmin on Rocky / AmaLinux. phpMyAdmin provides an easy way to manage the MariaDB server. Here you do not need to memorize the SQL commands required for database administration. I hope this was significant.

• How To Install PHPMyAdmin on Kali Linux
• How To Install MariaDB 11.0 on Ubuntu
• Install Apache, MariaDB, PHP (LAMP) on Rocky Linux 9