Challenge

We have a requirement to see the production database via a remote connection so we can run queries internally for further analysis. This production server has tightened security already in place.

Solution

Get the Public IP address (remote)

Open up the browser and type in the following URL: https://ipchicken.com

Note down the IP address.

Verify the MariaDB server

Log in to the server via the command line.

Ensure the MariaDB server is running by using the following command:

ps -ef | grep -i mysql

You should see the following output:

MariaDB server is by default listening on localhost for security reasons. Check with the following command:

netstat -ant | grep 3306

You should see the following output:

tcp       0      0 127.0.0.1:3306        0.0.0.0:*      LISTEN

Reconfigure MariaDB

You need to configure the MariaDB server to listen to all IP addresses on the system.

Edit via text editor the MariaDB default configuration file on /etc/mysql/mariadb.conf.d/50-server.cnf

Change the value of bind-address from 127.0.0.1 to 0.0.0.0:

bind-address = 0.0.0.0

Save and close it.

Restart the MariaDB service to apply the change:

sudo systemctl restart mariadb

Verify the MariaDB listening status with the following command:

netstat -ant | grep 3306

You should get the following output:

tcp       0      0  0.0.0.0:3306        0.0.0.0:*      LISTEN

Grant Access to the User for a Remote User

Log in to the MariaDB shell with the following command:

mysql -u admin -p

Create a new user:

CREATE USER  'dbuserremote'@'localhost' IDENTIFIED BY 'new-password';

Then, grant permission to the remote system with a Public IP (XXX.XXX.XXX.XXX) address that was collected previously:

GRANT ALL ON targetdatabase.* to 'dbuserremote'@'XXX.XXX.XXX.XXX' IDENTIFIED BY 'new-password' WITH GRANT OPTION;

Flush the privileges:

FLUSH PRIVILEGES;

Lastly, exit from the MariaDB shell:

EXIT;

Reconfigure the Firewall

In this case, we use the UFW firewall. You need to allow traffic for the remote system.

Grant access to the remote system with IP XXX.XXX.XXX.XXX to connect port 3306 with the following command:

sudo ufw allow from XXX.XXX.XXX.XXX to any port 3306

Finally, reload the firewall with the following command:

sudo ufw reload

Test the Connection from the Remote system

For the MariaDB client, we use the HeidiSQL tool which is free software.

Download and install this software.

Run the HeidiSQL application.

On the bottom, click the New button.

Enter the target server for hostname/IP address.

Enter the username and password.

Click the Open button.

If it’s a successful connection, it will open a new window for HeidiSQL and you can access the database from here.

That is it!!!

Clean up the Connection after finish

Typical of the project, usually it will have an end so it is always a good practice for security reasons to close the unnecessary connection once it is complete.

Delete port 3306 from the public IP address:

sudo ufw status numbered

Marked down the number of the port that needs to be deleted:

sudo ufw delete #

Warning: Be careful working with firewalls; take care not to lock yourself out of ssh session when deleting rules.

Reload the firewall with the following command:

sudo ufw reload

You can also further delete the remote user on MariaDB. This is optional.

The End

Congratulation on reaching the end of this article. We hope we have shed some light on outlining how to enable remote access to the MariaDB database.

We write this so that this is not a fixed article. Like in this journey, we learn as we go and re-write some parts, so please keep pinging with this article.

We also would love to hear about how you deal with the situation and what problems you might be facing. Please feel free to comment below this article or jump into our Dewachat and let us know if you have any questions we can answer!

If you like this post, please check out our other related posts:

• Quickly Fix Your WordPress Views After Changing Themes
• Troubleshoot for Root Access Denied on MySqlDump
• Troubleshooting ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)
• A Complete Setup Guide for Contabo VPS Ubuntu 20.04 with preinstalled Webmin + LAMP

To boost your product and service to the broader web community, you can visit our Dewalist classified website – home to 33,000+ active users and 40,000+ one year of active advertising so far. Check it out!

If you love this article or any tech posts and you would like to receive an update of this article or our latest post, please sign up for the form below: