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

Tai

Answer 1

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

Pun

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. – Tai

try telnet xx.xx.xx.xx 3306. If it doesn't connect, it's a network issue – Pun

@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. – Tai

It's possible that the remote server is using a different port. – Kalisz

Yes, it is. asl the host administrators about that, too – Pun

@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. – Tai

Seems the problem was because of a change in firewall settings made at the hosts end. Accepting answer. – Tai

I am using GoDaddy. I made the user but don't know how to access the firewall – Mcclelland

Answer 2

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

Spavin

Answer 3

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.

Kelson

Answer 4

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.

Fonseca