Remote connection to MySQL


#1

Hi, I wonder if you can suggest something for the following.

I am unsuccesfully trying to access a MySQL database that I have under Dreamhost from a website located under another provider. I have never in my life tried this before, so I am not sure if I am doing something wrong, or not doing something that I should. I am not a total novice to the internet, but not a web guru, either. In other words, I can maintain a website, do the basic PHP and MySQL stuff, and if I don’t know how to do something I can usually figure it out by reading the relevant documentation, help files and forums on the net. This time, however, I have read everything I can find and spent more time than I have, and now must accept defeat.

So far, I have done the following:

  1. I have tested and made sure that the database connection works when accessed from my website that is under Dreamhost.

  2. I have modified the database’s user’s details from Dreamhost’s panel so that the IP list of allowed hosts connecting to the database includes my other webhost. In fact, it currently for the sake of testing stands as “%” (which I understand allows everyone to connect?) just so that I know the problem should not be there.

  3. I have tried running the php script that works under Dreamhost on my other web host, but instead of succesfully connecting, I get the error “Lost connection to MySQL server during query” after quite some waiting. The script is this:

<?php $link = mysql_connect('MYDATABASE.MYDOMAIN.EXTENSION', 'USERNAME', 'PASSWORD'); if (!$link) { die('Could not connect: ' . mysql_error()); } echo 'Connected successfully'; mysql_close($link); ?>
  1. I have also tried the above script by giving it information about which port to connect to, and using the default 3306. Again, it works under Dreamhost, but not under my other web host.

  2. I have also tried accessing the database with telnet, but with no success. To be honest, I am not very accustomed to telnet at all, so the problem there may be with my lack of skills. I am not sure.

What else should I try? I have searhed the knowledgebase, the forum and the wiki without finding any solution. Yet, in case this question is already covered somewhere else, please just kindly provide me a link and excuse my inability to locate information. :slight_smile:

Thank you in advance.

Vili


#2

If you created the database just recently, maybe the server name didn’t propagate to your other webhost nameservers yet ? (dreamhost warns that it might take up to 2 days… although for me it usually works in a couple of hours)

Also the easiest way to try it out is with the mysql command line, for example
mysql -h db.host.name -u db_username db_name -p
(asks for password)


#3

Thank you very much for your quick and helpful reply.

I actually forgot to mention that I did my tests with two databases: one created yesterday, and another created two months ago.

However, with your help I managed to connect to the Dreamhost databases from my home computer with the mysql command, which shows that the databases are open for remote access. This, then, appears to suggest that there is either something wrong with my php code, or my other web host. Since the php code works under Dreamhost, it is the other web host that seems to create the problem.

Can web hosts somehow prevent their users from accessing databases outside the localhost? (I know I should direct this question to my web host rather than you, but the reason why I am gradually moving away from that host in the first place is that their support seems to know less about computers than I do – which isn’t THAT much – and they also have the habit of calling you names.)

Once again, thank you very much for your reply.


#4

Your script is fine (mysql_connect works for me in the same situation) (the 1st parameter is not the database name but the database host name, I suppose you have it right, because otherwise it would’ve said that it can’t find the host) What if you replace the database host name with its IP address ? Also, you can try to login to the database myphpadmin control panel (http://yourdatabasehostname) and look at the list of processes at the same time when you’re running the php script, to see if you’re getting some connection from your host or not at all, maybe it will help with more ideas…

But I suppose you’re right that it may be the problem with the other web host (and yes, they can prevent the access to the outside databases…)


#5

Thanks again for the help. Indeed, I should have written “database host name” and not “database name”.

Following your suggestion, I tried replacing the host name with its IP address. Once again, I can access the database from Dreamhost, but not from my other web host (by using the same script).

MyphpAdmin doesn’t report any (additional) processes when I am trying to connect from my other web host. I even modified the script with “sleep(10);” before closing the mysql connection so as to give me some time to see if anything happens, and while the connection from Dreamhost shows up, there is absolutely no sign of anything when the script it run from my other web host.

I then installed XAMPP to give me an apache server and mysql on my computer. It confirms that there is no problem with Dreamhost: I can run the php code from my temporary server to succesfully connect to the database. I also managed to set up a database on the home computer server and temporarily opened the firewall. Results: I can connect to it with the script from Dreamhost and from my computer, but not from my other web host.

This all definitely points towards my other web host. However, I sent them an email and they replied by saying that accessing remote databases is not prevented in any way and should work.

For what it is worth, if I try to connect to a remote host that does not exist (in this case “kaak.kop”), I get:

Could not connect: Unknown MySQL Server Host ‘kaak.kop’ (1)

rather than the typical

Could not connect: Lost connection to MySQL server during query

So at least it tries connecting, or something. It would just be interesting to know why the connection doesn’t work.

To tell the truth, I am actually not really sure if I should believe my other web host when they say that the connection is not prevented. Last summer I tried to get URL frame forwarding to work within their system, but as I had trouble figuring out their control panel, I asked them about it. Their response was “sure, you can make pages with frames”. I wrote back as kindly as I could telling them that I am not really interested in making pages with frames, but doing frame forwarding. They replied by sending me instructions on how to code pages with frames. This went on about half a dozen rounds until I asked them if they actually know what I am talking about, at which point they got annoyed, sent me one last spicy email and never continued the thread any further. About a month later, they silently disabled the “frame forwarding” option from their control panel.

Well, they are cheap and are very rarely down. And maybe I just got someone who was in the support as a summer replacement, but still…

In any case, thank you for your help. I think I will just give up on trying to connect to Dreamhost from my other web host (unless you have any more ideas) and try to find another solution to what I am attempting to achieve.


#6

Go to the database panel, select the user you are trying to have connect from outside of DH’s LAN. Hit modify and plug your ip in and it will add it to the allowed hosts.

I connect to my DB all the time from my server box @ home.

Edit: Gotcha, look like you might be right. Does your other host offer a shell account that you could try and connect to the database with?

digitalrundown.com
Promo Code: WJD97 - $97.00 off any new DreamHost plan (except month-month payments).


#7

wjd: Unless I am greatly mistaken, that was one of the first things I did (see my first post, point 2).