Knowledge Base

How to setup a Remote MySQL Database Connection

90% of the times you connect to a MySQL database it is done by using “localhost” as the host inside the connection settings in your PHP files, as most of you host the files and the database on the same server.  However, especially in high traffic websites, a remote MySQL database is needed. In other occasions, you will need to connect from your home any remote location to the database server as well. And that’s when you start asking your self: How can I set up a remote MySQL database connection?

In this tutorial, we will see how to configure a remote MySQL database connection so you can connect remotely from your web server, or any other server, to the remote database server.

Creating a remote MySQL connection is pretty easy, you can do this from both sides: cPanel, or the MySQL terminal if you are using a plain server without any control panel.

What is the recommended software to connect to a remote MYSQL database?

You can connect using several ways, which include but are not limited to:

• PHPMyAdmin
• DBeaver
• MYSQL Workbench
• Heidi SQL
• DataGrip
• SQLECTRON

Allowing the IP address to connect to the MYSQL server

Now let’s see how to allow your IP address to the MySQL server so you can connect without any problems.

If you are connecting from your current ISP assigned IP address, open a terminal and run:

 curl ifconfig.co

Now that you know your current IP, let’s move on to the next step.

• Log into your cPanel interface at http://www.yoursite.com/cpanel
• Click on the Remote MYSQL icon, inside the Database block.
• Enter your IP address or any remote address you want to allow inside the “Add Access Host” block.
• Click “Add Host”.

All done, now the IP address is allowed to connect to the MYSQL server.

You can add as much IP addresses as you need.

For plain based servers, you will have to run:

mysql -u root -p

Enter your MySQL root password. Then, issue this command to grant MySQL remote connection privileges:

GRANT ALL PRIVILEGES ON yourdatabase_name.* TO 'user'@'11.22.33.44' IDENTIFIED BY 'password';
flush privileges;
quit;

Remember to replace “yourdatabase_name”, “user”, “11.22.33.44” and “password” with your real connection details.

Adding Wilcard IPs to MySQL Server

Another cool option you can use are wildcards, these will allow you to add full IP ranges. This is especially useful if you are using a dynamic IP address (which changes frequently within days or hours).

Use the same procedure as before, but instead of adding an IP, just add:

% at the end of the IP range.

For example, if you want to allow access from 11.22.33.*, the idea is to use something like this:

11.22.33.%

This would include up to 255 IP addresses from that range.

Common errors while setting up Remote MySQL database connections

If you did follow all the steps described in this tutorial, and still can’t connect to the remote MYSQL server, you will probably get a few typical errors, see below:

1045 - access denied for user

If you have this error in your screen, it may be caused because of a few reasons like:

Check that your IP address or range, are not filtered by the server firewall

Your remote MySQL connection will not work unless the firewall running on your remote server is also allowing the same IP or IP ranges. If you don’t have full root access control over your remote MYSQL server, ask the server administrators or technical support so they can allow your IP address.

If you are using CSF Firewall, then with simply running these commands it will get fixed:

csf -a 11.22.33.44

Then restart the firewall:

csf -r

Check if your IP has changed

As stated before, if you are using a dynamic IP address, it will likely change within hours or days. If this is your case, you may need to double check if your IP address was updated and no longer allowed inside the MySQL server. Again, you can do this by running:

curl ifconfig.co

If you see a new different IP address, go back to where we started and add your IP address again.

In an ideal world, the best would be for you to use a static IP address, which never changes and will always be allowed.

Double check your MySQL user password

Make sure you are using the right password, you can also reset this from cPanel – MySQL at any time, or by the console with the previous grant command.