These days many web applications have files and databases lying on separate servers.

Due to security restrictions, database server allows connection only from web servers. And, if your website developer needs to check the database, MySQL remote access comes handy.

But, enabling remote MySQL access in Linode may not always work correctly due to configuration errors.

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

Today, we’ll see how our Support Engineers setup remote MySQL access in Linode and fix common errors.

Risks in enabling remote MySQL access

Running MySQL on a separate Linode server allows to allocate more resource exclusively for databases. Therefore, at some point of time, there will be a need to set up remote access on the server.

But, this has to be done considering the security of the database server. Allowing public access on MySQL server can make your Linode server a target for attack.  This can affect all the databases running on the server. That’s why, our Support Engineers always restrict remote MySQL access to selected trusted IP addresses. Again, the connection will be limited to specific databases too.

How we setup remote MySQL access in Linode?

Now, let’s have a look on how we enable remote MySQL access in Linode server.

The process starts with the setup of database server. Some servers have MySQL while some may have MariaDB. For example, in CentOS7 servers,  MySQL is no longer in the repositories and MariaDB has become the default database system.

Here, our Dedicated Engineers fetch the required packages and install MySQL on the server. Now we need to enable remote MySQL access.

1. MySQL IP address binding

As the first step, we need to ensure that MySQL is properly listening on port 3306 of the server IP address. This can be checked by looking the service ports listening on the server.

For example, our Support Engineers use the netstat command to confirm MySQL binding.

In a CentOS7 server, the results will look like :

[root@li4xx-241 ~]# netstat -lpan | grep :3306
tcp6       0      0 :::3306                 :::*                    LISTEN      14763/mysqld

2. Firewall modification

By default, most servers deny connections on MySQL port. Usually, this restriction happens in the server firewall.  So, to allow remote access, we need to allow port 3306 traffic from trusted IP address.

For example, to allow port 3306 traffic on a CentOS7 server, our Support Engineers modify Firewalld using the below commands:

firewall-cmd --permanent --zone=trusted --add-source=192.0.x.x
firewall-cmd --permanent --zone=trusted --add-port=3306/tcp
firewall-cmd --reload

This allows MySQL connection from IP 192.0.x.x.

3. MySQL user and privileges

Last, but not the least, for remote MySQL access to work, we need to set MySQL users and privileges. We need to add the MySQL user to the database too.

To give remote access to user ‘mydbuser’ to database ‘mydb’ from IP address 61.xx.xx.20, our Security Engineers use the following command in MySQL prompt of the Linode server:

mysql> grant all on mydb.* to 'mydbuser'@'61.xx.xx.20' identified by 'ww4ICED0XsUw.';
Query OK, 0 rows affected (0.00 sec)

After this, the remote MySQL connection to Linode server works correctly.

Common errors with Linode remote MySQL access and fixes

Although, enabling remote MySQL involves only basic steps, we often see customers reporting trouble connecting to database servers. Let’s have a quick look at them.

1. Wrong firewall

Recently, a Linode customer reported problems when connecting remotely. The error said:

Can't connect to MySQL server on 'xx.xx.xx.xx'(10061)

Here, our Support Engineers analyzed the server logs and found that the problem was with the server firewall. The customer’s IP address was not specifically allowed in the firewall. Modifying the firewall fixed the problem and connection worked fine.

2. Wrong MySQL access host

Similarly, problems can happen when proper MySQL access host is not set up.

Here, the remote IP address was already white-listed in the server firewall. But, remote MySQL connection reported the error:

HHost 'xyz.domain' is not allowed to connect to this MySQL serverConnection closed by foreign host.

To fix, our Dedicated Engineers had to configure MySQL access host for the database user from the MySQL prompt of the Linode server.

Conclusion

In short, enabling remote MySQL access in Linode servers involves modifying MySQL configuration, enabling IP address in firewall, etc. Today, we’ve seen how our Support Engineers set up remote MySQL access and fix common MySQL access problems in Linode server.