2

I have a MySQL server running on my Windows 7 computer as a Windows Service. When I try to connect to it using external.ip = my ip address from http://www.whatismyip.com/ in command prompt:

mysql -h external.ip -u root -p

it returns:

ERROR 2003 (HY000): Can't connect to MySQL server on external.ip (10060)

I can, however, connect it to it when I change external.ip to the ip address listed with the ipconfig command:

mysql -h ipconfig.ip -u root -p

I already changed the permissions for root to accept any host (using commands like those from Accessing a mysql database from external host/ip? (ie: mysql workbench)). In the database:

SELECT host, user FROM user;

returns (to summarize):

| host   | user |
-----------------
| %      | root |

Also, in my my.ini file, there is no line that says skip-networking.

It worked fine when I tried this the other day on a different network where external.ip was the same as ipconfig.ip. Could this be the source of the problem or is there something else?

Community
  • 1
  • 1
xgord
  • 4,606
  • 6
  • 30
  • 51
  • I'll just assume you know that having root accessible to the world is a bad idea. Check your firewall. If whatsmyip reports a different ip address than your internal address, for instance, if you are behind a router, then you need to open/forward a port on your firewall/router – Russell Uhl Jun 17 '13 at 20:01
  • Agree with @RussellUhl: you're almost certainly behind a NAT router which is not forwarding the relevant port (usually 3306). – eggyal Jun 17 '13 at 20:03
  • Two things to check: 1) Are you on a home network behind a NAT device? If so, you probably need to configure it to direct incoming traffic on the MySQL port to the correct server on your home network. 2) Is the firewall on your server configured to allow connections to the MySQL port from outside machines? – Jim Lewis Jun 17 '13 at 20:05
  • @RussellUhl I know giving the root global access is probably a bad idea, but I use different public networks, so my ip address is continually changing and I can't predict what it will be. – xgord Jun 17 '13 at 20:07
  • as for the firewall, I'm on a public network and I don't have access to the physical router. – xgord Jun 17 '13 at 20:07
  • @xgord this is off-topic, but consider looking into dynamicDNS. It will allow you to have a non-changing hostname while your ip address changes. If you configure things properly, your mysql server will allow your NAME in (instead of your ip), and you'll be set. (I am a fan of www.dyndns.org - it's free!) – Russell Uhl Jun 17 '13 at 20:09
  • 1
    As for the router, running a server behind routers you can't control is nearly pointless. If you can't forward the port, you can't have external access to the server. – Russell Uhl Jun 17 '13 at 20:10
  • @RussellUhl So I can't do anything to make the server work on this network? The other network that the server worked on was also a public network where I couldn't control the routers. Why did that network work and not this one? Is it because that network didn't give a different internal ip address? – xgord Jun 17 '13 at 20:15
  • 2
    not that i can think of. MOST routers provide basic internet access that dynamically open and close ports based on requests started BEHIND the router. In the case of servers, however, the request starts OUTSIDE the router, and so the port is not opened. The one where it worked may have been exposing your IP address to the Internet directly, rather than going through the NAT process. – Russell Uhl Jun 17 '13 at 20:19
  • ok. thanks for all your help/explanations. i'll just try to muddle on without using the server on this network. – xgord Jun 17 '13 at 20:29

1 Answers1

8

If http://www.whatismyip.com/ and the address you get from ipconfig are different, that means you're using a local router (probably the one you use to connect to the internet) that network address translation. In other words, you have a local network (in your house or your fav internet cafe) that has private-network addresses like 192.168.0.1 or 10.0.0.1.

MySQL usually uses listens on port 3306 for inbound connection requests. But, when you try to connect via your public ip address (the What's My IP address) your network provider sends the request to your router. The router notices port 3306, but it probably doesn't know what to do with it. So it silently ignores the request. The software you're using to make the connection then times out. This is good. Crackers try to connect to ports like that to see if they can get into your machine.

So, to get this to work you need to configure your router to pass incoming TCP requests to port 3306 through to the machine that runs your MySQL server. It probably has a configuration screen to do this. It will work on the router you own. It won't work on the coffee shop router.

If you're not sure what "port" means, or why you get a different IP address from ipconfig and from http://www.whatismyip.com/, with respect you probably need to learn quite a bit more about internet technology before you should attempt this kind of thing.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • 1
    Thanks for the response. Yes, I know what ports are, but as it says in the comments on the question, I'm using a network I don't own. I don't have access to the router. It sounds like I need to figure out who the administrator of the network is and get them to properly configure the router. – xgord Jun 17 '13 at 22:12