How to Install Mariadb on Ubuntu 20.04 (Step by Step Tutorial)
How to Install Mariadb on Ubuntu 20.04 step by step tutorial. MariaDB is an open-source relational database management system and drop-in replacement for the MySQL database. It is used with LAMP or LEMP stack to store data. MariaDB is continuously gaining popularity due to its performance and faster query response. It supports SQL and allows you to use SQL queries to insert, manipulate, and retrieve data from MariaDB. It comes with a variety of plugins and tools to exaggerate its features. It is used by many big companies including, Alibaba Travels, Walmart, Accenture, UpstageAI, BirdView, Bagelcode, Google, Wikipedia, WordPress and many more.
Table of Contents
In this post, we will show you how to install and use MariaDB on Ubuntu 20.04 server.
Add MariaDB Repository on Ubuntu 20.04
By default, the latest version of MariaDB is not included in the Ubuntu 2.04 default repository. So it is recommended to add the MariaDB official repository to get the latest MariaDB version.
First, install the required dependencies by running the following command:
apt-get install software-properties-common gnupg2 -y
Next, add the MariaDB signing key with the following command:
apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
Once the key is added, you should get the following output:
Executing: /tmp/apt-key-gpghome.2YI4j4VRrN/gpg.1.sh --fetch-keys https://mariadb.org/mariadb_release_signing_key.asc
gpg: requesting key from 'https://mariadb.org/mariadb_release_signing_key.asc'
gpg: key F1656F24C74CD1D8: public key "MariaDB Signing Key <signing-key@mariadb.org>" imported
gpg: Total number processed: 1
gpg: imported: 1
Next, add the MariaDB official repository to APT with the following command:
add-apt-repository 'deb [arch=amd64] http://mariadb.mirror.globo.tech/repo/10.5/ubuntu focal main'
Once the repository is added, update the repository with the following command:
apt-get update -y
Once the MariaDB repository is updated, you can proceed to install the MariaDB.
Install MariaDB on Ubuntu 20.04
Now, you can install the MariaDB server and client package by running the following command:
apt-get install mariadb-server mariadb-client -y
After the installation, start the MariaDB service and enable it to start at system reboot:
systemctl start mariadb
systemctl enable mariadb
Next, you can verify the MariaDB installed version with the following command:
mysqladmin version
You should get the MariaDB version in the following output:
mysqladmin Ver 9.1 Distrib 10.5.12-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Server version 10.5.12-MariaDB-1:10.5.12+maria~focal
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /run/mysqld/mysqld.sock
Uptime: 19 sec
Threads: 1 Questions: 466 Slow queries: 0 Opens: 171 Open tables: 28 Queries per second avg: 24.526
By default, MariaDB listens on port 3306. You can check it by running the following command:
ss -antpl | grep mariadb
You should see the MariaDB listening port in the following output:
LISTEN 0 80 127.0.0.1:3306 0.0.0.0:* users:(("mariadbd",pid=4026,fd=18))
Secure MariaDB Installation for Ubuntu 20.04
After installing the MariaDB server, it is a good idea to secure the MariaDB installation and set a MariaDB root password. You can perform this by executing the mysql_secure_installation script:
mysql_secure_installation
You will be asked to provide your current MariaDB root password if you have set:
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):
If you have not set any password then just press Enter to continue. You will be asked to switch to the unix socket authentication method:
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] n
Type n and hit Enter to skip it. You will be asked to change the MariaDB root password:
... skipping.
You already have your root account protected, so you can safely answer 'n'.
Change the root password? [Y/n] Y
New password:
Re-enter new password:
Set your MariaDB root password and press Enter. You will be asked to remove anonymous users.
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] Y
Type Y and press Enter. You will be asked to disallow root login remotely:
... 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
Type Y and press Enter. You will be asked to remove the test database:
... 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
Type Y and press Enter. You will be asked reload the privilege tables:
... 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
Type Y and press Enter. Once the MariaDB is secured, you should get the following output:
... 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!
Create a Database and Table in MariaDB
In this section, we will show you how to connect to the MariaDB console, create a Mariadb database, create a table and insert the data in a table.
First, you can log in to the MariaDB console using the following command:
mysql -u root -p
You will be asked to provide your MariaDB root password:
Enter password:
Provide your MariaDB root password and press Enter. Once you are connected, you should get into the MariaDB shell as shown below:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 54
Server version: 10.5.12-MariaDB-1:10.5.12+maria~focal 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)]>
To create a database called persons, run the following command:
MariaDB [(none)]> CREATE DATABASE persons;
To list all databases, run the following command:
MariaDB [(none)]> SHOW DATABASES;
You should see all databases in the following output:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| persons |
+--------------------+
Now, switch the database to persons using the following command:
MariaDB [(none)]> USE persons;
Next, create a table called students using the following command:
MariaDB [persons]> CREATE TABLE students (id int, name varchar(20), surname varchar(20));
Next, list your created table using the following command:
MariaDB [persons]> SHOW TABLES;
You should see the following output:
+-------------------+
| Tables_in_persons |
+-------------------+
| students |
+-------------------+
To display the table structure, run the following command:
MariaDB [persons]> DESCRIBE students;
You should see the following output:
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| surname | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
To insert some data in the first row, run the following command:
MariaDB [persons]> INSERT INTO students VALUES (1,"hitesh","jethva");
Now, insert some data in the second row using the following command:
MariaDB [persons]> INSERT INTO students VALUES (2,"jayesh","jethva");
To display your table’s content, run the following command:
MariaDB [persons]> SELECT * FROM students;
You should get the following output:
+------+--------+---------+
| id | name | surname |
+------+--------+---------+
| 1 | hitesh | jethva |
| 2 | jayesh | jethva |
+------+--------+---------+
Backup and Restore MariaDB Database on Ubuntu 20.04
MariaDB provides a mysqldump command-line utility to backup your database and tables.
To back up a database named persons and create a backup file named persons_back.dump, run the following command:
mysqldump -u root -p persons > persons_back.dump
To back up all databases, run the following command:
mysqldump -u root -p --all-databases > alldb_back.dump
To restore a backup from the persons_back.dump backup file, run the following command:
mysql -u root -p persons < persons_back.dump
To restore all databases, run the following command:
mysql -u root -p < alldb_back.dump
Uninstall MariaDB Database
If you want to remove the MariaDB database from your server, run the following command:
apt-get remove mariadb-server --purge
After removing the MariaDB database, some other unwanted dependencies are still persist in your system. You can remove them by running the following command:
apt-get autoremove
Next, remove all package cache with the following command:
apt-get clean
Install MariaDB Server on Ubuntu 20.04 Conclusion
That’s it for now! You have successfully installed the MariaDB database on Ubuntu 20.04 server. You can now use MariaDB as a database backend with LAMP/LEMP stack or other applications.
