A remote MySQL connection can often be done to get a program like MySQL client that doesn’t run on the server connected to your database. This tutorial will walk you through the simple steps to connect to a remote MySQL server with a collection of practical suggestions included.
Preparatory Work to Connect Remotely
To get yourself well-prepared in advance, you should have an IP address that can access the database. If you’re connecting to the database from the current connection, then you can get to know the current IP address by typing http://ipchicken.com or http://www.whatismyip.com in your web browser.
In the further, it is required to obtain serial configuration settings for connecting to your own database. Note that, all those listed settings are accessible in the configuration.php file normally located at your site’s installation folder.
- Host Name – The IP address of your own webserver.
- Database Username – The name of your MySQL database.
- Database Name – The username of your MySQL database.
- Database Password – The password that you’re entering for database user.
Apart from that, you also need to open up the 3306 MySQL connection port in the webserver’s firewall. Once you are done with the above preparatory work, you can refer to the following instructions to connect remotely.
Enable MySQL Connection in the cPanel Interface
In most cases, the remote MySQL connections are disabled in cPanel servers to avoid potential security risk. That is, all IPs are originally blocked and have to be added to your “Access List” to get access to the webserver. Therefore, the computer connecting to a MySQL database from another place will has to be enabled from the cPanel interface. In respect to details, log onto your cPanel account and navigate to “Databases” > “Remote MySQL” as below.
If done, a new page will appear on the screen, and you will be able to enable the connecting computer in this “Access Host” by entering the mentioned “IP address” or “Host Name” in the field. Do not forget to click the next “Add Host” button to take effect. Till present, the filled-in IP address has been added to the remote IP connections, and in the meantime, you will be able to get connected to a MySQL database remotely. By the way, you can grant as many IP addresses as you like.
Note for Beginners: If needed, you can also delete an IP address or hostname from the “Access Hosts” list. To do that, simply click the red “X” option within the “Remove” area.
Add a Wildcard to Allow Dynamic IPs
In some cases, you may need to use a wildcard for the IP addresses that change periodically. In this way, those connecting dynamic addresses will be updated in “Remote MySQL” without any user input. To use the wildcard, you can type this % symbol in the same field. If you have the need to list all IPs in the range 184.108.40.206 to 220.127.116.118, then you can input the IP address like 123.45.67.%.
Some Programs You May Use to Connect Remotely
In the online market, there are plenty of programs that get you connected to the remote database. Note that, the database management application/program depends on the specific database that you’re connected to. There are some free open-source programs like DreamWeaver and MySQL Workbench. On the other hand, below are some commercial solutions that connect you to a remote MySQL server.
- EMS SQL Manager for MySQL – It is such a high-performance tool for your MySQL database development and administration. More importantly, you are provided with the ability to manage MySQL users, run SQL scripts and edit MySQL database data, etc.
- EMS SQL Management Studio for MySQL – It is an all-inclusive solution for your MySQL database management tasks. For instance, you can administer database objects and schema for MySQL database migration, design and extraction in one easy-to-use environment.
What If You Meet the Remote Connection Issue?
When using a remote connection tool, you may be disconnected to the database all of a sudden. If this is the case, you are enduring an error named as 1045, and there are some useful solutions for your reference.
Re-check the cPanel Password
If you’re using cPanel username and password to connect remotely, it is likely that this connection error is caused by your cPanel password. It is possible that you have changed the password of cPanel but haven’t changed it simultaneously in the “Remote MySQL” settings. It is a good practice to check and change your password out there.
Check Your IP Address
Believe it or not, most of our public IP addresses are regularly changed from the Internet Service Providers (ISPs). Hence, it is quite common to find yourself unable to access the database through a preferred program. To get rid of this embarrassing situation, you can go to the above-mentioned websites to check the current IP. In the meantime, you still need to go back the “Remote MySQL” section to add this new IP address in the “Access Host” list.
If neither of the mentioned methods is working, you can seek out professional assistance from the hosting provider. Below are some recommendable web hosts that have overwhelming reputation for technical support.