How to Install and Use MariaDB on Debian 12

March 17, 2024 March 17, 2024 16 min read

Welcome to Geekandnix, in this guide, I will teach you how to install and use the MariaDB server on the Debian 12 server.

Following the MariaDB server installation, I will show you how to secure MariaDB deployment, basic usage of the mariadb client, and basic queries for creating a user, database, and table. Lastly, I also show you basic CRUD (Create, Read, Update, Delete) operations in MariaDB.

Without any more delays, let’s start!

Prerequisites

Before you move on, confirm that you have a Debian 12 server initialized with non-root users as sudo privileges. Follow our guide to Initial Server Setup with Debian 12 (9 things todo)

Installing MariaDB on Debian

MariaDB is a free and open-source Relational Database Management System (RDBMS). It is a MySQL fork and was created by original MySQL creators to ensure stays open-source, and provides more features than MySQL. MariaDB is more scalable and suitable for large datasets and provides faster SQL query speed compared to MySQL.

On Debian, MariaDB is available by default and you can install it easily via APT. But if you want to get the latest version of MariaDB, then install MariaDB via the official MariaDB repository.

Installing MariaDB via Debian repository

The MariaDB database is available by default on the Debian repository. It is also a replacement for the default-mysql package on Debian, which can easily be installed via APT.

To install the MariaDB server via the Debian repository, carry out the following:

1. First, run the command below to update and refresh your Debian package list.

sudo apt update

Get:1 http://deb.debian.org/debian bookworm InRelease [151 kB]
Get:2 http://security.debian.org/debian-security bookworm-security InRelease [48.0 kB]
Get:3 http://deb.debian.org/debian bookworm-updates InRelease [55.4 kB]
Get:4 http://security.debian.org/debian-security bookworm-security/main Sources [84.3 kB]
Get:5 http://security.debian.org/debian-security bookworm-security/main amd64 Packages [146 kB]
Get:6 http://security.debian.org/debian-security bookworm-security/main Translation-en [87.7 kB]
Get:7 http://deb.debian.org/debian bookworm/main Sources [9,489 kB]

2. Once the repository is updated, run the command below to check the info of the mariadb-server package.

sudo apt info mariadb-server

As you can see below, the Debian Bookworm repository provides MariaDB server 10.11.6.

Package: mariadb-server
Version: 1:10.11.6-0+deb12u1
Priority: optional
Section: database
Source: mariadb
Maintainer: Debian MySQL Maintainers <[email protected]>
Installed-Size: 55.2 MB
Provides: virtual-mysql-server

3. Now, run the apt install command below to install the MariaDB server. Enter y to proceed with the installation.

sudo apt install mariadb-server

Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
...
  libsnappy1v5 libterm-readkey-perl libtimedate-perl liburi-perl mariadb-client mariadb-client-core mariadb-common mariadb-plugin-provider-bzip2
  mariadb-plugin-provider-lz4 mariadb-plugin-provider-lzma mariadb-plugin-provider-lzo mariadb-plugin-provider-snappy mariadb-server-core
...
0 upgraded, 44 newly installed, 0 to remove and 34 not upgraded.
Need to get 19.1 MB of archives.
After this operation, 194 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 http://deb.debian.org/debian bookworm/main amd64 libsigsegv2 amd64 2.14-1 [37.2 kB]]
Get:2 http://deb.debian.org/debian bookworm/main amd64 mariadb-common all 1:10.11.6-0+deb12u1 [24.4 kB]

4. After installation is finished, check the MariaDB version with the command below.

mariadb --version

You will see the MariaDB server xxx is installed on your Debian server.

mariadb  Ver 15.1 Distrib 10.11.6-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

Installing MariaDB via official MariaDB repository

If you need to install the latest version of the MariaDB server, you must install it via the official MariaDB repository, which is available for most Linux distributions including Debian, Ubuntu, and RHEL. At this time, the default MariaDB repository provides MariaDB 10.11 and 11.x.

To install MariaDB server 10.11 or 11.x via the MariaDB repository, follow these steps:

1. First, visit the MariaDB repository list, choose the distribution as Debian 12 “Bookworm” and the MariaDB version 10.11. Then select the mirror nearest to your location.

Setting up MariaDB repository for Debian 12

2. Now, run the command below to install basic packages apt-transport-https, curl, and gnupg2 to your Debian server.

sudo apt install -y apt-transport-https curl gnupg2

3. Once basic packages are installed, run the following command to download the GPG key for the MariaDB repository.

sudo mkdir -p /etc/apt/keyrings
sudo curl -o /etc/apt/keyrings/mariadb-keyring.pgp 'https://mariadb.org/mariadb_release_signing_key.pgp'

4. Next, run the following command to add the MariaDB repository to 10.11.

sudo sh -c "echo 'deb [arch=amd64,arm64 signed-by=/etc/apt/keyrings/mariadb-keyring.pgp] https://mirrors.gigenet.com/mariadb/repo/10.11/debian bookworm main' > /etc/apt/sources.list.d/mariadb.list"

5. After that, run the apt command below to refresh your Debian package index and install the MariaDB server via the official MariaDB repository.

sudo apt update && sudo apt install mariadb-server

When asked for confirmation, enter Y to proceed.

...
Get:4 https://mirrors.gigenet.com/mariadb/repo/10.11/debian bookworm InRelease [4,625 B]
Get:5 https://mirrors.gigenet.com/mariadb/repo/10.11/debian bookworm/main amd64 Packages [41.0 kB]
Get:6 https://mirrors.gigenet.com/mariadb/repo/10.11/debian bookworm/main arm64 Packages [34.3 kB]
...
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
...
  mariadb-client-core mariadb-common mariadb-server mariadb-server-core mysql-common pv socat
0 upgraded, 34 newly installed, 0 to remove and 34 not upgraded.
Need to get 27.3 MB/29.6 MB of archives.
After this operation, 238 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 https://mirrors.gigenet.com/mariadb/repo/10.11/debian bookworm/main amd64 galera-4 amd64 26.4.16-deb12 [11.7 MB]
...
Get:5 https://mirrors.gigenet.com/mariadb/repo/10.11/debian bookworm/main amd64 mariadb-server-core amd64 1:10.11.7+maria~deb12 [7,576 kB]
Get:6 https://mirrors.gigenet.com/mariadb/repo/10.11/debian bookworm/main amd64 mariadb-server amd64 1:10.11.7+maria~deb12 [3,940 kB]

6. Lastly, check the MariaDB version with the command below.

sudo mariadb --version

The following output confirms that MariaDB 10.11 has been installed.

mariadb  Ver 15.1 Distrib 10.11.7-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

Securing MariaDB server on Debian

With the MariaDB server installed, you need to set up the root password and secure the MariaDB installation. Both tasks can be done easily using the mariadb-secure-installation utility that is included in the MariaDB package.

To secure MariaDB server installation, complete the following actions:

1. First, execute the command mariadb-secure-installation on your terminal.

sudo mariadb-secure-installation

From here, you will be asked about numerous MariaDB server configurations.

2. When asked for the current MariaDB password, press ENTER to continue. The default MariaDB server installation comes without a root password.

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...

3. Enter n when asked to switch the authentication method to unix_socket_authentication.

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.

4. Enter Y when asked to change the MariaDB root password. Then, input a new strong password and repeat.

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!

5. Now, Enter Y again to remove the default anonymous user from your MariaDB server installation.

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!

6. When asked to disable remote login for MariaDB root user, enter Y to confirm.

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!

7. Next, enter Y to remove the database test from your MariaDB server.

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!

8. Lastly, confirm to reload table privileges and apply the changes by typing Y again.

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!

Managing MariaDB service on Debian

As an administrator, you must know the basic command for managing MariaDB service. For example, you must know how to restart MariaDB whenever needed or check the MariaDB server port to ensure MariaDB is running.

Learn how to manage the MariaDB server with the following:

1. To start the MariaDB server, run the systemctl start command below.

sudo systemctl start mariadb

2. Once started, run the command below to verify the MariaDB server and ensure that the service is running.

sudo systemctl status mariadb

If MariaDB server running, you will receive active (running).

● mariadb.service - MariaDB 10.11.7 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; preset: enabled)
    Drop-In: /etc/systemd/system/mariadb.service.d
             └─migrated-from-my.cnf-settings.conf
     Active: active (running) since ; 4min 7s ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 5940 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 5941 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 5943 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 >
    Process: 5984 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 5986 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
   Main PID: 5972 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 10 (limit: 15229)
     Memory: 170.5M
        CPU: 2.713s
     CGroup: /system.slice/mariadb.service
             └─5972 /usr/sbin/mariadbd

3. Additionally, run the ss command below to verify the MariaDB server via the system port.

ss -tulpn | grep 3306

When the MariaDB server running, you will get port 3306 is used by the mariadbd program.

tcp   LISTEN 0      80         127.0.0.1:3306       0.0.0.0:*    users:(("mariadbd",pid=5972,fd=18))

4. Now, if you need to stop MariaDB, run the systemctl stop command below.

sudo systemctl stop mariadb

5. Lastly, whenever you make changes to MariaDB, restart it with the following command.

sudo systemctl restart mariadb

Creating Database and User on MariaDB

After MariaDB is secured and running, move on to create the MariaDB database and user for your application. This task can be completed by logging into the MariaDB server mariadb client utility.

To create a MariaDB database and user via the mariadb client, proceed as follows:

1. First, run the mariadb command below to log in to the MariaDB server. Enter your MariaDB root password when asked.

sudo mariadb -u root -p

Once logged in to the MariaDB server, you will get a prompt such as MariaDB [(none)]>.

Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 39
Server version: 10.11.7-MariaDB-1:10.11.7+maria~deb12 mariadb.org binary distribution

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)]>

2. Run the query CREATE DATABASE to create a new database myappdb. Then, check available databases on your MariaDB server.

CREATE DATABASE myappdb;
SHOW DATABASES;

You’ll get the database myappdb has been created.

+--------------------+
| Database           |
+--------------------+
| information_schema |
| myappdb            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.002 sec)

3. Now, run the query CREATE USER to create a new user myappuser with the password xxx. Then, run the GRANT ALL PRIVILEGES query to give all privileges on the database myappdb to the myappuser. After that, reload table privileges with the query FLUSH PRIVILEGES to apply the changes to MariaDB.

CREATE USER myappuser@localhost IDENTIFIED BY 'Strongp4ssw0rd';
GRANT ALL PRIVILEGES ON myappdb.* TO myappuser@localhost;
FLUSH PRIVILEGES;

4. Next, run the following query to get a list of users on the MariaDB server. You will get the user myappuser has been created.

SELECT user FROM mysql.user;

+-------------+
| User        |
+-------------+
| mariadb.sys |
| myappuser   |
| mysql       |
| root        |
+-------------+
4 rows in set (0.003 sec)

5. Lastly, run the query below to check privileges for the myappuser. Then, type quit to exit from the MariaDB server.

SHOW GRANTS FOR myappuser@localhost;
quit

As displayed below, the myappuser has privileges to manage the myappdb database.

+------------------------------------------------------------------------------------------------------------------+
| Grants for myappuser@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `myappuser`@`localhost` IDENTIFIED BY PASSWORD '*7B70ECB7506EF9423CE4FDDFD4DF740354088C23' |
| GRANT ALL PRIVILEGES ON `myappdb`.* TO `myappuser`@`localhost`                                                   |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

Connecting to MariaDB via specific user

Your new database and user are created in MariaDB. But how you can connect to it? Here, you will connect to the MariaDB server using a specific user to the specific database, then you’ll also verify the connection status to the MariaDB server.

Follow these actions to connect to the MariaDB via a specific user to a specific database:

1. Run the command below to connect to the MariaDB server via myappuser to the database myappdb. Enter the password for myappuser when asked.

sudo mariadb -h localhost -u myappuser -D myappdb -p

2. Once logged in to the MariaDB server, run the status query to verify your connections.

status

You’ll receive information that you’re connected to the database myappdb via myappuser.

--------------
mariadb  Ver 15.1 Distrib 10.11.7-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

Connection id:          41
Current database:       myappdb
Current user:           myappuser@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.11.7-MariaDB-1:10.11.7+maria~deb12 mariadb.org binary distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb3
Conn.  characterset:    utf8mb3
UNIX socket:            /run/mysqld/mysqld.sock
Uptime:                 8 min 20 sec

Threads: 1  Questions: 92  Slow queries: 0  Opens: 33  Open tables: 26  Queries per second avg: 0.184
--------------

Creating Table in MariaDB

After connecting to your database, it’s time to create a table in your database. Learn the basic query for managing tables in MariaDB by performing the following actions:

1. Run the CREATE TABLE query below to create a new table movies with columns id, title, genre, and year.

CREATE TABLE movies (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(50),
genre VARCHAR(50),
year INT(100)
);

2. Now, run the query below to check the tables in your current database. The table movies should be listed.

SHOW TABLES;

+-------------------+
| Tables_in_myappdb |
+-------------------+
| movies            |
+-------------------+
1 row in set (0.001 sec)

3. Lastly, run the DESCRIBE query below to check columns in the table movies.

DESCRIBE movies;

As pointed out in the output below, there is columns id, title, genre, and year in the table movies.

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| title | varchar(50) | YES  |     | NULL    |                |
| genre | varchar(50) | YES  |     | NULL    |                |
| year  | int(100)    | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.002 sec)

Basic CRUD (Create Read Update Delete) in MariaDB

At this point, you have created a table on your database. Moving forward, you’ll learn queries for basic CRUD (Create, Read, Update, and Delete) operations in MariaDB.

Master the basic CRUD operation (Create, Read, Update, and Delete) in MariaDB with the following:

1. Run the following INSERT query to add data to the table movies.

INSERT INTO movies (title, genre, year)
VALUES ('John Wick: Chapter 4', 'Action', 2023);

You can also add multiple data at once with the INSERT query like the following:

INSERT INTO movies (title, genre, year)
VALUES ('Avatar: The Way of Water', 'Fantasy', 2022),
('Spider-Man: No Way Home', 'Adventure', 2021),
('The Equalizer 3', 'Thriller', 2020),
('Dune: Part One', 'Drama', 2021);

MariaDB [myappdb]> INSERT INTO movies (title, genre, year)
    -> VALUES ('John Wick: Chapter 4', 'Action', 2023);
Query OK, 1 row affected (0.003 sec)

MariaDB [myappdb]> INSERT INTO movies (title, genre, year)
    -> VALUES ('Avatar: The Way of Water', 'Fantasy', 2022),
    -> ('Spider-Man: No Way Home', 'Adventure', 2021),
    -> ('The Equalizer 3', 'Thriller', 2020),
    -> ('Dune: Part One', 'Drama', 2021);
Query OK, 4 rows affected (0.005 sec)
Records: 4  Duplicates: 0  Warnings: 0

2. Now, run the SELECT query below to retrieve data from table movies. The * symbol allows you to retrieve data from all columns.

SELECT * FROM movies;

+----+--------------------------+-----------+------+
| id | title                    | genre     | year |
+----+--------------------------+-----------+------+
|  1 | John Wick: Chapter 4     | Action    | 2023 |
|  2 | Avatar: The Way of Water | Fantasy   | 2022 |
|  3 | Spider-Man: No Way Home  | Adventure | 2021 |
|  4 | The Equalizer 3          | Thriller  | 2020 |
|  5 | Dune: Part One           | Drama     | 2021 |
+----+--------------------------+-----------+------+
5 rows in set (0.001 sec)

If you want to display data from a specific column, replace * with the column name like this:

SELECT title, genre FROM movies;

+--------------------------+-----------+
| title                    | genre     |
+--------------------------+-----------+
| John Wick: Chapter 4     | Action    |
| Avatar: The Way of Water | Fantasy   |
| Spider-Man: No Way Home  | Adventure |
| The Equalizer 3          | Thriller  |
| Dune: Part One           | Drama     |
+--------------------------+-----------+
5 rows in set (0.001 sec)

3. Next, run the UPDATE query below followed by the WHERE statement to update data in MariaDB. In this example, you will update column year to 2023 for specific title = The Equalizer 3.

UPDATE movies
SET year = 2023
WHERE title = 'The Equalizer 3';

Once updated, run the query below to verify the updated data. You will see the year for title = The Equalizer 3 has changed to 2023.

SELECT * FROM movies
WHERE title = 'The Equalizer 3';

MariaDB [myappdb]> UPDATE movies
    -> SET year = 2023
    -> WHERE title = 'The Equalizer 3';
Query OK, 1 row affected (0.005 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [myappdb]> SELECT * FROM movies
    -> WHERE title = 'The Equalizer 3';
+----+-----------------+----------+------+
| id | title           | genre    | year |
+----+-----------------+----------+------+
|  4 | The Equalizer 3 | Thriller | 2023 |
+----+-----------------+----------+------+
1 row in set (0.002 sec)

4. Lastly, run the DELETE query below to remove data from MariaDB. In this case, you will remove data for title = The Equalizer 3.

DELETE FROM movies
WHERE title = 'The Equalizer 3';

Once deleted, run the following query to get all data in table movies. The data for The Equalizer 3 has been deleted.

SELECT * FROM movies;

MariaDB [myappdb]> DELETE FROM movies
    -> WHERE title = 'The Equalizer 3';
Query OK, 1 row affected (0.005 sec)

MariaDB [myappdb]> SELECT * FROM movies;
+----+--------------------------+-----------+------+
| id | title                    | genre     | year |
+----+--------------------------+-----------+------+
|  1 | John Wick: Chapter 4     | Action    | 2023 |
|  2 | Avatar: The Way of Water | Fantasy   | 2022 |
|  3 | Spider-Man: No Way Home  | Adventure | 2021 |
|  5 | Dune: Part One           | Drama     | 2021 |
+----+--------------------------+-----------+------+
4 rows in set (0.002 sec)

Optional: Clean up your MariaDB

If you need to delete the table and database to clean up your MariaDB installation, proceed as follows:

1. Run the DROP TABLE query below to delete table movies from your MariaDB server.

DROP TABLE movies;

2. If you want to delete the database, execute the DROP DATABASE query like the following:

DROP DATABASE myappdb;

Conclusion

In this guide, you have learned how to install and use the MariaDB server on Debian 12. You also learned how to secure MariaDB, basic usage of the mariadb client, and basic queries for creating a database, user, and table. Lastly, you have learned basic MariaDB queries related to CRUD (Create, Read, Update, and Delete) operations.

ArvidL

System administrator and devops enthusiast, leveraging over 10+ years of Linux expertise to optimize operations. Proficient in FreeBSD, VMWare, KVM, Proxmox, PfSense, Ansible, Docker, and Kubernetes.