Install MariaDB 10.6 on CentOS 7/ CentOS 8

MariaDB is a commonly used OpenSource database mainly known for being robust and scalable with new storage engines. MariaDB is a development of MySQL which puts focus on stability and performance and to make it free to users. It is the default database in most Linux distribution. With a variety of tools and plugins, MariaDB is widely applicable.

In this guide, we are going to look at how to install MariaDB 10.6 on CentOS 7/ CentOS 8

Features of MariaDB 10.6

MariaDB 10.6 is the current stable version of MariaDB and comes with a number of new features as discussed below:

• Ignored Indexes – These are indexes that are visible and maintained but not used by the optimizer
• sys schema supported- This is a “system” database containing views and procedures for investigating performance problems.
• SKIP LOCKED – Locked tables are skipped from being updated or selected.
• JSON_TABLE() – can create a JSON table that can be used as a subquery from a JSON document.
• OFFSET…FETCH…[WITH TIES] – WITH TIES is an optional clause that adds extra functionality. Example as used
• Oracle compatibility – There are ongoing works in making MariaDB compatible with OracleDB with some Oracle Syntaxes and functions already added.

The Improvements in MariaDB 10.6 from MariaDB 10.5 include:

• Atomic DDL – CREATE, ALTER, DROP and RENAME are atomic and crash safe. If MariaDB server crashes while processing any of these operations, the change will either e done completely or not done at all.
• InnoDB improvements – First insert to an empty table is faster. Also writes to temporary tables are avoided.Faster implicit and explicit temporary tables.
• Improvements in Galera. Ability to enable encrypted connections between two nodes without downtime. Also added flags to specify if galera controversial compatible features should be enabled.
• Clean up to remove unsupported features such as TukoDB Engine, Cassandra Engine, some InnoDB variables and some innodb_checksum_algorithm.

Step 1: Update System Packages

Ensure that you are running the latest system packages before installation to avoid possible inconveniences with dependencies.

sudo dnf upgrade

Step 2: Add MariaDB Repository

We need to create a MariaDB repo file and add the content for MariaDB installation

sudo vim /etc/yum.repos.d/MariaDB.repo

Paste the below content and save the file

For CentOS 8

# MariaDB 10.6 CentOS repository list - created 2021-08-04 11:23 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.6/centos8-amd64
module_hotfixes=1
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

For CentOS 7

# MariaDB 10.6 CentOS repository list - created 2021-08-04 11:35 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.6/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Step 3: Install MariaDB 10.6 on CentOS 8 | CentOS 7

Once you have saved the repo file, proceed to install MariaDB 10.6

sudo dnf install MariaDB-server MariaDB-client

Step 4: Start and Enable MariaDB

Once installed, start mariadb and also enable it to start automatically on system reboot.

sudo systemctl start mariadb
sudo systemctl enable mariadb

Secure database server installation:

sudo mysql_secure_installation

Step 5: Check MariaDB Version

We need to first login to MariaDB to be able to check the installed version.

$ mysql -u root -p
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.6.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)]>

You can already see the installed MariaDB version from the output above. However, you can also run the below command to check MariaDB version

MariaDB [(none)]> SELECT VERSION();
+-------------------------------------+
| VERSION()                           |
+-------------------------------------+
| 10.6.3-MariaDB |
+-------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

Step 6: How to Create a Database in MariaDB

Once you login to Mariadb, create a database as below:

#Create a new database
MariaDB [(none)]> CREATE DATABASE db1;
Query OK, 1 row affected (0.000 sec)

#If the database with the same name exists, you should get an error
CREATE DATABASE db1;
ERROR 1007 (HY000): Can't create database 'db1'; database exists

#Create a database if already exits, replace
MariaDB [(none)]>  CREATE OR REPLACE DATABASE db1;
Query OK, 2 rows affected (0.009 sec)

#First check if a database exists 
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS db1;
Query OK, 1 row affected, 1 warning (0.000 sec)

# Check Databases MariaDB
MariaDB [(none)]>  SHOW DATABASES;

Step 7: How to Create User and Grant Privileges in Mariadb

To create a user and grant privileges to the databases, run the commands as shown:

#Create user mariadb
MariaDB [(none)]> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

#Grant all privileges to the user
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

#Grant privileges to a specific database
MariaDB [(none)]> GRANT ALL PRIVILEGES ON 'DB1'.* TO 'user1'@'localhost';

#Remember to refresh the privileges
MariaDB [(none)]> FLUSH privileges;

#To check user grants in MariaDB
MariaDB [(none)]> SHOW GRANTS FOR 'myuser'@'localhost';

Step 8: Creating a Table and Adding Data in MariaDB

Since we already have a database, we can proceed to create a table and add some values.

MariaDB [(none)]> USE db1;
MariaDB [(none)]> CREATE TABLE employees (id INT, name VARCHAR(20), email VARCHAR(20));
MariaDB [(none)]> INSERT INTO employees (id,name,email) VALUES(01,"lorna","[email protected]")

Cleaning Up MariaDB

To completely remove MariaDB, run the following commands:

sudo dnf remove MariaDB-server MariaDB-client
sudo rm -rf /var/lib/mysql/
sudo rm /etc/my.cnf

You have successfully installed the latest MariaDB version, MariaDB 10.6. We have also seen how you can clean up your MariaDB installation. I hope the guide has been helpful.

Video Courses to Learn MySQL / MariaDB Databases: