Overview

The MariaDB version in the default Ubuntu repository is not the latest one. To see the default version, you can use the following commands:

apt show mariadb-server

Output:

Package: mariadb-server
Version: 1:10.6.18-0ubuntu0.22.04.1
Priority: optional
Section: universe/database
Source: mariadb-10.6
Origin: Ubuntu

As you can see, the default MariaDB version on Ubuntu 20.04 is the version 10, however the latest one is version 11. In order to install the latest one, you need to add MariaDB APT repository first before installing

Follow these steps to install the latest version of MariaDB.

Installing MariaDB

Run system update

Before installing the mariadb, patch the system to the latest, ensuring your system is updated.

sudo apt update && apt upgrade -y

Add MariaDB repository

  • Go to https://mariadb.org/download/?t=repo-config
  • On the server repository section, choose the following:
    • Distribution: version of your ubuntu server
    • MariaDB Version: version of your mariadb you wan to install
    • Mirror: the repository closest to your location
  • Once done, you will see the instruction and its Repository key.
  • Add the MariaDB repository to your system by adding the following:
sudo apt-get install apt-transport-https curl
sudo mkdir -p /etc/apt/keyrings
sudo curl -o /etc/apt/keyrings/mariadb-keyring.pgp 'https://mariadb.org/mariadb_release_signing_key.pgp'
  • Once the key is imported, create a mariadb.sources file under /etc/apt/sources.list.d then copy and paste the following to that file:
# MariaDB 11.4 repository list - created 2025-01-15 07:50 UTC
# https://mariadb.org/download/
X-Repolib-Name: MariaDB
Types: deb
# deb.mariadb.org is a dynamic mirror if your preferred mirror goes offline. See https://mariadb.org/mirrorbits/ for details.
# URIs: https://deb.mariadb.org/11.4/ubuntu
URIs: https://sg-mirrors.vhost.vn/mariadb/repo/11.4/ubuntu
Suites: noble
Components: main main/debug
Signed-By: /etc/apt/keyrings/mariadb-keyring.pgp
  • Check the version again.
apt show mariadb-server
  • As you can see, you should now see the version 11 in the output as the following:
Package: mariadb-server
Version: 1:11.4.4+maria~ubu2204
Priority: optional
Section: database
Source: mariadb

Install the latest MariaDB version

  • Run the following to re-update the repository and install the version 11 mariadb server.
sudo apt-get update
sudo apt-get install mariadb-server

Verify the installed mariadb version

  • Check installed version
mariadb -V

Output:

mariadb from 11.4.4-MariaDB, client 15.2 for debian-linux-gnu (x86_64) using  EditLine wrapper
  • Check MariaDB status
systemctl status mariadb

Output:

mariadb.service - MariaDB 11.4.4 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
    Drop-In: /etc/systemd/system/mariadb.service.d
             └─migrated-from-my.cnf-settings.conf
     Active: active (running) since Wed 2025-01-15 07:57:40 UTC; 7min ago

Service command:

Function Command
Start Service sudo systemctl start mariadb
Restart Service sudo systemctl restart mariadb
Stop service sudo systemctl stop mariadb
Enable MariaDB sudo systemctl enable mariadb
Disable MariaDB sudo systemctl disable mariadb

Securing MariaDB 11

MariaDB includes a default security script called mariadb-secure-installation, that can be used to improve the security of your MariaDB installation by:

  • Setting a password for root accounts (if necessary).
  • Disabling remote root access to the databases.
  • Removing anonymous user accounts.
  • Deleting the test database, which can be accessed by anonymous users by default.

To run the script, just execute the following command:

sudo mariadb-secure-installation

Output:

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

MariaDB Authentication

When you install MariaDB, two secure accounts are created: root@localhost and mysql@localhost. These accounts use either the unix_socket and the mysql_native_password authentication plugins.

unix_socket authentication plugin lets the system’s root to login as root@locahost to MariaDB database without a password.

If the unix_socket authentication plugin is active, while being a root user, you can simply login by running either of the commands below:

mariadb
mariadb -u root
sudo mariadb
sudo mariadb -u root

Even if you run, mariadb -u root -p and press ENTER with a blank password, you will still login. You can also login as mysql user sudo -u mysql mariadb

Enable MariaDB password Authentication

The two privileged accounts, root@localhost and mysql@localhost, allow anyone with access to them to log in to MariaDB. It is not recommended to leave such user accounts with no password and all privileges, as this can pose a significant security risk or vulnerability to your database.

The two accounts above uses mysql_native_password plugin by default;

sudo mariadb
MariaDB [(none)]> select user,Host,plugin from mysql.user;
+-------------+-----------+-----------------------+
| User        | Host      | plugin                |
+-------------+-----------+-----------------------+
| mariadb.sys | localhost | mysql_native_password |
| root        | localhost | mysql_native_password |
| mysql       | localhost | mysql_native_password |
+-------------+-----------+-----------------------+
3 rows in set (0.015 sec)

The mysql_native_password plugin is used as a failover for the unix_socket plugin. Even if you have setup a password using MariaDB secure installation script, you can still login as those accounts without a password.

show grants for root@localhost;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING '*9C6C35530EE4427B07D2FA4F9E119C3915D18940' OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                                                             |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
show grants for mysql@localhost;
+------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for mysql@localhost                                                                                                               |
+------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `mysql`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'mysql'@'localhost' WITH GRANT OPTION                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

As you can see above, the root user’s password has already been configured using the mysql_secure_installation command. However, the mysql user account currently has an invalid password. Therefore, it is highly recomended to enable password authentication for increase the additional security to your database.

ALTER USER root@localhost identified by 'RootIsTheStrongestPassword123';
ALTER USER mysql@localhost identified by 'MySQListheStrongestPassword123';
flush privileges;
quit

This re-enables the MariaDB password authentication and hence, you can now login as non root or non sudo user.

sudo mariadb

Sample output;

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Provide a password to be able to login;

mariadb -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 61
Server version: 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 mariadb.org binary distribution

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

Set Native Password Authentication Method as Default

When you set the password above, it completely disables unix_socket authentication plugin and instead use the msqyl_native_password authentication method;

show grants for root@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*73E1DDB4DA8B34D3080B082A8DFC863A56285DD4' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)
show grants for mysql@localhost;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for mysql@localhost                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `mysql`@`localhost` IDENTIFIED BY PASSWORD '*4C8C1832BBF14A360C2F70EA14CBD912AE0AF280' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'mysql'@'localhost' WITH GRANT OPTION                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

This concludes our guide on installing MariaDB 11 on Ubuntu 24.04.

Thank you for following along!