Vultr DocsLatest Articles

How to Install MariaDB on Debian 12

DebianMariaDB
FK
Updated on November 21, 2023
How to Install MariaDB on Debian 12 header image

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 article explains how to install MariaDB on a Debian 12 server.

Prerequisites

Install MariaDB

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 
    $ sudo apt update

  2. Install the MariaDB database server package.

    console 
    $ sudo apt install mariadb-server -y

  3. Test access to the MariaDB console.

    console 
    $ mariadb -u root

    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 
    $ sudo systemctl enable mariadb.service

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

    console 
    $ sudo systemctl status mariadb.service

    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 
    $ sudo systemctl start mariadb.service

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

    console 
    $ sudo systemctl stop mariadb.service

  5. Restart the service.

    console 
    $ sudo systemctl restart mariadb.service

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 
    $ sudo mariadb-secure-installation

  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 
    $ sudo systemctl restart mariadb

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 
    $ mariadb -u root -p

  2. View all databases available on the server.

    sql 
    MariaDB [(none)]> SHOW DATABASES;

    Output:

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

  3. Create a new sample database example_vultr.

    sql 
    MariaDB [(none)]> CREATE DATABASE example_vultr;

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

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

  5. Grant the user full privileges to the database.

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

  6. Flush the MariaDB privilege tables to apply changes.

    sql 
    MariaDB [(none)]> FLUSH PRIVILEGES;

  7. Exit the database console.

    sql 
    MariaDB [(none)]> quit

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

    console 
    $ mariadb -u test_user -p

  9. View all databases available to the user.

    sql 
    MariaDB [(none)]> SHOW DATABASES;

    Output:

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

  10. Switch to the example_vultr database.

    sql 
    MariaDB [(none)]> USE example_vultr;

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

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

  12. Add the following sample data to the table.

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

  13. View the table data.

    sql 
    MariaDB [example_vultr]> SELECT * FROM products;

    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.