11

I am attempting to connect to a remote MySQL server from my local machine virtualhost using the following code:

$conn = mysql_connect("$dbhost", "$dbuser", "$dbpass") or die(mysql_error());
        mysql_select_db($dbname, $conn) or die(mysql_error());

My problem is that I am unable to connect locally, receiving the error:

Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (10060)

This is not the case when I upload the same PHP file to the server. I am able to query the database with no problems at all.

I am unable to connect via command line either, but I can access cPanel which rules out the chance of my IP being banned accidentally.

My local server is running PHP 5.2.9, the remote server 5.2.12

OMG Ponies
  • 300,587
  • 73
  • 490
  • 482
BenTheDesigner
  • 1,894
  • 3
  • 17
  • 21

4 Answers4

18
  • firewall of the server must be set-up to enable incomming connections on port 3306
  • you must have a user in MySQL who is allowed to connect from % (any host) (see manual for details)

The current problem is the first one, but right after you resolve it you will likely get the second one.

Bozho
  • 554,002
  • 136
  • 1,025
  • 1,121
  • I created a wildcard user in remote MySQL when the problem first arose, thinking my IP may have changed (which it shouldn't have). I was able to connect to the same server a few days ago so I doubt it is a firewall issue. – BenTheDesigner Dec 20 '09 at 09:06
  • 3
    try telnet xx.xx.xx.xx 3306. If it doesn't connect, it's a network issue – Bozho Dec 20 '09 at 09:07
  • @Bozho: Unable to Telnet in on 3306. As it is shared server I'll have to speak to the host as I don't have access to their network. – BenTheDesigner Dec 20 '09 at 09:18
  • It's possible that the remote server is using a different port. – Jacob Relkin Dec 20 '09 at 10:36
  • Yes, it is. asl the host administrators about that, too – Bozho Dec 20 '09 at 11:27
  • @Jacob: MySQL can use whichever port it is configured to use, although in this case it is running on 3306 - I was able to connect to it last week, just seems to have died for some reason. – BenTheDesigner Dec 20 '09 at 12:56
  • Seems the problem was because of a change in firewall settings made at the hosts end. Accepting answer. – BenTheDesigner Dec 27 '09 at 16:08
  • I am using GoDaddy. I made the user but don't know how to access the firewall – Marvin May 19 '19 at 16:07
3

It is very easy to connect remote MySQL Server Using PHP, what you have to do is:

  1. Create a MySQL User in remote server.

  2. Give Full privilege to the User.

  3. Connect to the Server using PHP Code (Sample Given Below)

$link = mysql_connect('your_my_sql_servername or IP Address', 'new_user_which_u_created', 'password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}

echo 'Connected successfully';

mysql_select_db('sandsbtob',$link) or die ("could not open db".mysql_error());
// we connect to localhost at port 3306
j0k
  • 21,914
  • 28
  • 75
  • 84
2

I just solved this kind of a problem. What I've learned is:

  1. you'll have to edit the my.cnf and set the bind-address = your.mysql.server.address under [mysqld]
  2. comment out skip-networking field
  3. restart mysqld
  4. check if it's running

    mysql -u root -h your.mysql.server.address –p 
    
  5. create a user (usr or anything) with % as domain and grant her access to the database in question.

    mysql> CREATE USER 'usr'@'%' IDENTIFIED BY 'some_pass';
    mysql> GRANT ALL PRIVILEGES ON testDb.* TO 'monty'@'%' WITH GRANT OPTION;
    
  6. open firewall for port 3306 (you can use iptables. make sure to open port for eithe reveryone, or if you're in tight securety, then only allow the client address)

  7. restart firewall/iptables

you should be able to now connect mysql server form your client server php script.

j0k
  • 21,914
  • 28
  • 75
  • 84
1

This maybe not the answer to poster's question.But this may helpful to people whose face same situation with me:

The client have two network cards,a wireless one and a normal one. The ping to server can be succeed.However telnet serverAddress 3306 would fail. And would complain

Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (10060)

when try to connect to server.So I forbidden the normal network adapters. And tried telnet serverAddress 3306 it works.And then it work when connect to MySQL server.

Shihe Zhang
  • 1,977
  • 4
  • 30
  • 49