MySQL stops accepting connections over 3306, still working on localhost

Question

I have a MySQL database that stopped accepting connections from my web server altogether. So I SSH'ed into the server and started checking its vitals. The hard disks had plenty of open space, and there was plenty of available memory and swap space. Nothing was eating up the CPU (close to 100% idle). I even connected to MySQL locally and ran a few queries without any issues. But SHOW PROCESSLIST only showed my own connection, no others.

Worst of all, in the MySQL log, no errors even remotely coincided with the unavailability of the server.

On the web server, I got an error saying "Lost connection to MySQL server during query" at the moment the unavailability started, followed by a bunch of "MySQL server has gone away" errors.

There's only one other application on the server that accepts network connections, and I killed that one (in case it was holding too many open connections or something), but it didn't help.

Finally I just restarted the MySQL process, and everything is (for now) working again.

What else should I check in these circumstances? Any idea what the problem might be? And how might I verify that is in fact the problem?

Answer 1

As the above reply, verify no changes to iptables that are blocking 3306. Also, make sure your /etc/my.cnf did not get overwritten, possibly by a distribution package update. You should NOT see the skip-networking directive under [mysqld]

Answer 2

You should lsof or netstat to check to see if the process is listening on port 3306 on the correct interface (is it bound only to 127.0.0.1 or is it bound to the hosts ip or *). You should also check to see how many files the process has open (you may be running into a ulimit). You should also make sure that nothing changed with your iptables rules to cause it to block (though unless you're using some advanced modules redrafting wouldn't have helped). Also make sure your not using something that may be updating your tcp wrappers rules like deny hosts (check /etc/hosts.* -- though again, this is not likely to have been fixed by a restart). The next time this happens fire up wireshark on both your myswl server and application server and filter on port 3306 while you try to connect to mysql across the network.