This has been a very sysadmin-y week for me, and I’ve mixed feelings about that. For now though, I’d like to tell the story of how I debugged why I couldn’t access my MySQL server.
My standard MO would be to have MySQL and the app running on the same machine. This time though, since I’ve built my app using Docker (which I’m deploying with Docker Cloud) I can’t have the MySQL server on the same box.
I do have a box already with MySQL running, however that box is smartly locked down with all sorts of iptables voodoo. Very few things are allowed to talk out from the server, and even fewer are allowed to talk to the server. Here are the steps I took while learning how to open that box up.
First, on the MySQL box figure out what port it’s running on. The default is 3306, but you can confirm that like so:
sudo netstat --tcp --listening --program --numeric-ports | grep 'mysql'
The number in the forth column, which looks a bunch like an IP address with a port, is the port you’re looking for.
Now we know that, we can check if our app server has access to the database server.
telnet yourhost.com 3306
Hopefully, you won’t get anything back but a quick message about it “Trying” to connect, and eventually “Network is unreachable”. If this command does actually connect you to your MySQL server then you should focus on locking that down as soon as you can.
Assuming that you’ve already got your server locked down via iptables though, you’ll want to open it up so that you app server (and only your app server’s IP) can get access to this port. I don’t know enough about system administration to get dirty with real iptables conf files though, so I much prefer to install webmin which will give you a lovely interface for it.
You want to be setting up rules which look like this:
- Source address: [Your app’s IP address]
- Source port: 3306
And then the defaults are largely good enough. Let me know in the comments if there are even more things that I could lock down – I think have the IP address locked to one I’m expecting should be safe enough though.
Once you’ve saved and applied those new rules, you may be able to telnet to your MySQL server now. You’ll see what definitely looks like a MySQL prompt.
If not, there’s another debug tool you can use: tshark. I’ve found this to be super helpful when trying to track down malicious looking traffic I had one time on a server of mine. In this case though, you can run it on your MySQL server and see if the server is even spotting the
tshark -ta -n port 3306
This’ll show you data being sent to that port. Try and telnet again, you should see some traffic. If not, your iptables rules are wrong, or you’re mistaken about your IP address.
If all is going well though, you should see the traffic from your
This is where I got stuck for a little while, but eventually found that MySQL doesn’t listen to the wider network – only internal network comms. You can fix this in your my.cnf file (likely
bind-address = 0.0.0.0
Restart MySQL, and you should be able to access it all you need.