Installing and configuring a MariaDB Database server in Ubuntu or Debian

MariaDB is an open source database server that is derived from MySQL. Together with Apache and PHP, MariaDB forms the so-called LAMP stack (Linux Apache Mariadb PHP) which is used to host dynamic websites (e.g., WordPress). 

MariaDB is the most used database software in Linux and almost completely corresponds to MySQL. In this article, we show you how to install MariaDB 10.5 on a VPS with Ubuntu or Debian.

Installing and configuring MariaDB 

Step 1

Connect to your VPS via SSH or use the VPS console and update your server first:

apt -y update && apt -y upgrade

It is recommended to reboot your VPS after an update and then to run these commands again. If you've just installed a kernel update as well with the commands above some software may not be available until you've restarted your server.

Step 2

Via the command below you'll retrieve and execute the MariaDB Package Repository setup script. This allows you to configure your VPS to ebable the installation of packages from the MariaDB Package Repository and install a specific MariaDB version, here 11.4 (you are free to replace 11.4 with another version number):

curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash -s -- --mariadb-server-version=11.4

You can also simply install the latest stable version using the command below. Besides the general MariaDB repository this also adds a repository for MariaDB MaxScale and  MariaDB Tools (the latter contains Percona XtraBackup and its dependencies):

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash

Step 3

Update your VPS to add the new repository and then install MariaDB with the commands:

apt -y update
apt -y install mariadb-server mariadb-client

During installation you'll be asked to provide a password. Provide a strong password so you won't need to change it again in step 4.

Step 4

Before you use MariaDB, you must configure several security options. For this, you use the command:

mysql_secure_installation

It is important for the safety of your VPS to carefully follow the instructions. We recommend making the following choices in this process (highlighted in bold and italic):

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

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

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.

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!


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!

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!

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!

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!

Firewall and external connections

SQL port 3306 is not automatically open in your Firewall (by default, this is UFW in Ubuntu 16.04). For most cases this is certainly not necessary: For example, if you host a WordPress website on your VPS, then, your Apache (or Nginx) server will connect to MariaDB locally via the localhost and there is no need to open the SQL port to the outside world.

If you still have a use case for which you have to make the SQL port available to another server, then depending on your use case, only open the SQL port for your private network, or only allow specific access to the SQL port per IP in UFW with the syntax:

ufw allow from 123.123.123.123 to any port 3306 proto tcp

Replace 123.123.123.123 with the IP address that you want to give access.

In addition, MariaDB 'ties' to the localhost (127.0.0.1) by default and is therefore not accessible externally. To make MariaDB externally accessible, you need to adjust the bind address. To do this, open the MariaDB configuration:

nano 

Replace the line:

bind-address=0.0.0.0

By the line:

bind-address=123.123.123.123

Replace 123.123.123.123 here with the IP address of your VPS to which you want MariaDB to bind. Then restart MariaDB with the command:

systemctl restart mariadb

Managing MariaDB

There are two common options for managing MariaDB: commandline or phpMyAdmin.

phpMyAdmin: For installing and configuring phpMyAdmin, see this tutorial.

Command-line: In this article we explain how to manage MariaDB using an SQL shell via commandline.

For managing databases via PHP we recommend consulting w3schools.com's documentation.

Your MariaDB server is now ready for usage! Would you like to know how you can configure a redundant MariaDB server environment? Then take a look in our redundancy series.

Should you have any questions left regarding this article, do not hesitate to contact our support department. You can reach them via the ‘Contact Us’ button at the bottom of this page.