To enable remote connections to MySQL and secure them on Ubuntu 16.04

There is only one method to allow remote connections on MariaDB. However, there are many ways to secure these connections to your MariaDB server. Each has its own advantages and complexity. In this case, we will start with a clean Virtual Machine or Virtual Private Server, and by the end of this tutorial, we will have a MariaDB server that allows remote access and is protected by a Firewall.

To follow this guide, you will need a clean Ubuntu 16.04 server with root access. We need root access to the server as we will install MariaDB and update the configuration file to allow remote connections.

So, let’s start with the actual tutorial.

Install MariaDB Server

It only takes a few commands to install the MariaDB server on your VM/VPS. Let’s update our repositories and install the MariaDB server on our machine. To complete this task, execute the commands below as root:

sudo apt-get update
sudo apt-get install mariadb-server mariadb-client -y
sudo mysql_secure_installation

The first command will update all the repositories, the second one will install the actual MariaDB server and MariaDB client so that we can work on our databases. In the third command, you will be asked a few questions. Follow the instructions below to answer with yes or no.

Enter password for user root: YOUR_PASSWORD
Press y/Y for Yes and any other key for No: N
Change the password for root ? N
Remove anonymous users? Y
Disallow root login remotely? Y
Remove test database and access to it? Y
Reload privilege tables? Y

Once done, our MariaDB server is installed and secured locally. Now, we have to enable remote access by updating our configuration file.

Allow MariaDB remote connections

Finally, it’s time to update our MariaDB configuration file and bind the MariaDB server to the public IP address. To complete this task, execute the following command in the terminal to open the MariaDB configuration file for editing.

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

Now, you will see a configuration file with many directives. The directive we are looking for is bind-address. To find a line containing the text we want, press CTRL+W and type bind-address, then hit the enter key. You will be automatically navigated to a line containing the following text.

bind-address = 127.0.0.1

Replace the local IP address with the Public IPv4 address of your server. For example, let’s say it is 1.2.3.4. Then, the correct configuration should look like this:

bind-address = 1.2.3.4

After updating the IP address in this directive, press CTRL+X followed by Y and Enter. It will save the file with the changes. However, we still have to restart the MariaDB server to apply the changes.

To restart the MariaDB server, execute the following command.

sudo service mysql restart

Once done, we have to create a MariaDB user that has access from the MariaDB client from which you want to connect to your MariaDB server remotely.

Allow MariaDB remote connections for MariaDB users

MariaDB is quite secure by default. It authenticates users not only based on their username and password but also on their host. This means that the IP address of the MariaDB client from which a user is trying to connect must be set as the host when creating a user. Execute the following command to log in to the MariaDB command line interface.

mysql -uroot -p;

You will be prompted for the root password. Enter the root password you have set so that you can access the MariaDB command line interface. Once you are in, execute the following commands to create a user with access from a remote host.

CREATE USER ‘USERNAME‘@’REMOTE_IP‘ IDENTIFIED BY ‘PASSWORD‘;
CREATE DATABASE ‘EXAMPLE‘;
GRANT ALL ON EXAMPLE.* TO ‘USERNAME‘@’REMOTE_IP‘;
FLUSH PRIVILEGES;

Do not forget to replace the variables with your own values. You can set any username, password, database name, and Remote IP address you want.

We executed these commands to create a user with access from a specific IP address and a strong password. Then, we created an example database and granted all privileges on the example database to our user.

Congratulations! Now, you can access the MariaDB server from a remote server using the following command, but only from the remote IP you entered while creating a user. Again, do not forget to replace the IP address with your MariaDB server’s IP address.

mysql -uroot -p -hMYSQL_SERVER_IP;

You will be prompted for a password. With the correct combination of your IP, username, and password, you will be able to log in.

Secure MariaDB remote connections

As we know, MariaDB is already a secure database management system. But we want to further secure it using a Firewall. This means that only requests from trusted IP addresses will reach MariaDB, and other requests will be denied as soon as they reach the server.

So, we will use UFW to deny requests coming on the MariaDB port (3306) except from the Remote IP we trust. Thus, every time you create a user with access from a different Remote IP, you have to add a rule in your UFW firewall configuration.

First of all, we have to allow access on the SSH (22) port so that we can access the server via SSH. Then, we have to allow access to the MariaDB (3306) port only to specific IP addresses. This means that we have to deny all incoming connections first and then allow the connections as required.

Execute the following command to deny all incoming requests, allow all outgoing requests, and allow requests on the SSH (22) port.

sudo ufw default deny incoming
sudo ufw default allow outgoing
sudo ufw allow ssh

Now, we also have to allow incoming requests from the REMOTE_IP we entered while creating a user in MariaDB. To allow requests from the REMOTE_IP, execute the following command.

sudo ufw insert 1 allow from REMOTE_IP to any port 3306

Do not forget to replace the REMOTE_IP with the IP address from which you want to access the MariaDB server. Once done, execute the following command to enable the firewall.

sudo ufw enable

It will show you that the firewall is active and enabled on system startup. This means that our remote MariaDB connections are now secure, as our server will only accept requests sent from trusted IP addresses with the correct username and password.

Conclusion

This is how you can allow MariaDB remote connections to run securely. Using this method, you can separate your WordPress host server and a database server so that all the web requests and processing are done on the application server while the database queries are executed on the database server.

You can also perform master-slave replication in MariaDB. With replication, you can make your application use two MariaDB servers instead of one to read and write data, which can divide the load between two database servers. If you are facing any issues following this guide, please let us know. We are happy to help.