How to Install MariaDB on Ubuntu 20.04

Choose a different version or distribution

Introduction

Before we begin talking about how to install MariaDB on Ubuntu 20.04, let’s briefly understand – What is MariaDB?

MariaDB is a popular open-source relational database management system (RDBMS) that offers high-performance, scalability, and reliability. It was developed as a fork of MySQL and is compatible with its predecessor.

MariaDB provides a seamless transition for MySQL users while offering additional features and improvements. With its robust architecture and extensive community support, MariaDB has become a preferred choice for businesses seeking a reliable and efficient database solution.

In this tutorial, you will install MariaDB on Ubuntu 20.04. We will also address some of the FAQs on How to install MariaDB on Ubuntu 20.04.

Advantages

Performance: MariaDB offers enhanced performance with optimized query execution and faster response times.
Scalability: It allows seamless scaling to handle growing data and user demands effectively.
Reliability: MariaDB ensures data integrity and offers features like replication and backup for high availability.
Compatibility: It is compatible with MySQL, making it easy for existing MySQL users to migrate to MariaDB.
Community Support: MariaDB has a large and active community that provides extensive support, updates, and resources.

Prerequisites

One Ubuntu 20.04 server.
A non-root user with sudo privileges and a firewall.

Step 1 – Installing MariaDB

For Ubuntu 20.04, MariaDB version 10.1 is present by default in the APT package repositories.

1) For installing the same, the first step is to update the package index on the server using the following command:

sudo apt update

2) Then initiate the installation process using the following command:

sudo apt install mariadb-server

3) The previous commands shall install MariaDB. However, you will not be prompted to set a password or make any other configuration changes. This is due to the fact that the default configuration leaves your installation of MariaDB insecure, now you need to use the script that the mariadb-server package provides to restrict access to the server and remove unused accounts will make MariaDB more secure.

Step 2 – Configuring MariaDB

1) In order to install MariaDB, the next step is to execute the included security script. This script amends certain default settings to make MariaDB more secure. The following commands will block remote root logins and to delete unutilized database users.

Firstly, execute the below-given security script:

sudo mysql_secure_installation

2) This will take you to a number of prompts where you may implement changes in the security settings of MariaDB. The prompt shall ask you to enter the current database root password. In case you have not set up a database root password, just hit ENTER.

3) After that, the next prompt will inquire if you prefer setting up a database root password. Type N for indicating that you do not want to set up root password and then press ENTER. On the Ubuntu server, the root account for MariaDB should not be changed. Doing the same would let a package update to break the database system by removing access to the administrative account.

In the next step, we will learn how to set up an additional administrative account for password access in case socket authentication is not the appropriate way.

4) After that, you must press Y and then hit ENTER to accept the default setting for all the subsequent prompts. This would ensure any anonymous users do not have access to your database and load the new rules to make sure that MariaDB immediately affects the changes you made.

Step 3 (Optional) – Adjusting User Authentication and Privileges

1) On the Ubuntu system which contains MariaDB 10.1, the root MariaDB user is set to activate with the unix_socket plugin by default rather than with a password. This ensures a greater level of security and usability, however, this may further complicate things when you need to grant admin rights to an external program.

Since the server utilizes the root account for tasks such as log rotation and initiating and ceasing the operation of the server, we recommend you to not change the root account’s authentication details. Doing so in the /etc/mysql/debian.cnf configuration file may work initially, but package updates may overwrite such changes.

As an alternative to modifying the root account, the package maintainers recommend setting up another admin account for password-based access.

For this, we will first set up a new account called admin with the same permissions as the root account and this account would be configured for password authentication. The first step to do this is to open the MariaDB prompt from your terminal using the following command:

sudo mysql

2) Now, create a new user with root privileges and password access and change the username and password that suits you.

GRANT ALL ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

3) Now, flush the privileges to make sure that the privileges are saved and available in the current session.

FLUSH PRIVILEGES;

4) Now, exit the MariaDB shell using the following:

exit

In the next step, we shall test MariaDB.

Step 4 – Testing MariaDB

1) On installation from the default repositories, MariaDB usually starts operating automatically. To verify its installation and operation, check its status using the following command:

sudo systemctl status mariadb

Output similar to the following shall appear:

Output

mariadb.service - MariaDB 10.1.44 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: active (running) since Wed 2020-10-16 16:51:16 UTC; 8min ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
 Main PID: 22559 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 27 (limit: 1152)
   CGroup: /system.slice/mariadb.service
           └─22559 /usr/sbin/mysqld

Oct 16 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22596]: mysql
Oct 16 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22596]: performance_schema
Oct 16 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22596]: Phase 6/7: Checking and upgrading tables
Oct 16 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22596]: Processing databases
Oct 16 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22596]: information_schema
Oct 16 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22596]: performance_schema
Oct 16 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22596]: Phase 7/7: Running 'FLUSH PRIVILEGES'
Oct 16 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22596]: OK
Oct 16 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22658]: Checking for insecure root accounts.
Oct 16 16:51:17 ubuntu-mariadb /etc/mysql/debian-start[22663]: Triggering myisam-recover for all MyISAM tables and aria-recover for all Aria tables

2) In case MariaDB is not operational, you can activate it using the command sudo systemctl start mariadb.

To double-check, you can try connecting to the database using the mysqladmin tool, which is a client that allows you to execute administrative commands. For instance, the following command will connect the user to MariaDB as root and return the version using the Unix socket:

sudo mysqladmin version

Upon entering the command, something similar to the following shall appear:

Output

mysqladmin  Ver 9.1 Distrib 10.1.44-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version      10.1.44-MariaDB-0ubuntu0.20.04.1
Protocol version    10
Connection      Localhost via UNIX socket
UNIX socket     /var/run/mysqld/mysqld.sock
Uptime:         10 min 9 sec

Threads: 1  Questions: 445  Slow queries: 0  Opens: 167  Flush tables: 1  Open tables: 30  Queries per second avg: 0.730

3) In case you have configured a separate administrative user with password authentication, the same operation can be executed using:

mysqladmin -u admin -p version

This essentially means that MariaDB is operational and that your user is able to authenticate successfully.

FAQs to Install MariaDB on Ubuntu 20.04

How do I start and stop MariaDB service on Ubuntu 20.04?

To start the MariaDB service, run: sudo systemctl start mariadb. To stop the service, run: sudo systemctl stop mariadb.

How can I secure my MariaDB installation on Ubuntu 20.04?

MariaDB provides a script called mysql_secure_installation that can be used to secure your installation. Run the following command and follow the prompts: sudo mysql_secure_installation

How do I log in to the MariaDB shell?

You can log in to the MariaDB shell by running: sudo mysql. This will log you in as the root user.

How can I create a new user and grant privileges in MariaDB?

After logging in to the MariaDB shell, you can create a new user and grant privileges using the following commands:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

How can I create a new database in MariaDB?

To create a new database in MariaDB, use the following command in the MariaDB shell: CREATE DATABASE database_name;

How do I import a SQL file into MariaDB?

You can import a SQL file into MariaDB using the following command: mysql -u username -p database_name < file.sql

How do I uninstall MariaDB from Ubuntu 20.04?

To uninstall MariaDB from Ubuntu 20.04, run the following command: sudo apt remove --purge mariadb-server

Conclusion

We hope this detailed guide helped you understand how to install MariaDB on Ubuntu 20.04. To learn more about MariaDB installation, check out the official MariaDB documentation.

If you have any queries, please leave a comment below, and we’ll be happy to respond to them for sure.