Users can't connect remotely to MySQL

Question

Problem

Users from other IPs on the (Windows XP) LAN suddenly cannot connect to my local MySQL server.

Background

I've set up MySQL on my local Windows computer so that other computers on the network have access to the root account. I've added each IP as a host for root. Up to some weeks ago, things worked flawlessly and I could connect to the server programatically and using various MySQL admin tools. Now, however, the MySQL server simply refuses connections from those IPs and I can't figure out why.

The network changes that I've done are: changing network card for two (of three) computers and fiddled around with MySQL settings. None of which should have caused this problem. I've tried adding a new user with all relevant hosts, but I get the same type of error:

MySQL Error number 1045 Access denied for user 'root'@'shop' (using passwords: YES)

The odd part is that the computer name, 'shop', is used instead of the IP. I don't know why.

Answer 1

Could you have turned off TCP connections in MySQL?

Also, is the MySQL port open in your firewall?

Answer 2

First thing that pops into mind is Windows Firewall, which could have got re-enabled if you swapped NICs on the host computer.

My next suggestion would be to use a sniffer like Wireshark on the host computer and see what exactly happens packet-wise. You can use filters to make to reduce the output - they're very simple and easy to use. This tool has saved me countless hours of debugging.

-EDIT-

Another possible cause might be that your server somehow decided to resolve IPs to hostnames, in which case ip addresses may no longer work - one would need to add hostnames to the allowed list. Not sure if it works this way for MySQL though.

Answer 3

Somehow, IPs seem to be resolved now and hostnames are used. Did you grant access to root@shop? Did you flush privileges?

Answer 4

If you changed your IP (DHCP?), make sure to correct it in my.cnf if you bound mysqld to your lan ip:

[mysqld]
...
bind-address=192.168.x.y