How to enable Remote access to your MariaDB/MySQL database

Introduction

MariaDB is a free, open-source and one of the most popular relational database system around the globe. It is a drop-in replacement for MySQL database system. However, the structure and indexes of both database systems are same, this will allow you to switch your database from MySQL to MariaDB without having to alter your applications.

By default, MariaDB is configured to listen on localhost only. So it can be accessed only from local users operating on the same server as the database. In modern web application architecture, most database backends are hosted on their own dedicated server. One of the easiest ways to achieve this is to configure the database to allow remote connections.

Another use-case is if you want to use remote-management software with your database such as MySQL Workbench, HeidiSQL, DBeaver, Sequel Pro or similar.

In this tutorial, we will show you how to configure MariaDB for remote connections using the Webdock dashboard, or manually with shell access on a Ubuntu 18.04 server.

Enabling Remote Access in the Webdock Dashboard

We have tried to make it relatively straightforward to create a database with remote access in Webdock. This approach may be considered insecure, please see note below.

The steps are as follows:

Run the "Enable Remote Mysql Access" script on your server (found in the Script Library)
Create a new database and dabase user which contains the word "remote" in the username. Such as "myremotedatabaseuser"

Please note: When using this method you are opening up port 3306 to the world and anyone who can guess your DB username and password may gain access. If you want to restrict users or your firewall port by e.g. IP - then you need to follow the manual setup method below.

To repeat: Any database user created in the Webdock dashboard with the word "remote" in the username will be granted remote access on % - meaning that user can connect from anywhere. If you have not run our enablement script in order to allow MariaDB to listen on all interfaces and your firewall port is still closed, then this has no security impact.

If you need to grant remote access to an existing database you will need to grant the appropriate privileges to a new or existing user in MySQL using the command line, or alternatively using phpmyadmin - please see instructions on how to achieve this on the command line in the instructions below.

Manual configuration using the command line

Prerequisites

A fresh Webdock cloud Ubuntu instance with LEMP / LAMP installed.
You have shell (SSH) access to your VPS.
A root password is set up on your VPS.

Note : You can refer to the Webdock DNS Guide to manage the DNS records.

Verify MariaDB Server

Before starting, make sure the MariaDB server is running by using the following command:ps -ef | grep -i mysql

You should get the following output:mysql 595 1 0 04:17 ? 00:00:00 /usr/sbin/mysqld root 1350 1337 0 04:22 pts/0 00:00:00 grep --color=auto -i mysql

By default, the MariaDB server is listening on localhost only for security reasons. You can check it with the following command:netstat -ant | grep 3306

In the following output, you should see that the MariaDB server is listening on localhost (127.0.0.1):tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN

Configure MariaDB

By default, the MariaDB is allow connection only from the localhost, all connections from a remote server is denied by default.

First thing you need to do is to configure the MariaDB server to listen on all IP addresses on the system.

You can do it by editing the MariaDB default configuration file /etc/mysql/my.cnf. You can open this file using your favorite text editor:nano /etc/mysql/my.cnf

Change the value of the bind-address from 127.0.0.1 to 0.0.0.0. So that MariaDB server accepts connections on all host IPv4 interfaces.bind-address = 0.0.0.0

Save and close the file when you are finished. Then, restart the MariaDB service to apply the changes:systemctl restart mariadb

You can now verify the MariaDB listening status with the following command:netstat -ant | grep 3306

If everything is fine, you should get the following output:tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

Now, the MariaDB server is setup to listen to all IP addresses.

Note : If your are running MySQL Server instead of MariaDB, the main configuration file is located at /etc/mysql/mysql.conf.d/mysqld.cnf. You can change the bind-address by editing this file.

The remaining steps are all the same.

Grant Access to a User from a Remote System

In this section, we will create a new database named wpdb and user named wpuser, and grant access to the remote system to connect to a database wpdb as user wpuser.

First, login to the MariaDB shell with the following command:mysql -u admin -p

Provide your admin (root) password as shown in the Webdock backend and when you get the prompt create a database and user with the following command:MariaDB [(none)]> CREATE DATABASE wpdb; MariaDB [(none)]> CREATE USER 'wpuser'@'localhost' IDENTIFIED BY 'password';

Next, you will need to grant permissions to the remote system with IP address 208.117.84.50 to connect to a database named wpdb as user wpuser. You can do it with the following command:MariaDB [(none)]> GRANT ALL ON wpdb.* to 'wpuser'@'208.117.84.50' IDENTIFIED BY 'password' WITH GRANT OPTION;

Next, flush the privileges and exit from the MariaDB shell with the following command:MariaDB [(none)]> FLUSH PRIVILEGES; MariaDB [(none)]> EXIT;

A brief explanation of each parameter is shown below:

wpdb : It is the name of the MariaDB database that the user want to connect.
wpuser : It is the name of the MariaDB database user.
208.117.84.50 : It is the IP address of the remote system from which user want to connect.
password : It is the password of the database user.

If you want to grant remote access on all databases for wpuser, run the following command:MariaDB [(none)]> GRANT ALL ON *.* to 'wpuser'@'208.117.84.50' IDENTIFIED BY 'password' WITH GRANT OPTION;

If you want to grant access to all remote IP address on wpdb as a wpuser, use % instead of IP address (208.117.84.50) as shown below:MariaDB [(none)]> GRANT ALL ON wpdb.* to 'wpuser'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

If you want to grant access to all IP addresses in the subnet 208.117.84.0/24 on wpdb as a wpuser, run the following command:MariaDB [(none)]> GRANT ALL ON wpdb.* to 'wpuser'@'208.117.84.%' IDENTIFIED BY 'password' WITH GRANT OPTION;

Configure Firewall

If your MariaDB server is configured with the UFW firewall (which it is by default on all Webdock servers) then you will need to allow traffic on port 3306 from the remote system.

You can grant access to the remote system with IP 208.117.84.50 to connect the port 3306 with the following command:ufw allow from 208.117.84.50 to any port 3306

If you want to grant access from any IP address you would use the following command:ufw allow 3306

Next, reload the firewall with the following command:ufw reload

Once you are finished, you can proceed to the next step.

Test Connection from Remote System

At this point, the MariaDB server is configured to allow connection from the remote system with IP address 208.117.84.50. Now, it's time to test the connection from the client system to the MariaDB server. Here we show how to do this on the command line in Linux, but you can also test this from your desktop (if not on Linux) by utilizing any MySQL remote manager such as the ones listed in the introduction to this article.

First, you will need to install the MariaDB Client package in the remote system. You can install it with the following command:apt-get install mariadb-client -y

Once the installation is completed, connect to the MariaDB server by running the following command on the remote system:mysql -u wpuser -h 45.148.28.101 -p

You will be asked to provide the password of the wpuser as shown below:Enter password:

Once the connection has been established, you should get the following output:Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 50 Server version: 10.4.10-MariaDB-1:10.4.10+maria~bionic-log 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.

You can now list the databases using the following command:MariaDB [(none)]> show databases;

Conclusion

In the above tutorial, we learned how to configure MariaDB for remote connections. we also learned how to grant access on a specific database or all databases to the specific IP address or all IP addresses. We hope this guide helps you to understand how to connect your web application to the database hosted on the remote server and how to secure it properly with IP restrictions.

Source : https://webdock.io/en/docs/how-guides/how-enable-remote-access-your-mariadbmysql-database