MariaDB Setup

Debian / Ubuntu

Overview

Install MariaDB and complete initial configuration. MariaDB is a drop-in replacement for Oracle’s MySQL.

Assumptions

• Initial System Setup completed.

• Logged in as administrative user.

Update

Before getting started, update package repositories and apply upgrades for the latest patches.

# Debian
sudo apt update
sudo apt upgrade

Install MariaDB

The MariaDB version in the default Debian and Ubuntu repositories tends to be out of date. It is recommended to add a MariaDB repository mirror to your apt sources. Choose a mirror and select the latest version from MariaDB’s repository mirror list, this example will use OSUOSL’s mirror.

Start by verifying that the required packages apt-transport-https and curl are installed.

sudo apt install apt-transport-https curl

Create a directory to store apt keys, if not already present.

sudo install -m 0755 -d /etc/apt/keyrings

Download the MariaDB GPG key to the keyrings directory.

sudo curl -fsSL https://mariadb.org/mariadb_release_signing_key.pgp -o /etc/apt/keyrings/mariadb.pgp

Add the MariaDB repository for the correct distribution to the sources list.

# Debian
echo \
"deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/mariadb.pgp] https://ftp.osuosl.org/pub/mariadb/repo/11.rolling/debian $(. /etc/os-release && echo "$VERSION_CODENAME") main" \
| sudo tee /etc/apt/sources.list.d/mariadb.list > /dev/null

# Ubuntu
echo \
"deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/mariadb.pgp] https://ftp.osuosl.org/pub/mariadb/repo/11.rolling/ubuntu $(. /etc/os-release && echo "$VERSION_CODENAME") main" \
| sudo tee /etc/apt/sources.list.d/mariadb.list > /dev/null

Update the sources and install the MariaDB Server package.

sudo apt update

sudo apt install mariadb-server

MariaDB is enabled by default, manage the MariaDB service via systemd.

sudo systemctl enable mariadb

sudo systemctl start mariadb

sudo systemctl status mariadb

Secure Installation

MariaDB comes packaged with a configuration script to improve the security of your MariaDB server by implementing some basic security recommendations. Execute the script using the included command.

sudo mysql_secure_installation

The first step in the script will ask for the current MariaDB root password. By default there is no root password, so simply press enter to move on.

Enter current password for root (enter for none):

You will then have option to choose between setting a root password, or using unix_socket authentication. In this bit, password authentication will be used due to some of the limitations detailed in MariaDB’s Unix Socket documentation.

Enter N when asked about switching to unix_socket authentication, and Y when asked to change the root password.

Switch to unix_socket authentication [Y/n] N
...
Change the root password? [Y/n] Y

Generate a secure password using your password manager of choice, and enter it when prompted.

New password:
Re-enter new password:

Following password creation, a series of questions will be presented to complete the basic security setup. All of these should be answered with Y.

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
...
All done!  If you have completed all of the above steps, your MariaDB
installation should now be secure.

Admin User

Create an administrative user for everyday use, avoid using the root user unless required. This will also prevent the need for sudo to access MariaDB.

Login to the MariaDB instance as the root user. Use the generated root password when prompted. The MariaDB root user can only be accessed by the system root user, therefore sudo is required.

sudo mariadb -u root

Create Admin User

Create a new administrative user, in this guide sqladmin.

CREATE USER 'sqladmin'@'localhost' IDENTIFIED BY 'Generated-Secure-Password';

Grant the sqladmin user administrative privileges.

GRANT ALL PRIVILEGES ON *.* TO 'sqladmin'@'localhost' WITH GRANT OPTION;

Exit MariaDB to return to the system shell.

exit;

Delete History

Consider deleting the SQL command history after creating users to prevent the entered password from being saved.

Delete SQL command history by removing the .mysql_history file from a users home directory.

rm $HOME/.mariadb_history

Remove history for the root user with sudo.

sudo rm /root/.mariadb_history

Setup Database

Open a MariaDB session as an administrative user. Enter the password when prompted.

mariadb -u sqladmin -p

Create Database

Create a new database named exampledb.

CREATE DATABASE IF NOT EXISTS exampledb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

Create User

Create a SQL user to access the exampledb database: exampledbuser. When generating a password, keep it at 41 characters with the mysql_native_password plugin.

CREATE USER 'exampledbuser'@'localhost' IDENTIFIED BY 'Generated_Secure_Password';

Database Permissions

Grant the exampledbuser user access to the exampledb database.

GRANT ALL PRIVILEGES ON exampledb.* TO 'exampledbuser'@'localhost';

Errors

1045

After creating a new user and attempting to login, you may receive error 1045. This means the password is incorrect, the user lacks proper permissions, or the password is too long. If you are certain the password is correct, verify that the password is not more than 41 characters. 41 characters is the upper limit for the mysql_native_password plugin.

ERROR 1045 (28000): Access denied for user 'exampleuser'@'localhost' (using password: YES)

References

^{1 2 3}