PID 7 – MySQL and DNS
by Bill - January 7th, 2010.Filed under: Uncategorized.
This morning I helped a customer change out their old firewalls for a new pair. In the process this hideously broke their site. We had attempted this conversion before with no success and had to revert – it was broken enough that two of our other engineers couldn’t figure out what was wrong. I fixed it. What follows is a brief analysis of the process.
In the error logs on the apache server I noticed numerous failed connections to the database. Okay, that makes sense. A command-line connection also failed. The web server could see the database server, but it wasn’t getting any responses. WTF? The database didn’t show anything out of the ordinary in its log, so I started some basic network diagnostics. Traceroute from web to db worked, but traceroute wasn’t installed on the database server.
I tried to install traceroute, but discovered that outbound connectivity from the database server to the world was blocked. We had the firewall guys open up ICMP and set up the necessary NAT rules. Once ping was working, I tried to install again only to learn that DNS was also being blocked. Magically, after DNS was allowed, MySQL began to respond to requests from the Apache server and the pages loaded normally.
So what happened? Well, it turns out that by default MySQL attempts to perform a lookup on new client connections1. Since DNS was blocked it couldn’t do this and was just stalling on any new connection. We probably could have corrected the issue by modifying the hosts file on the database server, but it’s probably easier to set up and less maintenance in the long run to just allow outbound DNS requests. Alternatively, we could have reconfigured MySQL to not do this (using the –skip-name-resolve option), but that would mean that any GRANTs would have to be purely by IP address. I can verify that the users in mysql.user are only entered by IP, but that doesn’t mean that something in one of the tables isn’t.
Morals of the story? 1) Always allow DNS requests to go out. 2) Sometimes it really is the little things that mess you up.
—————————————————————————————————————
1 – http://dev.mysql.com/doc/refman/5.0/en/dns.html