Though it positively has its uses, not everyone like phpMyAdmin for managing databases. Personally I still like to use the good old MySQL Query Browser, but when your database is hosted via cPanel, getting remote access from your local machine (e.g. your development laptop) can be a pain in the neck. I’ve done extensive research and while there doesn’t seem to be a solid one-stop solution, in this article I will show you an easy way to get secure remote access to your MySQL database from your development machine (laptop, desktop).
1. Find the IP of your mysql database server — this is the host. Usually it’s the shared IP of your hosting account or the dedicated IP of your VPS. You can go to cPanel and in the left column you should see your IP. Remember — this is the host IP address for your connection.
2. Now let’s find out your IP and domain name
(e.g. how does the word see your computer coming through your cable modem connection). I tried using a DYNDNS/NO-IP setup to get around the problem of DHCP and cPanel but I couldn’t get it to work. So we’re going to use a workaround to find your exact hostname (the hostname of YOUR machine — the laptop, desktop, etc) — and this technique should work even when you are going through a DHCP laptop, for example, through your router through a cable modem.
Go to the connection properties for whatever program you are using. In my case I’m using MySQL Query Browser which looks like this:
Note that I just made up this database host IP address but what you will do here is put in the real IP of your database host (that you found in step 1), and then enter your username and password.
And naturally, as expected, you will get an error — which is exactly what we want!
For some reason, even with a service like no-ip running, cPanel didn’t like the no-ip domain as a host . So when you hit “OK”, you are going to get the mysql 1130 error, which will basically tell you that your database host cannot accept a connection from your machine — and it will give you the domain you are currently coming from.
3. Allow your domain access to your MySQL database in cPanel. Now you can take your newly discovered local domain (host) name (e.g the name of the computer you are connecting FROM [your laptop, desktop, etc]) — and enter this domain name in the cPanel Remote MySQL area. To get to this area in cPanel, browse to the Remote MySQL option:
and in the screen that appears, enter your newly discovered domain name (the name you were given from the mySQL error):
You will now see this host (what I’ve been calling your local domain) added to your list. This list is the list of hosts (domains or computers) that can access your MySQL database remotely.
Now Make the Remote Connection
At this point you should be able to connect to your MySQL database from your client of choice (e.g MySQL Query Browser) — using a username and password that has already been created in your database (if not, create a MySQL user in cPanel). Once you are done with your updating I’d recommend that you go back into cPanel and remove the host. The reason I suggest this is that it just isn’t safe to leave a possible open door to your database. You can read more about this in the paragraph below.
Considerations and Caveats
I admit that this is not the cleanest way to get access — but I find that it’s easy. There’ no SSH to configure and no keys to mess with. The downside is that each time you want remote access, you need to find your domain (which may or may not change based on your cable/internet provider), you have to add this host to the cPanel approved list, and then you should remove it when you are done. In theory — if your cable/internet provider domain doesn’t change — then you could leave the host name stored in cPanel. The risk to leaving this name stored there is that if someone gets your password, then can have full access to your database. Naturally you should have a VERY strong password if you are even thinking of this approach. Personally I remove the host when I’m done so there’s no risk of unintended access from this port of entry. And generally speaking, I do many of my database updates in bulk, so taking 2 minutes to go through these steps when I have to do an hour of updating is well worth it considering the time I am saving using my preferred updating program vs MySQL Query Browser.