At times, server owners need to connect to the databases from outside their office network.
In this case, they must connect to the database using third party software that runs remotely. In other words, this is called remote MySQL connection.
It’s a simple process to enable remote MySQL on Vultr instance. But, a single wrong step can break your databases.
At 1onlyhost, we help server owners enable remote MySQL connection on their Vultr instances as part of our Managed Cloud Services.
Today, we’ll discuss how we setup remote MySQL connection on a Vultr Cloud Compute.
Enable remote MySQL on Vultr – Where it’s needed?
Remote MySQL feature is used when user wants to connect to the database using third party software that doesn’t run on the server. We’ve generally seen the following scenarios where customers have requested to install remote MySQL connection.
- Web developers need to access the database from remote locations.
- For servers hosted with large websites, MySQL consumes large amount of resources. In such cases, setting up remote MySQL connection helps.
Enable remote MySQL on Vultr – How to do it?
It’s always a risk to allow direct access to MySQL remote systems, because the system is exposed to attackers. This can affect the security of all the databases hosted on the server. That’s why, our Support Engineers always restrict the remote MySQL connection from trusted IPs.
Now, let’s see how our Support Experts enable remote MySQL on a Vultr instance.
1) Edit the MySQL configuration file
By default, for security reasons, remote access to the MySQL database server is disabled. So inorder to allow remote access, we must manually modify the MySQL configuration.
For instance, on CentOS servers, we access the MySQL configuration file /etc/my.cnf and modify the line as follows.
bind-address = *
This means that the MySQL service should bind to all IP addresses. Further, we make sure that the skip-networking parameter is removed from the configuration file. This ensures that everyone can make connections to MySQL.
Most importantly, we restart the MySQL service on the Vultr instance to bring these changes into effect. For example, we use the following command to restart MySQL service on an Ubuntu server.
/etc/init.d/mysql restart
2) Add MySQL privileges to the user
Secondly, we must assign proper privileges to the MySQL user to remotely connect to MySQL. For example, see the below command.
mysql> GRANT ALL ON testdb.* TO testuser@'xx.xx.xx.xx' IDENTIFIED BY 'testpassword';
This gives the user testuser all privileges on the database testdb when the user connects from the IP address xx.xx.xx.xx by using the testpassword password.
Further, we run the following command for these permissions to work.
mysql> FLUSH PRIVILEGES;
3) Firewall modifications
Finally, we ensure that there is no firewall that blocks connections to TCP port 3306 of the MySQL server. Sometimes, we specifically need to allow connections from the remote IP address on port 3306. For example, we use the below command to allow an IP address on Ubuntu servers.
ufw allow from xx.xx.xx.xx to any port 3306
This allows connections from the IP address xx.xx.xx.xx on port 3306. Similarly, on CentOS servers, we use the following command.
iptables -A INPUT -i eth0 -s xx.xx.xx.xx -p tcp --destination-port 3306 -j ACCEPT
4) Test the remote connection
Once we are done with the above steps, we let the customer to test the remote MySQL connection. We usually check the remote MySQL connection using the below command.
mysql -u testuser -h xx.xx.xx.xx -p
Replace testuser with the MySQL username and xx.xx.xx.xx is the MySQL server IP address.
In addition to that, we use the below command to test the connectivity to MySQL port 3306.
telnet xx.xx.xx.xx 3306
[Need help in configuring remote MySQL connection on your Vultr VPS? Click here and get one of our Server Experts to fix it for you.]
Enable remote MySQL on Vultr – Common errors
Enabling remote MySQL on Vultr servers look pretty straight forward. However, we’ve seen instances where customers receive errors due to misconfigured settings.
1) Errors due to frequent IP changes
One of the common errors that customers face while using a MySQL remote connection tool is “1045 – access denied for user“. This means that the users are not allowed to access the particular database.
Firstly, our Support Experts ensure that correct password is used for the user managing the databases. Similarly, another reason for this error is that some ISPs may change the public IPs constantly. And, if the new IP address isn’t allowed for remote MySQL connection, it throws the above error. Here, we get the new IP address or IP address range from the customer and allow those IPs to connect remotely.
2) Firewall restrictions
Similarly, the following is another common error raised by the customers while enabling remote MySQL connection.
Can't connect to MySQL server on 'xx.xx.xx.xx'(10061)
The error code 10061 means that the network connection has been refused. This commonly occurs due to the firewall settings on the server. So, our Support Engineers check the server firewall and allow the customer’s IP address for remote MySQL connections if necessary.
[And, do you need a Database Expert to fix your database errors? We can help you here.]
3) Missing MySQL privileges
Similarly, we’ve seen multiple instances where users missed to assign proper privileges to the user. As a result, the remote MySQL connection fails. In such cases, our Database Experts assign proper privileges to the database user and assign the user to the correct database.
Conclusion
In short, it involves a series of 4 simple steps to enable remote MySQL on a Vultr instance. Today, we’ve discussed how our Dedicated Engineers enable remote MySQL on Vultr instance and fix the common errors.
