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 included in the default Debian and Ubuntu repositories tends to be out of date, so it is recommended to add a MariaDB repository to your apt sources. For example we will use OSUOSL’s mirror. Choose your own and select the latest version from MariaDB’s repository mirror list.
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.2/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.2/ubuntu $(. /etc/os-release && echo "$VERSION_CODENAME") main" \
| sudo tee /etc/apt/sources.list.d/mariadb.list > /dev/null
Finally, update the sources and install MariaDB using apt
.
sudo apt update && sudo apt install mariadb-server
MariaDB is enabled by default, Verify this and check that the mariadb
service is running.
sudo systemctl status mariadb
The MariaDB service can easily be managed via systemd
.
sudo systemctl start 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've 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
MariaDB. “MariaDB Documentation.” 2024. ↩︎
MariaDB. “MariaDB Knowledge Base.” 2024. ↩︎
Oracle. “MySQL Documentation.” 2024. ↩︎