How to Install MariaDB on Ubuntu [Setup & Configuration]

Install MariaDB on Ubuntu

In this blog, you will learn to install MariaDB on Ubuntu 20.04 server using step-by-step guides including important MariaDB configurations.

Install MariaDB on Ubuntu 20.04

Follow the steps given below for installing and configuring MariaDB.

Step 1: Install the MariaDB server & Client

Update the server

sudo apt update -y

As a recommended practice, we will add the official MariaDB apt repository using the following script.

curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash

Now, install MariaDB server and client.

sudo apt-get install mariadb-server mariadb-clien -y

Step 2: Validate MariaDB Installtion

First, let’s check the installed version of MariaDB using the following command.

mysql -V

Now, let’s check MariaDB Service Status.

sudo systemctl status mariadb

You should get an active status output as shown below.

image 28

Step 3: Secure MariaDB

The next step is to secure the MariaDB server by removing all the insecure configurations. Execute the following command to start the configuration.

sudo mysql_secure_installation

You will be prompted with six questions. Choose options as shown below.

  1. Enter current password for root (enter for none): Press enter as there is no password by default.
  2. Set root password? [Y/n]: Select Y and enter a new password.
  3. Remove anonymous users? [Y/n]: Select Y
  4. Disallow root login remotely? [Y/n]: Enter Y
  5. Remove the test database and access to it? [Y/n]: Enter Y
  6. Reload privilege tables now? [Y/n]: Enter Y

Step 4: Connect to MariaDB

Now that we have secured MariaDB and set a root password, let’s validate the configurations by connecting to MariaDB.

Execute the following command to connect to MariaDB. When prompted, enter the root password you set up in the previous step.

mysql -u root -p

You should be able to connect as shown below.

MariaDB Remote Connection Configuration

mariaDB.drawio

By default, MariaDB accepts connections only on the Unix socket and localhost. You can verify it by listing the TCP connection

ss -nlt
MariaDB default localhost connection.

If you try to connect MariaDB from a remote host, you will get the following error

ERROR 2002 (HY000): Can't connect to server on '192.168.6.6' (115)

To enable remote connection, open the 50-server.cnf file. You can find this file in the /etc/mysql/mariadb.conf.d/ location.

sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf

Find the bind-address parameter and replace the bind-address value from 127.0.0.1 to 0.0.0.0 as shown below.

bind-address            = 0.0.0.0
MariaDB remote connection bind address

Restart the MariaDB server for the changes to take effect.

sudo systemctl restart mariadb

Now, if you check the TCP connections, you will see that the MariaDB server is listening on all the interfaces.

MariaDB server listening on all interfaces.

MariaDB Database Access From Remote Clients

Even though the MariaDB server accepts connections from remote hosts, you need to allow host-specific remote database access. If not, you will get the following error.

ERROR 1130 (HY000): Host '' is not allowed to connect to this MariaDB server

Now we need to create a database and a user to allow remote connections. First login using the root user and password.

mysql -u root -p

Create a database using the following command. Replace appdb with a database name of your choice.

CREATE DATABASE appdb;

Create a database user named dbadmin and grant all the privileges to the appdb database from the remote host 192.168.6.6. Replace 192.168.6.6 with your client host IP address from where you need to connect to the Database.

GRANT ALL ON appdb.* to 'dbadmin'@'192.168.6.6' IDENTIFIED BY 'myPassword';

If you want to allow DB connection from anyhost for a user you need to use % wildcard instead of host address as shown below.

GRANT ALL ON appdb.* to 'user'@'%' IDENTIFIED BY 'myPassword';

In MariaDB, a user is identified along with the host address. You can list all the users using the following SQL query.

SELECT User, Host FROM mysql.user;

Now, try connecting to the dbadmin MariaDB database from the remote host.

mysql -u dbadmin -p -h 192.168.10.5

MariaDB Important Configurations

The following table contains all the important MariaDB configurations.

MariaDB ConfigurationDetails
MariaDB default port3306
TCP Socket file/run/mysqld/mysqld.sock
MariaDB Config Files location/etc/mysql/mariadb.conf.d
MariaDB Server Configuration file /etc/mysql/mariadb.conf.d/50-server.cnf

MariaDB Server Installation FAQs

How to enable remote access on MariaDB?

To enable remote access, you need to replace the bind address with 0.0.0.0. Also, you need to add a user with the remote host details or a wildcard entry to access the database remotely.

Conclusion

This guide looked at the steps to install MariaDB on a Ubuntu server.

As a DevOps engineer, it is essential to know the basic administration and configurations involved in a Database.

If you are starting your DevOps engineer journey, look at my comprehensive guide to becoming a DevOps engineer.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like