Access to a remote MySQL Server

12 posts / 0 new
Last post
#1 Wed, 06/08/2011 - 16:16
drum

Access to a remote MySQL Server

I am hosting a site that uses PHP to access a MySQL database that is hosted on a remote server. This is a one off database, not all databases.

MySQL is working for local databases but appears not to be working with this remote database, I know the domain\ip that is hosting the database, so I'm guessing that I need to somehow allow\whitelist the IP so that the responses are received.

Can anybody tell me where I need to do this, and what I need to enter?

Wed, 06/08/2011 - 16:23
andreychek

Howdy,

Well, you'd have to set two things.

First, in Edit Databases -> Remote Hosts, you'd need to add the IP address for the various systems that should be able to remotely access MySQL.

Second -- MySQL doesn't listen for external connections by default. You'd need to edit the my.cnf file, set the "bind-address" to "0.0.0.0", then restart MySQL.

After that, you should be able to remotely connect to your MySQL instance on your Virtualmin server.

-Eric

Wed, 06/08/2011 - 16:27
drum

Sorry I didn't make myself very clear, the website\php is on the virtualmin system, but the database is on a remote server (that does allow access), from what I can see the requests are getting to the remote server, but the responses aren't getting to the virtualmin hosted site. So I'm guessing it is block the responses possibly in the firewall?

Wed, 06/08/2011 - 17:58
andreychek

Hrm, that sounds unusual... a MySQL response shouldn't be a new request, I believe that would be delivered over the same connection.

Typically, all you'd have to do is make sure that port 3306 on the remote server is open. If your application can see port 3306 on the remote database server, you should be able to receive responses to queries.

One thing you may want to verify is that, by default, a given MySQL user can only make connections from localhost. That's a setting in MySQL.

If you hadn't set that user to be able to connect from a remote server, you would need to tweak the MySQL settings for that user in order to allow that.

-Eric

Wed, 06/08/2011 - 18:08
drum

The server and user are setup to allow remote access, and are indeed been used from other websites, which I am in the process of moving to my own server, hence this situation.

I have seen the same error on shared hosting, and when speaking to the hosts, they "whitelisted" the IP of the remote mysql server, and this cured the problem. So the problem is unique to virtualmin, I'm just not sure how I should go about whitelisting the remote server.

Wed, 06/08/2011 - 18:21
Locutus

Are you sure the data / credentials for the remove MySQL DB are set up correctly? Can you connect to the remote DB from the command line using the mysql command? What kind of errors are you getting exactly? What web software are we talking about? Anything in the Apache logs?

Eric is right: the responses to SQL queries are most definitely sent inside the same connection, so if firewall is an issue, you wouldn't be able to connect to the remote DB in the first place (except the firewall is not configured to statefully allow reply packets to established connections in -- which would be a quite odd and error-prone setup).

You don't need to "white-list" remote database servers. PHP has no such option, nor does Apache or any web software I've seen so far. Only the other way round is true: if a remote machine wants to access a local MySQL DB, that DB it needs to be configured in several places (listening IP, user host access rights) to allow that.

Wed, 06/08/2011 - 18:19
andreychek

Hrm, I suppose I'm a bit confused... since the remote MySQL server isn't creating new connections when it responds, I'm not sure what would need to be whitelisted.

Also, Virtualmin doesn't setup firewalling by default... so you shouldn't be seeing any firewall issues unless you had enabled one. But even if there were a firewall, those should always allow responses to outgoing connections.

I've heard lots of folks who had trouble initiating a connection with a remote server, but I hadn't heard of issues in receiving a response... I'm unfortunately not sure what to suggest :-)

-Eric

Wed, 06/08/2011 - 18:34 (Reply to #7)
drum

Okay in that case it sound to be more a linux\security issue rather than a virtualmin managed one.

For the record the mysql server has no access restrictions apart from username\password, i.e. connections are allowed from any IP

The code works on 50+ other websites, some have exhibited the same symptoms before the hosts "whitelisted" the mysql server

pings are received fine at approx 40ms

neither mysql or php can connect to the database on server

ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xxx' (110)

Wed, 06/08/2011 - 18:48
Locutus

Okay, if mysql cannot connect either, then the web software is bound to fail. :)

I see three possible reasons. Either the MySQL on the remote host is rejecting your IP address, an intermediate firewall is blocking port 3306, or the remote DB is running on a different port.

Wed, 06/08/2011 - 18:58
Locutus

Great, just lost another post I made to the dreaded "500 Internal server error".

Can't be bothered to re-type that all right now. I'm off till tomorrow.

Wed, 06/08/2011 - 18:59
Locutus

(Server error induced duplicate deleted.)

Wed, 06/08/2011 - 18:59
Locutus

(Server error induced duplicate deleted.)

Topic locked