There can be times when you need to connect to the database from outside your office network.

And, for this to work, we need to enable remote MySQL connection in your DigitalOcean Droplet.

However, due to security risks, web hosts restrict this remote access only to selected IP addresses. And, a single bad step while enabling remote access can harm your databases.

At Bobcares, we help cloud server owners to set up remote MySQL access as part of our Managed Cloud Services.

Today, we’ll see how we set up remote MySQL connection in the DigitalOcean Droplets along with common failure points and their fixes.

How to turn ON Remote MySQL connection in Droplets?

When your web developer needs access to the database from his home, remote MySQL connection comes handy.

But, to defend any hack attempt via remote database access, we need to limit the access to specific IP addresses.

Now, let’s see how our Cloud Support Engineers selectively allow remote MySQL connection in DigitalOcean Droplets.

1. Modify MySQL configuration

By default, MySQL do not allow any connection to the databases from outside network. To enable access, we need to manually allow this in the MySQL configuration file.

For example, in Ubuntu server, our Cloud Specialists edit the file  /etc/mysql/mysql.conf.d/mysqld.cnf and comment out the following line.

bind-address=127.0.0.1

By commenting this line, we remove this restriction. Therefore, MySQL becomes accessible to any IP address.

2. Edit Firewall

As the next step, we need to allow access to the MySQL port 3306. For this, in Droplets, we use the following command.

ufw allow from 192.xx.4x.30 to any port 3306

Here, 192.xx.4x.30 is the address from which we will connect to the databases.

3.  Add MySQL privileges to user

Finally, we need to give privileges for the MySQL user to connect from remote IP address.  This needs to be done from the MySQL command line with this command.

mysql> GRANT ALL ON newbieDB.* TO 'newbie'@'%' IDENTIFIED BY 'rN.BleJ1.mAQI';
Query OK, 0 rows affected, 1 warning (0.00 sec)

This command grant privileges for the database user ‘newbie‘ to newbieDB database from any host. The ‘%’ symbol represents connection from all hosts.

But, this can be a security risk. So, our Cloud Engineers recommends to restrict access from selected IP address using

mysql> GRANT ALL ON newbieDB.* TO 'newbie'@'192.xx.4x.30' IDENTIFIED BY 'rN.BleJ1.mAQI';

Then, to make the permissions work, we execute,

mysql> FLUSH PRIVILEGES;

That’s it, now we have allowed remote MySQL connection in DigitalOcean Droplet.

What can go wrong while enabling remote MySQL and their fixes?

The steps to allow remote MySQL connection appears simple. But, we often get help-desk requests from DigitalOcean customers having trouble connecting to databases.

Let’s now see the top errors and how we fix them.

1.Editing the wrong file

Remote MySQL connection will work only when the edits are done in the correct configuration file.

For instance, in Ubuntu Droplets, there will be conf file in the conf.d and mysql.conf.d folder.  And, things will not work, with wrong file edits.

To fix the problem, our Dedicated Engineers change bind-address in the real conf file at mysql.conf.d folder itself. Then we restart MySQL and the remote connection works fine.

2. IP restrictions

Again, we’ve seen cases where MYSQL bind IP addresses causing problems with remote connection. Commenting out the line that tells MySQL to only accept local connections may not work. So, we need to bind MySQL to a public IP that can accept connections.

So, our Cloud Specialists replace 127.0.0.1 with the Droplet’s Public IPv4 address, and then restart MySQL. And, that fixes remote MySQL connection.

3. Missing MySQL User privileges

Adding necessary privileges to MySQL user is a step that is often forgotten. As a result, remote MySQL connection fails.

To fix it, we add proper privileges to the MySQL user and assign the user to the correct database.

4. MySQL_secure_installation script

Again, we often see problems with remote MySQL connection on Droplets where the script “mysql_secure_installation” is run. This script secures MySQL installation on the Droplet by removing anonymous users, restricting remote root MySQL access, etc. Because of these changes, it can affect the remote access to the databases too.

Here, we fix the connections to the databases by modifying the MySQL configuration.

Do you have trouble connecting to your Database server? We can configure remote MySQL connection for you.]

Conclusion

Allowing remote MySQL connection in DigitalOcean Droplets gives great flexibility in managing databases. Considering security risks, the remote access should be given only to selected IP addresses. Today, we’ve seen how our Cloud Specialists allow remote MySQL access to specific IPs, common problems and their fixes.