HowTo: Allow Remote access to MySQL/MariaDB Instance

by

In the previous article we installed MariaDB on a Raspberry Pi OS which is a small piece of hardware that can barely handle the SQL instance, so you might choose to host a application on another hardware OR sometimes you just want to allow connections from another system to your MySQL instance. So, in this article we will learn how to allow external or remote access to an instance of MySQL or MariaDB server. Procedure to allow it on either of the server technologies is the same as they use the same configuration as well as the same configuration file.

Step 1: Editing the configuration file

First of all we need to edit the configuration file for the server instance which can be found at ( /etc/mysql/my.cnf ) by using the following command:

sudo nano /etc/mysql/my.cnf
Step 2: Securing Data Integrity

This one is necessary to make sure if multiple applications or multiple application instances are access the same data base at the same time, then the external locking should be turn on to maintain data integrity. Hence, we comment the skip-external-locking line in the config file before we go ahead and allow remote access to the sql server.

#skip-external-locking
Step 3: Change the Bind Address

Now we update the bind address on the my configuration file to point to it’s own ip address by setting it’s value to 0.0.0.0 . This will allow us to connect to the server instance using the system IP address.

bind-address      = 0.0.0.0

Once you have set the value, save the file and close it.

Step 4: Restart the Server

The next step is to restart the server instance, in case of MySQL use teh following command:

sudo systemctl restart mysql

OR if it’s MariaDB use the followng:

sudo systemctl restart mariadb

Once the process has been restart, just make sure you open the port 3306 on your firewall to allow connection.

Step 5: Update the users

Now you need to update the users to be able to remotely access. You can use the following query to update a user’s password and allow them to access remotely due to the Host update.

UPDATE user SET Host = '%', authentication_string=PASSWORD('<password>'), plugin='mysql_native_password' WHERE User='<username>';
FLUSH PRIVILEGES;

Once done, test connecting to your server instance remotely from another system.

Now, you are ready to allow connections to your MySQL or MariaDB instance!

Leave a Comment