Connection with remote MySQL-server fails

jonaskellens · 11-27-2009, 01:38 PM

I'm trying to connect to a remote MySQL-DB from a webserver where I have my website running.

To give the webserver access to the MySQL-server, I define this host in the mysql-database. To be exact the tables host, user and db.
There I mention the webserver with its hostname and its IP-address. In the table user I also mention the usrername and password.

Putting the password in plain or with PASSWORD('') doesn't matter: connection keeps failing.

This is what the browser says :

Warning: mysql_connect() [function.mysql-connect]: Can't connect to MySQL server on 'ipadres' (4) in /var/www/vhosts/domain.be/httpsdocs/connectietest.php on line 5

/var/log/mysqld.log mentions :

Code:

091127 19:12:10 [Warning] Found invalid password for user: 'myuser@hostnamewebserver'; Ignoring user
091127 19:12:10 [Warning] Found invalid password for user: 'myuser@domain.be'; Ignoring user
091127 19:12:10 [Warning] Found invalid password for user: 'myuser@ipwebserver'; Ignoring user

Mysql-CLI gives :

mysql> select * from user where Host="domain.be";
<snip>
| domain.be | myuser | plainpassword | Y | N | N | N
<snip>
1 row in set (0.00 sec)

I have always flushed the privileges after changes.

I just can't connect to my database.

Can someone tell me how a host, username and password should be added to the MySQL-DB for giving this host access to the database-server or the database ?

Using phpMyAdmin for making changes...

TB0ne · 11-28-2009, 06:26 PM

Quote:

Originally Posted by jonaskellens

I'm trying to connect to a remote MySQL-DB from a webserver where I have my website running.

To give the webserver access to the MySQL-server, I define this host in the mysql-database. To be exact the tables host, user and db.
There I mention the webserver with its hostname and its IP-address. In the table user I also mention the usrername and password.

Putting the password in plain or with PASSWORD('') doesn't matter: connection keeps failing.

This is what the browser says :

Warning: mysql_connect() [function.mysql-connect]: Can't connect to MySQL server on 'ipadres' (4) in /var/www/vhosts/domain.be/httpsdocs/connectietest.php on line 5

Mysql-CLI gives :

mysql> select * from user where Host="domain.be";
<snip>
| domain.be | myuser | plainpassword | Y | N | N | N
<snip>
1 row in set (0.00 sec)

I have always flushed the privileges after changes.

I just can't connect to my database.

Can someone tell me how a host, username and password should be added to the MySQL-DB for giving this host access to the database-server or the database ?

Using phpMyAdmin for making changes...

Well, I can't help you much with phpMyAdmin...for alot of things, I prefer the command-line, to avoid problems such as this. The more layers, the more confusing things can get.

First, I'd start by eliminating the web/php portion from the equation, and make sure you can just plain connect to the MySQL database from the box. Doing "mysql -h <ip address or host name> -u <user name> -p", should let you put in the users password, and then connect. If it doesn't, you've got connection problems, separate from web/php. Check the iptables/firewall rules on the database server, and make sure they're open. Also, these pages can help you with the user permissions, as far as users/remote systems are concerned:

http://dev.mysql.com/doc/refman/5.1/...ss-denied.html
http://forums.mysql.com/read.php?34,92009
http://www.geekology.co.za/blog/2009...tabase-server/

Once you can connect from the command-line over, and you're still having problems, then you've at least narrowed it down to your PHP page(s).

jonaskellens · 11-30-2009, 03:19 AM

TB0ne,

even with :

mysql>grant all privileges on mydatabase.* to myuser@"%";

I can not connect to the database.

I have several webservers and only from this 1 webserver I cannot talk to my database-server.

So I narrowed it down to this webserver I guess. Problem is I don't control the webserver, it is controlled by my hosting provider.
So far they have not yet answered to this problem...

TB0ne · 11-30-2009, 09:34 AM

Quote:

Originally Posted by jonaskellens

TB0ne,

even with :

mysql>grant all privileges on mydatabase.* to myuser@"%";

I can not connect to the database.

I have several webservers and only from this 1 webserver I cannot talk to my database-server.

So I narrowed it down to this webserver I guess. Problem is I don't control the webserver, it is controlled by my hosting provider.
So far they have not yet answered to this problem...

Did you check the firewall(s) as I suggested? Can you connect to it via the command-line utility?

jonaskellens · 11-30-2009, 02:03 PM

Quote:

Originally Posted by TB0ne

Did you check the firewall(s) as I suggested? Can you connect to it via the command-line utility?

I do not have ssh-access to the webserver @ my hosting company. Else I would have never had to post this problem.
Your advice is what one should check...

TB0ne · 11-30-2009, 08:34 PM

Quote:

Originally Posted by jonaskellens

I do not have ssh-access to the webserver @ my hosting company. Else I would have never had to post this problem.
Your advice is what one should check...

Hard to diagnose, if you can't see the system logs/firewall rules on the web server. Not sure what to suggest...you can check the incoming syslog on your DB server from your web server, and see if you get anything rejected, or use a packet sniffer (wireshark, etc.,), to see if you get ANY packets from that name/address.

Since you've got other web servers working, are they all connecting to one DB server?? If so, focus on the differences in the web servers, and the modules they have loaded. On that note, I'm sure you've made sure the PHP database access modules are loaded, right?