Connecting to MySQL on a different server

Question

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

This question may help you : [Question][1] [1]: http://stackoverflow.com/questions/14438544/connect-to-mysql-on-a-different-server — pollux1er, Jun 10 '14 at 10:08

score 3 · Accepted Answer · answered Sep 21 '10 at 12:23

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.

Thanks Ben, this is the most detailed answer so I will tick this. Thanks to everyone else who answered though! — 109221793, Sep 21 '10 at 12:29

score 2 · Answer 2 · answered Sep 21 '10 at 11:59

2

"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);

answered Sep 21 '10 at 11:59

Māris Kiseļovs

16,957
5
41
48

score 2 · Answer 3 · answered Sep 21 '10 at 12:01

2

You need to give rights for that machine

Check it here

answered Sep 21 '10 at 12:01

jmj

237,923
42
401
438

score 2 · Answer 4 · answered Sep 21 '10 at 12:04

2

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

answered Sep 21 '10 at 12:04

Eduardo Xavier

1,520
21
29

Connecting to MySQL on a different server

4 Answers4