Connecting to remote MySQL server using PHP

Question

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

Answer 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.

Answer 2

• 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.

Answer 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

Answer 4

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.