Connecting to MySQL on a different server

Question

Hi,

I have two servers (virtual machines - I can remotely connect to these) - server 1, and server 2.

On server 1 I keep my webpages, and on server 2, I keep the databases.

I am currently trying to connect to a database on server 2 from server 1.

Here is my php code:

<?php
            $dbhost = 'xxx.xx.xx.xx:xxxx';
            $dbuser = 'xxxxxx';
            $dbpass = 'xxxxxx';

            $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql' . mysql_error());


            $dbname = 'database';
            mysql_select_db($dbname);
?>

This is the error message I get when I try to connect:

Access denied for user 'xxxxxx'@'server1' (using password: YES)

I found it a bit puzzling that it says @server1 considering I'm trying to connect to server 2. Can anyone offer any insights?

Thanks

PS: They're both on windows 2008

Answer 1

"Access denied for user 'xxxxxx'@'server1'" means that server1 don't have access to server2. You need to add new user on server2 which can connect from server1 (hostname: server1);

Answer 2

You need to give rights for that machine

Check it here

Answer 3

Remove this user and create a new one with 'xxxxxx'@'%'. Do this on both servers. @'%' indicates that the user can connect from anywhere.

Answer 4

MySQL factors in the host name of the connecting client when determining whether or not to grant access. A given username may be allowed to log in from the same machine that is hosting the database (i.e. localhost) but not allowed to connect when used from a remote system.

In your case, it sounds like user 'xxxxxx' is not authorized to connect from 'server1'. You could grant user 'xxxxxx' login rights from all hosts ('%'). However, it would be more secure to limit your that account's login rights to the specific host ('server1') or range of hosts ('%.mydomain.com' or '144.155.166.0/255.255.255.0') from which it needs to be used.

For more information on this aspect of MySql's authentication process, see http://dev.mysql.com/doc/refman/5.0/en/connection-access.html.