How to Install MariaDB on Ubuntu 20.04

1. Overview

MariaDB is a popular community-developed fork of MySQL database. Until MariaDB 5.5, MariaDB versions could be used as a drop-in replacement for the equivalent MySQL version. But for later versions, you might have to make minor changes. To know more about compatibility visit MariaDB vs MySQL. In this tutorial, we’re going to install MariaDB on Ubuntu 20.04.

2. Prerequisite

Before you proceed with the installation, please make sure that you have access to ubuntu 20.04 with Sudo access enabled. If you haven’t already installed Ubuntu, please refer to this tutorial  How To Install Ubuntu Server 20.04 to install the latest LTS release of Ubuntu.

3. Add MariaDB repository(Optional)

While MariaDB is available in the Ubuntu default repository, often that is not the latest released version or sometimes you might have to install the older version then available in the repository. If this is the case then please add the repository. The latest repository information can be found on mariadb.com. Get the repository data by selecting your distribution and the version of MariaDB you’d like to install as shown.

Copy the commands and run in the terminal, this adds the repository for your chosen version of MariaDB.

sudo apt-get install -y software-properties-common
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://mirrors.piconets.webwerks.in/mariadb-mirror/repo/10.5/ubuntu focal main'

4. Install MariaDB

After adding repository , Run the following commands in the terminal to install MariaDB.

sudo apt update
sudo apt install -y mariadb-server

5. Securing MariaDB installation

After you’re done installing the package run sudo mysql_secure_installtion and follow the onscreen prompts to secure the installation of MariaDB as shown.

Set root password:

unixbin@mariadb-server:~$ sudo mysql_secure_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
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] Y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!

Remove anonymous users:

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!

Restrict remote root login:

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!

Remove test database:

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!

Reload privilege table:

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!
unixbin@mariadb-server:~$ 

6. Configure firewall

Open the MariaDB TCP port 3306 on your firewall by running the following command in terminal.

sudo ufw allow ssh
sudo ufw allow 3306/tcp
sudo ufw enable

While running the last command sudo ufw enable system present’s with the warning. Accept the warning as we’ve already allowed ssh in the firewall.

unixbin@mariadb-server:~$ sudo ufw enable
Command may disrupt existing ssh connections. Proceed with operation (y|n)? Y
Firewall is active and enabled on system startup
unixbin@mariadb-server:~$ 

7. Connect to database and create user

As we have disabled remote root login, we will have to create a new user so that we can connect remotely to our database. Connect to the database using the following command.

sudo mariadb -u root

Create a new user administrator by running the following SQL statements. Here I have given all permission to the user, but you can fine-tune the permission as necessary.

CREATE USER 'administrator'@'%' IDENTIFIED BY 'Secure12!@';
GRANT ALL ON *.* TO 'administrator'@'%';
FLUSH PRIVILEGES;

Edit /etc/mysql/my.cnf file by running the command as shown.

sudo vim /etc/mysql/my.cnf

Paste the following line to the last of the file.

[mysqld]
skip-networking=0
skip-bind-address

And save the file. To save the file press escape key, type wq! and press enter. After saving the file restart MariaDB by running the following command.

sudo systemctl restart mariadb

8. Connect remotely

Now you can connect to MariaDB server remotely by issuing following command in your terminal.

mysql -u <username> -h <IP> -p

If you created the same user administrator then you can connect to your database instance by entering the following command, please do not forget to replace the given IP with IP of your own MariaDB instance. In the prompt for password enter the password you created earlier.

mysql -u administrator -h 35.224.182.98 -p

9. Conclusion

You have successfully installed MariaDB on Ubuntu 20.04. Please feel free to share your feedback and comments.