How to Install MariaDB on Debian 12

Introduction

MariaDB is an open-source relational database management system used to store and retrieve tabular data using the Structured Query Language (SQL). It supports ACID features to ensure consistency, transaction isolation, and durability. MariaDB is commonly used in web applications, analytics, content management systems, and e-commerce platforms.

This guide will walk you through the steps to install MariaDB on Debian 12, from updating your system and installing the necessary packages to configuring the database server for optimal performance. If you're considering alternative database solutions, you can also check out our article on Install MySQL on Debian 12 for a comparative approach.

Prerequisites

• Deploy a Debian 12 server on Vultr.
• Access the server using SSH as a non-root user with sudo privileges

Install MariaDB on Debian 12

MariaDB is available in the default package repositories on Debian 12. Follow the steps below to install MariaDB on your server.

1. Update the server package index.

   CONSOLE

   Copy

   $ sudo apt update

   Explain Code

   Powered by Vultr AgentBeta

2. Install the MariaDB database server package.

   CONSOLE

   Copy

   $ sudo apt install mariadb-server -y

   Explain Code

   Powered by Vultr AgentBeta

3. Test access to the MariaDB console.

   CONSOLE

   Copy

   $ mariadb -u root

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   Welcome to the MariaDB monitor.  Commands end with ; or \g.
   Your MariaDB connection id is 31
   Server version: 10.11.6-MariaDB-0+deb12u1 Debian 12
   
   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)]>

Manage the MariaDB System Service

MariaDB uses the mariadb.service or mysqld system service profile to run on the server. Follow the steps below to manage the system service and enable it to start at boot.

1. Enable MariaDB to start at system boot.

   CONSOLE

   Copy

   $ sudo systemctl enable mariadb.service

   Explain Code

   Powered by Vultr AgentBeta

2. View the MariaDB service status and verify that it's running.

   CONSOLE

   Copy

   $ sudo systemctl status mariadb.service

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   ● mariadb.service - MariaDB 10.11.6 database server
        Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; preset: enabled)
        Active: active (running) since Tue 2024-07-23 20:00:41 UTC; 11min ago
          Docs: man:mariadbd(8)
                https://mariadb.com/kb/en/library/systemd/
       Process: 64041 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
       Process: 64042 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
       Process: 64044 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-e>
       Process: 64113 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
       Process: 64115 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
      Main PID: 64103 (mariadbd)
        Status: "Taking your SQL requests now..."
         Tasks: 10 (limit: 1091)
        Memory: 209.3M
           CPU: 372ms
        CGroup: /system.slice/mariadb.service
                └─64103 /usr/sbin/mariadbd

3. Start the MariaDB service.

   CONSOLE

   Copy

   $ sudo systemctl start mariadb.service

   Explain Code

   Powered by Vultr AgentBeta

4. Stop the service to drop all active database connections.

   CONSOLE

   Copy

   $ sudo systemctl stop mariadb.service

   Explain Code

   Powered by Vultr AgentBeta

5. Restart the service.

   CONSOLE

   Copy

   $ sudo systemctl restart mariadb.service

   Explain Code

   Powered by Vultr AgentBeta

Secure the MariaDB Server

MariaDB includes insecure defaults such as test databases and user authentication is not active by default. Follow the steps below to enable authentication, remove insecure defaults, and secure the MariaDB server.

1. Run the MariaDB secure installation script to secure the database server.

   CONSOLE

   Copy

   $ sudo mariadb-secure-installation

   Explain Code

   Powered by Vultr AgentBeta

2. Press Enter to proceed with the default root database user password.

   Enter current password for root (enter for none):

3. Press N and press Enter to enable password authentication when prompted to switch to Unix socket authentication,

   Switch to unix_socket authentication [Y/n]

4. Press Y and then Enter to change the default root database user password.

   Change the root password? [Y/n]

5. Press Y + Enter to remove anonymous users.

   Remove anonymous users? [Y/n]

6. Disable remote access to the root database user.

   Disallow root login remotely? [Y/n]

7. Remove the default test database.

   Remove test database and access to it? [Y/n]

8. Reload privileges to save changes.

   Reload privilege tables now? [Y/n]

9. Restart the MariaDB service to apply your configuration changes.

   CONSOLE

   Copy

   $ sudo systemctl restart mariadb

   Explain Code

   Powered by Vultr AgentBeta

Access MariaDB

You can access MariaDB using the mariadb or mysql client utilities. Follow the steps below to access MariaDB and create a sample database on the server.

1. Log in to MariaDB as root and enter the password you set.

   CONSOLE

   Copy

   $ mariadb -u root -p

   Explain Code

   Powered by Vultr AgentBeta

2. View all databases available on the server.

   SQL

   Copy

   MariaDB [(none)]> SHOW DATABASES;

   Explain Code

   Powered by Vultr AgentBeta

   Output:

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

3. Create a new sample database example_vultr.

   SQL

   Copy

   MariaDB [(none)]> CREATE DATABASE example_vultr;

   Explain Code

   Powered by Vultr AgentBeta

4. Create a new sample user test_user with a strong password.

   SQL

   Copy

   MariaDB [(none)]> CREATE USER test_user@localhost INDENTIFIED BY 'Passw0RD#123';

   Explain Code

   Powered by Vultr AgentBeta

5. Grant the user full privileges to the database.

   SQL

   Copy

   MariaDB [(none)]> GRANT ALL PRIVILEGES ON example_vultr.* TO test_user@localhost;

   Explain Code

   Powered by Vultr AgentBeta

6. Flush the MariaDB privilege tables to apply changes.

   SQL

   Copy

   MariaDB [(none)]> FLUSH PRIVILEGES;

   Explain Code

   Powered by Vultr AgentBeta

7. Exit the database console.

   SQL

   Copy

   MariaDB [(none)]> quit

   Explain Code

   Powered by Vultr AgentBeta

8. Access the MariaDB console as the sample user test_user you created earlier.

   CONSOLE

   Copy

   $ mariadb -u test_user -p

   Explain Code

   Powered by Vultr AgentBeta

9. View all databases available to the user.

   SQL

   Copy

   MariaDB [(none)]> SHOW DATABASES;

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   +--------------------+
   | Database           |
   +--------------------+
   | example_vultr      |
   | information_schema |
   +--------------------+
   2 rows in set (0.000 sec)

10. Switch to the example_vultr database.

    SQL

    Copy

    MariaDB [(none)]> USE example_vultr;

    Explain Code

    Powered by Vultr AgentBeta

11. Create a new sample table with at least 3 columns.

    SQL

    Copy

    MariaDB [example_vultr]> create table products (
       id INT AUTO_INCREMENT PRIMARY KEY,
       product_name VARCHAR(40) NOT NULL,
       unit_price DECIMAL NOT NULL
       );

    Explain Code

    Powered by Vultr AgentBeta

12. Add the following sample data to the table.

    SQL

    Copy

    MariaDB [example_vultr]> INSERT INTO products 
        (product_name, unit_price) 
        VALUES ('Logitech Mouse', 150),
        ('Foam Cleaner', 300);

    Explain Code

    Powered by Vultr AgentBeta

13. View the table data.

    SQL

    Copy

    MariaDB [example_vultr]> SELECT * FROM products;

    Explain Code

    Powered by Vultr AgentBeta

    Output:

    +----+----------------+------------+
    | id | product_name   | unit_price |
    +----+----------------+------------+
    |  1 | Logitech Mouse |        150 |
    |  2 | Foam Cleaner   |        300 |
    +----+----------------+------------+
    2 rows in set (0.000 sec)

Conclusion

You have installed MariaDB on a Debian 12 server and secured it to require authentication for all database users. MariaDB is used in real-time applications, data analytics and logging applications as the database backend. For more information and security options, visit the MariaDB documentation.