How to Install and Use MariaDB on Debian 11

MariaDB is an open-source and extremely popular relational database management systems developed by MySQL developers. MariaDB is an alternative to MySQL and allows you to perform any operation that MySQL performs. It is designed for high availability, scalability, and performance in data-intensive, business-critical applications. MariaDB can be installed on many operating systems including Linux, FreeBSD, Solaris, Mac OS X, Windows, and more.

At the time of writing this tutorial, the latest available version of MariaDB is 10.6. It comes with the following new features:

• Atomic DDL
• SQL Syntax
• Oracle Compatibility
• InnoDB
• Replication, Galera and Binlog

In this post, we will explain how to install and use MariaDB 10.6 on Debian 11.

Step 1- Add MariaDB Repository

By default, the latest version of MariaDB is not included in the Debian 11 default repository, so you will need to add the MariaDB official repository to your system.

First, install the required dependencies using the following commands:

apt-get update -y

apt-get install curl software-properties-common dirmngr gnupg2 -y

Once all the dependencies are installed, download and add the GPG key with the following command:

curl -LsS -O https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
bash mariadb_repo_setup --os-type=debian  --os-version=buster --mariadb-server-version=10.6

You will get the following output:

[info] Skipping OS detection and using OS type 'debian' and version 'buster' given on the command line
[info] Checking for script prerequisites.
[info] Repository file successfully written to /etc/apt/sources.list.d/mariadb.list
[info] Adding trusted package signing keys...
[info] Running apt-get update...
[info] Done adding trusted package signing keys

Next, download and install the MariaDB repository installation package using the command below:

wget http://ftp.us.debian.org/debian/pool/main/r/readline5/libreadline5_5.2+dfsg-3+b13_amd64.deb
dpkg -i libreadline5_5.2+dfsg-3+b13_amd64.deb

Once the repository is added, you can update the package cache with the following command:

apt-get update -y

Step 2 – Install MariaDB 10.6 on Debian 11

Now, run the following command to install MariaDB 10.6 on Debian 11.

apt-get install mariadb-server mariadb-client -y

Once the installation is completed, start and enable the MariaDB service using the following command:

systemctl start mariadb
systemctl enable mariadb

You can now check the status of MariaDB with the following command:

systemctl status mariadb

You should see the following output:

● mariadb.service - MariaDB 10.6.5 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
    Drop-In: /etc/systemd/system/mariadb.service.d
             └─migrated-from-my.cnf-settings.conf
     Active: active (running) since Fri 2021-12-17 05:46:58 UTC; 1min 28s ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 17393 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 17394 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 17396 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $>
    Process: 17455 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 17457 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
   Main PID: 17443 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 10 (limit: 2341)
     Memory: 78.6M
        CPU: 912ms
     CGroup: /system.slice/mariadb.service
             └─17443 /usr/sbin/mariadbd

Step 3 – Secure MariaDB Installation

By default, the MariaDB installation is not secured and its root password is not set. You can do both by running the following script:

mysql_secure_installation

You will be asked several questions to secure the MariaDB installation and set the root password, as shown below:

Enter current password for root (enter for none): 
Switch to unix_socket authentication [Y/n] Y
Change the root password? [Y/n] Y
New password: 
Re-enter new password: 
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

Once you are done, log in to the MariaDB shell with the following command:

mysql -u root -p

Once you are logged in, run the following command to check the MariaDB version:

SELECT VERSION();

You will get the following output:

+--------------------------------------+
| VERSION()                            |
+--------------------------------------+
| 10.6.5-MariaDB-1:10.6.5+maria~buster |
+--------------------------------------+
1 row in set (0.000 sec)

Step 4 – Create Database and User in MariaDB

In this section, we will show you how to create a database and user in MariaDB.

To create a database called newdb, run the following command:

CREATE DATABASE newdb;

Next, check the created database using the following command:

SHOW DATABASES;

You should see the following output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| newdb              |
| performance_schema |
| sys                |
+--------------------+

To create a user called newuser, run the following command:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

To grant all the privileges to newdb database, run the following command:

GRANT ALL PRIVILEGES ON newdb.* TO 'newuser'@'localhost' IDENTIFIED BY 'password';

Next, reload the privileges using the following command:

FLUSH privileges;

Step 6 – Create a Table in MariaDB

In this section, we will show you how to create a table and insert data into the table in MariaDB.

First, change the database to newdb using the following command:

USE newdb;

Next, create a table called students using the following command:

CREATE TABLE students (id INT, name VARCHAR(20), email VARCHAR(20));

Next, verify the created tables using the following command:

SHOW TABLES;

You should see the following output:

+-----------------+
| Tables_in_newdb |
+-----------------+
| students        |
+-----------------+

To insert the data in the first, second, and third rows of the table, run the following command:

INSERT INTO students (id,name,email) VALUES(01,"hitesh","[email protected]");
INSERT INTO students (id,name,email) VALUES(02,"raj","[email protected]");
INSERT INTO students (id,name,email) VALUES(03,"jay","[email protected]");

To verify your inserted data, run the following command:

SELECT * FROM students;

You should see the following output:

+------+--------+------------------+
| id   | name   | email            |
+------+--------+------------------+
|    1 | hitesh | [email protected] |
|    2 | raj    | [email protected]    |
|    3 | jay    | [email protected]    |
+------+--------+------------------+

Step 7 – How to Uninstall MariaDB

If you want to remove MariaDB from your system, run the following command:

apt-get remove mariadb-server --purge

After removing the MariaDB package, run the following command to uninstall all unwanted dependencies:

apt-get autoremove
apt-get clean

Next, remove the MariaDB database files using the following command:

rm -rf /var/lib/mysql/

Conclusion

In the above guide, we explained how to install MariaDB 10.6 on Debian 11. We also explained how to create a database, user, and table in MariaDB. Try using a MariaDB database on dedicated hosting from Atlantic.Net!