Share on Social Media

In this article, you will learn how to install MariaDB on CentOS 8 and configure admin users and database security. #centlinux #linux #mariadb

What is MariaDB Server?

MariaDB is a free and open source RDBMS (Relational Database Management System). MariaDB is a fork of MySQL database maintained by some of the original developers of MySQL project. Just like its predecessor, MariaDB is mostly used as a backend database for PHP based servers. MariaDB is also used by many FOSS (Free and Open Source Software) utilities as their data repository.

MariaDB Server is highly scalable and can be used as Stand Alone or MariaDB Galera Cluster environments.

MariaDB Server 10.5.3 is released on 12-May-2020 with many new features and some major changes. It is the latest release at the time of this writing.

New Features in MariaDB Server 10.5:

Some of the changes and features in MariaDB Server 10.5 are:

  • All binaries previously beginning with mysql now begin with mariadb, with symlinks for the corresponding mysql command
  • S3 Storage Engine – to archive MariaDB tables in Amazon S3, or any third-party public or private cloud that implements S3 API
  • Both S3 tables and partitioned S3 tables are discoverable. This means that if you create a partitioned S3 table, both the partitioned table and its partitions can be directly used by another server that has access to the S3 storage
  • New privileges were added by splitting the SUPER privilege, so that more fine grained tuning of what each user can do can be applied
  • Speed up binary row logging code
  • Improve connect speed (up to 25%)

You can see the complete list of new features in MariaDB 10.5.3 at MariaDB Official website.

MariaDB Alternatives

There are several alternatives to MariaDB, each offering unique features and capabilities for managing relational databases. Here are some notable alternatives:

MySQL

  • MySQL is MariaDB’s predecessor and remains a popular choice for relational database management.
  • Developed by Oracle Corporation, MySQL is known for its performance, reliability, and extensive community support.
  • It shares a similar syntax and functionality with MariaDB, making it easy for users familiar with one to work with the other.

PostgreSQL

  • PostgreSQL is an advanced open-source relational database system known for its robustness, extensibility, and SQL compliance.
  • It supports complex queries, indexing, and features such as JSON support, full-text search, and ACID compliance.
  • PostgreSQL is favored for applications requiring complex data structures, scalability, and strong data integrity.

SQLite

  • SQLite is a lightweight, serverless, self-contained, and cross-platform relational database management system.
  • Unlike MySQL and PostgreSQL, SQLite is embedded within the application and does not require a separate server process.
  • It is ideal for local storage, mobile applications, and situations where simplicity and ease of deployment are prioritized.

Oracle Database

  • Oracle Database is a commercial relational database management system developed by Oracle Corporation.
  • It is known for its scalability, high performance, advanced features, and enterprise-grade capabilities.
  • Oracle Database is widely used in large enterprises for mission-critical applications and extensive data processing.

SQL Server

  • SQL Server is a relational database management system developed by Microsoft.
  • It provides a robust set of features for data management, business intelligence, and analytics.
  • SQL Server is well-integrated with other Microsoft products and is popular in Windows-based environments.

MongoDB (NoSQL alternative)

  • MongoDB is a popular NoSQL database known for its flexible document-oriented data model.
  • It is designed for scalability, high availability, and ease of development with JSON-like documents.
  • MongoDB is suitable for applications requiring dynamic schemas and horizontal scaling.

Choosing the Right Alternative

  • Performance Requirements: Consider the performance characteristics (speed, scalability) required by your application.
  • Feature Set: Evaluate the specific features (e.g., JSON support, ACID compliance) needed for your database operations.
  • Compatibility: Assess compatibility with your existing infrastructure, development tools, and deployment environment.
  • Community and Support: Consider the availability of community support, documentation, and resources for troubleshooting and development.

Each of these alternatives has its strengths and is suitable for different use cases ranging from small-scale applications to enterprise-level systems. Assessing your project requirements and priorities will help determine the best fit for your relational database management needs.

Recommended Online Training: Mastering SQL with MariaDB: An Essential Beginner’s Guide

5458224 40f7show?id=oLRJ54lcVEg&offerid=1606991.5458224&bids=1606991

Update Linux Software Packages

Connect to mariadb-01.centlinux.com as root user by using a ssh tool.

It is a best practice to always update the Linux software packages before installing anything new.

# dnf update -y
Last metadata expiration check: 0:02:32 ago on Fri 29 May 2020 01:43:09 PM PKT.
Dependencies resolved.
Nothing to do.
Complete!

Our Linux machine is already up-to-date.

Install MariaDB Yum Repository

MariaDB Server 10.5.3 is the latest release therefore it is not available in CentOS 8 yum repositories.

MariaDB provides an official yum repository, that we can use to install MariaDB on CentOS 8.

Create a yum repository file as follows.

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

Add following directives in this file.

# MariaDB 10.5 CentOS repository list - created 2020-05-29 08:37 UTC
# http://downloads.mariadb.org/mariadb/repositories/

[mariadb]
name = MariaDB 
baseurl = http://yum.mariadb.org/10.5/centos8-amd64 
module_hotfixes=1 
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB 
gpgcheck=1 

Build cache for MariaDB yum repository.

# dnf makecache
CentOS-8 - AppStream                            7.8 kB/s | 4.3 kB     00:00
CentOS-8 - Base                                 292  B/s | 3.9 kB     00:13
CentOS-8 - Extras                               3.7 kB/s | 1.5 kB     00:00
MariaDB                                         164 kB/s | 512 kB     00:03
Metadata cache created.

Install MariaDB on CentOS 8

We have added the MariaDB yum repository, now we can easily install MariaDB on CentOS 8 by using dnf command.

# dnf install -y MariaDB-server
...
Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is mysql@localhost, it has no password either, but
you need to be the system 'mysql' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo

See the MariaDB Knowledgebase at https://mariadb.com/kb or the
MySQL manual for more instructions.

Please report any problems at https://mariadb.org/jira

The latest information about MariaDB is available at https://mariadb.org/.
You can find additional information about the MySQL part at:
https://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
Get Involved
... MariaDB-shared-10.5.3-1.el8.x86_64 galera-4-26.4.4-1.rhel8.0.el8.x86_64 Complete!

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.

Verify the status for MariaDB service.

# systemctl status mariadb.service
â mariadb.service - MariaDB 10.5.3 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor pre>
  Drop-In: /etc/systemd/system/mariadb.service.d
           ââmigrated-from-my.cnf-settings.conf
   Active: active (running) since Fri 2020-05-29 13:59:04 PKT; 12s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 12496 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_ST>
  Process: 12475 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && V>
  Process: 12473 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_STA>
 Main PID: 12485 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 11 (limit: 5916)
   Memory: 75.8M
   CGroup: /system.slice/mariadb.service
           ââ12485 /usr/sbin/mysqld

May 29 13:59:04 mariadb-01.centlinux.com mysqld[12485]: 2020-05-29 13:59:04 0 [>
May 29 13:59:04 mariadb-01.centlinux.com mysqld[12485]: 2020-05-29 13:59:04 0 [>

As mentioned in the installation output,

  • Linux root user can connect to MariaDB server as MariaDB root user without any password
  • Linux mysql user can connect to MariaDB server as MariaDB mysql user without any password

Also no other Linux user is allowed to login as MariaDB root or mysql users.

To allow the other Linux users to login as root or mysql user we need to set the password for these database users.

Connect with MariaDB server by using new mariadb command.

# mariadb -u root
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 4
Server version: 10.5.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)]>

Set strong passwords for root and mysql users.

MariaDB [(none)]> alter user 'root'@'localhost' identified by 'Ahmer@1234';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> alter user 'mysql'@'localhost' identified by 'Ahmer@1234';
Query OK, 0 rows affected (0.001 sec)

Reload the privileges tables.

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

List available databases in MariaDB server.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.001 sec)

Exit from MariaDB Shell.

MariaDB [(none)]> exit
Bye

Our MariaDB Server 10.5 has been installed successfully.

Configure MariaDB Security

We can optionally remove the test databases and restrict the remote logins by root user.

For this purpose, we can use new mariadb-secure-installation command.

# 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] n
 ... skipping.

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!

If you are new to Linux and facing difficulty in working at Linux Bash prompt. We recommend that, you should read The Linux Command Line, 2nd Edition: A Complete Introduction by William Shotts.

Final Thoughts

Installing MariaDB on CentOS 8 is essential for setting up a robust database management system that ensures reliable data storage and efficient querying. Whether you’re managing a small-scale application or a large enterprise database, MariaDB offers flexibility and performance.

For expert assistance in installing MariaDB on CentOS 8, tailored to your specific requirements, I provide a comprehensive service to guide you through the process seamlessly. Visit my Fiverr gig here to get started on optimizing your database infrastructure with MariaDB on Linux Server.

Leave a Reply

Your email address will not be published. Required fields are marked *