PHP - Can't connect to a remote MySQL database

Question

I have a MySQL database, hosted by me on a Windows server, and I would like to query it from a remote webserver. When I try to connect to the MySQL database using PHP (I know, I should be using mysqli):

$connection = @mysql_connect("203.0.113.0:3306", "username", "password");

With or without specifying the port, after a long time of loading, I get this error with an errorno of 2003:

Can't connect to MySQL server on '203.0.113.0' (4)

Here is what I get when I run netstat -n in command prompt on the server that is hosting the MySQL server: http://pastebin.com/pbRJNqCZ. It filled up the screen so I couldn't copy everything, but I know that everything else was (I saw a couple ports with a value of 3306, which is the MySQL port):

TCP    127.0.0.1:port        127.0.0.1:port        ESTABLISHED

When I run netstat -a | find "LISTENING" I get: http://pastebin.com/Lqj2BrQK

Here's what I know so far:

It isn't an error with the MySQL server not being up, because I can connect to it locally.
It isn't an error with the webserver not being able to connect to MySQL databases, because I can connect to other databases
It isn't an authentication error (The username and password are correct, and the remote server has permission)
It isn't a port forwarding error, because the port 3306 is fowarded on both TCP & UDP.
It isn't an error with being able to connect to the machine the server is hosted on, because I can ping it fine.
The server isn't only listening on the localhost interface. skip-networking and bind-address are commented out in my my.cnf file.

How could I edit my connection code, or edit my MySQL server to fix this error?

Are you sure there isn't a firewall issue? When you say the port is forwarded, why aren't you connecting to the local port? — Elliott Frisch, Jul 06 '14 at 05:08
What is `nmap` reporting from remote engine? Did you restart MySql server after altering configuration? Did you check what `netstat` is reporting on the server machine? — Pinke Helga, Jul 06 '14 at 05:09
@ElliottFrisch I'm pretty sure it isn't a firewall issue because I also host a Minecraft server on that machine. I've tried connecting to the local port and it gives the same error. — Jojodmo, Jul 06 '14 at 05:18
Does the user have remote privileges? http://stackoverflow.com/questions/6239131/how-to-grant-remote-access-permissions-to-mysql-server-for-user — cyberwombat, Jul 06 '14 at 05:20
When you connect to the local machine try to pass the path to sock file as server argument instead of IP or "localhost" (on many linux machines: "/var/run/mysqld/mysqld.sock") — Pinke Helga, Jul 06 '14 at 05:22
@Quasimodo'sclone My problem is that I can't connect to the server, which is not local — Jojodmo, Jul 06 '14 at 17:19
I've been in doubt since you've said that you've tried a connection to the local port in your comment. Please show your `netstat` on server and `nmap` on client. Has mysqld been restarted since configuration change? *MySql-Server* has been installed as .exe-installer? — Pinke Helga, Jul 06 '14 at 18:56
@Quasimodo'sclone What flag to you want me to use when I'm doing `netstat`? The Server has been restarted. What do you mean by *MySql-Server has been installed as .exe-installer*? — Jojodmo, Jul 06 '14 at 19:02
`netstat -ln` should be sufficient. A runnable installer does all the setup tasks as creation of permission tables aso. by default. MySql-Server can also be built from sources. — Pinke Helga, Jul 06 '14 at 20:24
Another approach, when there are only selected machines which should access and the are controlled by yourself, would be to establish an SSH-tunnel. Then it's like local access to MySql. That can be a solution if MySql is blocked on the route but SSH is possible, and furthermore that could even increase security. — Pinke Helga, Jul 06 '14 at 20:36
I can not see in `netstat` that mysqld is listening. Is there a `skip-networking` in *my.cnf* file? This should be commented or not present. Try `bind-address = 0.0.0.0`. Not to be ruled out that you are editing a *my.cnf* file in a wrong place and the mysqld instance isn't reading that one at startup. Or it hasn't read permission to that file. Is there a log file available? — Pinke Helga, Jul 06 '14 at 21:02
As I've just noticed, you didn't list the listening services. That's the `-l` switch on Linux and as I've looked up this can be achieved by `netstat -a | find "LISTENING"` on Windows. — Pinke Helga, Jul 06 '14 at 21:44
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/56898/discussion-between-jojodmo-and-quasimodos-clone). — Jojodmo, Jul 07 '14 at 15:41

score 1 · Accepted Answer · edited Jan 18 '21 at 12:34

Summarizing our discussion/chat:

Bind the network address 0.0.0.0 in my.cnf: bind-address = 0.0.0.0 and ensure skip-networking is commented out or not present.

Check netstat -a | find "LISTENING"

According to your pastebin there is a service listening on 3306. Test if the port is reachable on the NIC address from the server itself. This way an external firewall does not take effect. A simple test is to try a telnet connection to that port. More detailed information can be catched by the tool nmap. Nmap has displayed the mysql port as filtered. This adverts to a problem with a local packet filter, in this case the Windows firewall.

Ensure the Windows firewall is configured to allow public access to TCP port 3306 from all or a specific machine. Setup a rule in public profile or, if both servers are controled by the same domain controller, in domain profile. When the access from local machine is successful try the same from the remote web server.

If you fail to properly configure remote access to MySql port, consider to establish a SSH tunnel between the two machines. Once established you can access to MySql as if it were on the local machine. The port is then forwarded via the tunnel and on the database server side you can access the service on localhost loopback IP.

PHP - Can't connect to a remote MySQL database

1 Answers1