In this article, we'll see how you can install Mariadb server on a Linux server running Ubuntu. As of writing this article, the latest LTS version is Ubuntu 24.04. You might already know that Mariadb is a drop-in replacement for MySQL. So most of the things mentioned here will be applicable for MySQL as well.

Table of Contents

Video Tutorial

By playing this video, you agree to YouTube's Terms
Watch on YouTube →

Installing Mariadb-server

Mariadb server is available in the apt repository. So you can install it from there. Before that, make sure you update and upgrade the os:

sudo apt update && sudo apt upgrade

Now run the following command to install MariaDB:

sudo apt install mariadb-server

Once it is installed, the mariadb-server service needs to be started and enabled in systemd:

sudo systemctl start mariadb && sudo systemctl enable mariadb

The second command (enable) is to ensure that MariaDB service gets started at system boot, so that there is no need to manually start it every time.

Now you can check the status with:

sudo systemctl status mariadb

If everything is ok, you should see something like 'Status: active (running)'. Otherwise you might need to make sure no other service is conflicting with MariaDB. The default port for MariaDB/mysql is 3306. So if some other application has already taken up that port, you might want to disable that before starting MariaDB.

Securing the installation

Once MariaDB is installed and enabled, you need to tighten the security, especially for production servers. This includes a couple of things:

  • setting a password for the root user, or
  • switching the root user to unix_socket authentication
  • removing anonymous user
  • removing test database
  • disabling remote connections

There is a built-in script for that. You can invoke it by running the following command:

mysql_secure_installation

or you can also get the same by:

mariadb-secure-installation

Both - mysql_secure_installation and mariadb-secure-installation are symbolic links.

It opens up a questionnaire. You can answer them one by one.

Here is how it looked in my installation:

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):

The first question is to enter the root user's password. Since it is a fresh installation, the root user's password was not set. So just press enter to skip to the next question.

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] Y

The next question asks whether you want to switch the root user's authentication method to unix_socket authentication.By default, it will be mysql_native_password.

But for some reason, it did not work for me. Even if I pressed yes (Y) to this, it remained mysql_native_password when I checked afterwards. So I had to manually switch later (see the section about that).

Enabled successfully!
Reloading privilege tables..
 ... Success!

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n]

Whether to change/set the root user's password - since we're going to setup unix_socket authentication, there is no need to set a password now. So, it's okay to answer 'n' for this question.

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

Remove anonymous users, if any.

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

Disallow remote login from the internet. This makes sure the user can only be accessed from localhost.

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

Remove test databases, if any.

 - 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

Finally, reload the privileges table.

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

The mysql_secure_installtion step is supposed to strengthen the security of the server. But in my experiece, it is a little problematic. I don't know if something is wrong with my setup, but after searching for a while, I found other people are also experiencing somewhat related issues

Here are the issues I faced:

  • root user not switching to unix_socket in step 2
  • even if password is set, it is being ignored

So I prefer doing these steps manually.

Switching root user to unix_socket authentication

First of all, login to the mysql prompt by running the command mysql.

mysql

or if you are logged-in to the operating system as a non-root sudo user:

sudo mysql

There won't be any password prompt as we haven't setup any. Otherwise, if you had setup a password in the mysql_secure_installation, you might want to run sudo mysql -u root -p, which prompts you to enter the password.

First of all, let's list the users:

SELECT User,Host,plugin FROM mysql.user;

It should display something like:

+-------------+-----------+-----------------------+
| User        | Host      | plugin                |
+-------------+-----------+-----------------------+
| mariadb.sys | localhost | mysql_native_password |
| root        | localhost | mysql_native_password |
| mysql       | localhost | mysql_native_password |
+-------------+-----------+-----------------------+
3 rows in set (0.001 sec)

Notice that under the plugin column, for all users including the root, it is mysql_native_password, which means they're using password authentication.

So let's alter the root user to socket authentication.

ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket;

Run the SELECT command again, and you should get:

+-------------+-----------+-----------------------+
| User        | Host      | plugin                |
+-------------+-----------+-----------------------+
| mariadb.sys | localhost | mysql_native_password |
| root        | localhost | unix_socket           |
| mysql       | localhost | mysql_native_password |
+-------------+-----------+-----------------------+
3 rows in set (0.001 sec)

Difference between unix_socket auth and password auth

When the unix_socket plugin is enabled for a user, the user is tied to the operating system user. So the current operating system username should match the mysql/mariadb username you're trying to access.

Password is not used, and is irrelevant.

To access the MariaDB root user, you should be logged into Ubuntu (in our case) as the root user. Or you should be logged in as a sudo user.

Since there is no password, you can directly run the command mysql or sudo mysql (for root user), and you can access the corresponding MariaDB user.

On the otherhand, if password authentication is enabled for user, there is no connection with the OS user. So, you can only access it adding the username and password options - mysql -u root -p or mysql -uroot -pmypass.

unix_socket auth is believed to be safer, as there is no room for leaking mysql user password. So these days, it is common to configure the root user with that.

Then for other uses, like accessing databases, create a normal password-based user.

Deleting anonymous users (if any)

There weren't any anonymous users in my installation. You can verify that by running the command:

SELECT User,Host FROM mysql.user WHERE User='';

If it returns an empty set, then there are no anonymous users. Otherwise, if there are, you can delete them with these commands:

DROP USER ''@'localhost';
DROP USER ''@'127.0.0.1';
DROP USER ''@'%';

the last one ''@'%' is for any anonymous users with a wildcard host

Creating a new database

While you're logged in to the MariaDB prompt, you can run the following command to view the databases that currently exists:

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.001 sec)

Next, to create a new database named mysite:

CREATE DATABASE mysite;

Show databases again, and you'll get:

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

Creating a new user with password authentication

The new database is created with the root user. But your app/website won't be able to access it. So let's create a dedicated user with password credentials.

CREATE USER 'abhinav'@'localhost' IDENTIFIED BY 'pa55w0rd';

Granting permissions to the new user

Once the user has been created, it should get privileges to access the database. Privileges can be granted only if the current user has the power to do that. Root user has that. So:

GRANT ALL PRIVILEGES ON mysite.* TO 'abhinav'@'localhost';

Here we're granting all privileges to all the tables in mysite, including SELECT, UPDATE, DELETE, etc.

Now run the FLUSH command for the changes to take effect:

FLUSH PRIVILEGES;

Finally, you can exit from the MariaDB prompt:

exit;

Blocking remote connections

Direct remote connections must be blocked by default. But still we can ensure that in a few ways.

One way is to use the netstat tool. You can install it by:

sudo apt install net-tools

Then run the following to view all the active internet connections:

netstat -tuln

You'll see something like the following:

root@cnvps:~# netstat -tuln
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 127.0.0.54:53           0.0.0.0:*               LISTEN     
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN     
tcp        0      0 127.0.0.53:53           0.0.0.0:*               LISTEN     
tcp6       0      0 :::22                   :::*                    LISTEN     
udp        0      0 127.0.0.54:53           0.0.0.0:*                          
udp        0      0 127.0.0.53:53           0.0.0.0:*

Under the Local Address column, you can find 127.0.0.1:3306, which means MariaDB can only be accessed from localhost(127.0.0.1 is the loopback ip address).

Another way is to actually look at the mysql configuration under /etc/mysql/. The default configuration file will be /etc/mysql/my.cnf. In that file, look for a line that starts with bind-address. Its value should be 127.0.0.1.

Sometimes it will be in a subconfig file, included from the main my.cnf file. Mine was inside /etc/mysql/mariadb.conf.d/50-server.cnf. In that case, you can perform a search to easily find out the bind-address line:

find /etc/mysql/ -name '*.cnf' -exec grep -i 'bind-address' {} +;

NB: To connect to the database with a local desktop client like DBeaver, you need to do it via an SSH tunnel, which gets you into the server. From there it can safely access the database as localhost.

Conclusion

With all these steps, the mariadb-server should up and running.