Can't Connect To Local Mysql Using IP Address, but CAN connect from remote server

Question

Here's an interesting one that does not seem to fall into any of the mysql connection issues I've read about or searched for: On an Ubuntu 12.04 box I had some system updates waiting to install, and I took care of that this evening. After the install, I started seeing some errors in my syslog complaining about a particular php script that could no longer connect to the mysql instance on the box. Here is the specific error:

PHP Warning:  mysql_connect(): Can't connect to MySQL server on '192.168.0.40' (4)

Now, the server's IP address is 192.168.0.40, and I've checked to make sure that I have mysql listening on 0.0.0.0 so that I can connect using either "localhost" or "192.168.0.40".

Here's where things get odd: From the local machine, if I try the following:

mysql -uroot -p -h192.168.0.40

I get this error:

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.40' (110)

I've checked, and error 110 indicates an OS timeout, and error 2003 is the mysql generic "can't connect" error. This indicates that it is not permissions with the user.

However, if I do the same thing from a remote machine (say, from 192.168.0.30), I log right in with no problems.

Futher, other scripts on the local machine that connect to mysql using "localhost" for the host rather than "192.168.0.40" connect with no problems. Also, I can connect via the mysql socket with no problems both from the command line and php scripts.

So, this feels like a networking issue of some kind on the local box, but there are no iptables rules on this box (it is firewalled externally) and I can't figure out what else may be causing this.

This problematic script worked perfectly prior to the latest system update.

For now, I'll simply change the script to connect via localhost, but I'd really like to know why it broke for 2 reasons:

There may be other scripts that connect using 192.168.0.40 that don't run very often which are now broken. Auditing them all will take more time than I feel like devoting at the moment.
I'm curious, and want to know why it broke so I can fix it correctly.

Any help?

Maybe better for serverfault – Mike Brant Oct 29 '12 at 06:26 — Mike Brant, Oct 29 '12 at 06:26
I am facing same issue, how did you fix it ? – user1191081 Dec 04 '13 at 07:13 — user1191081, Dec 04 '13 at 07:13

score 1 · Answer 1 · answered Oct 31 '12 at 02:12

1

If you really mean to access the server remotely, then open up the proper firewall port (default 3306).

answered Oct 31 '12 at 02:12

Michael Hampton

244,070
43
506
972

score 1 · Answer 2 · answered Oct 31 '12 at 05:50

1

Make sure MySQL user has the permission to connect from 192.168.0.40.

answered Oct 31 '12 at 05:50

Wasif

330
1
9

score 0 · Answer 3 · edited Aug 07 '16 at 14:53

I had this same problem, only being able to connect to the database on localhost or 127.0.0.1, and tried everything. I opened the firewall ports, tried editing the my.ini network configurations, and more.

SOLUTION (these are the steps I took to finally fix it):

Open MySQL Workbench
Log into the database as an administrator
Navigate to the Server menu up top
Select Users and Privileges
Make sure all accounts you are looking to connect with have % selected for the 'Limit to Hosts Matching:' section.

I had my root user set to localhost, which I assume is the default. Making that % instead of localhost fixed the problem for me.

Again, this is all assuming that your firewall ports are open and you have tried everything else.

referencePicture1

referencePicture2

Can't Connect To Local Mysql Using IP Address, but CAN connect from remote server

3 Answers3