How to Install MariaDB on Ubuntu & Create Database

MariaDB server is one of the most popular and open-source relational database servers in the world. It is a backward-compatible, and replacement for MySQL. In this guide, you will learn how to install the MariaDB server on Ubuntu 20.04.

First of all, you need to log in as sudo user. Then, use the following command to install the MariaDB server:

> sudo apt install mariadb-server

Next, you can use the following command to check if the MariaDB service is enabled at system startup.

> sudo systemctl status mariadb

If it is not enabled at system startup, then you can enable it using the command:

> sudo systemctl enable mariadb

Also, you can start the service with this command:

> sudo systemctl start mariadb

Securing MariaDB Server

After installation, to secure it, you can run the following command:

> sudo mysql_secure_installation

In order to log into MariaDB to secure it, you’ll need to enter the current password for the root user. If you’ve just installed the MariaDB server, and you haven’t set the root password yet, the password will be blank, so you can 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 authorization.

You already have a root password set, so you can safely answer 'n' or you can press 'y' to change it.

Change the root password? [Y/n] n
 ... skipping.

By default, the installation has an anonymous user which allows anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing. You can remove them before moving into a production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, the root should only be allowed to connect from “localhost”. This ensures that someone cannot guess 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 going into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Now, reload the privilege tables so, the changes can take place their 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.

Creating Database & Assign Users

In order to log into the MariaDB server to run SQL commands, you can run the command:

> sudo mysql

If the above command doesn’t work, then you can run this command instead:

> sudo mysql -u root -p

Here, you will need to enter the MySQL root password. Once, you are logged into mariadb, you can run the following command to create a database with a character set (optionally).

> mariadb> CREATE DATABASE exampledb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Here, the database name is “exampledb”. Now, to show all the databases, you can run this command:

> mariadb> show databases;

To create a new database user and grant all permissions of this database (“exampledb”) to that user, you can use the following command:

> mariadb> GRANT ALL PRIVILEGES ON exampledb.* TO "exampledbuser"@"localhost" IDENTIFIED BY "your_password";

Here, “exampledbuser” is the new user, and the password for that user is “your_password”. Also, you can assign existing user “testdbuser” to this new database “exampledb” using the command:

> mariadb> GRANT ALL PRIVILEGES ON exampledb.* TO "testdbuser"@"localhost";

Now, you will need to flush the privileges for changes to take an effect.

> mariadb> FLUSH PRIVILEGES;

Finally, you can exit from mariadb mode using the “exit” command:

> mariadb> exit;

In conclusion, the MariaDB server is a widely-used open-source relational database server and a popular replacement for MySQL. By following the steps outlined in this guide, you can easily install it and start building robust and scalable applications.