Accessing your MySQL server: “Network is unreachable”

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 telnet request.

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 telnet request.

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 /etc/mysql/my.cnf):

bind-address            = 0.0.0.0

Restart MySQL, and you should be able to access it all you need.

The PHP-MySQL programming loop…

Seriously. I hate writing applications that consist entirely of

// get result set with mysql_query
//  check if there are any results
     //  loop through each result
          //  output its data
//  no results?
     //  output message saying so

Once you’ve mastered that you can get hundreds of freelance jobs and securely say you have the prerequisite skills. It’s just so dull after writing it a hundred thousand times. Oh, that and

//  has this form field been set?
     //  yes, update database

That’s everything. I want something more interesting. I expect this is why Rails was made because someone got sick of doing this.

Last night’s happiness involved a gun, and some mutant rats

Just off the bat, I’m getting really annoyed my my mysqld dieing all the time. I’ve no idea what’s causing it, except that maybe WordPress (which seems to be the only affected thing at the moment) is a little too intensive for my 64Mb server.

Database dieing was the reason I didn’t do last night’s happiness update, but I’ll do it now.

Yesterday’s most happiest thing for me was probably getting Fallout 3 for my xbox. That game is freaking awesome. It seems huge and expansive, however I’m worried that – because things don’t respawn (at least, I don’t think they do. There’re still dead bodies lying around that I killed in-game days ago) – I’ll kill everything and then won’t be able to level up any more. Last night, the stupid Brotherhood of Steel guys killed half of the Raiders before I had a chance.

I only found out you can queue up attacks in the VAT thing a few hours into playing…

I bought Ghost Recon too, but I don’t really like it, so I’m putting it on ebay. I only need one bid to get my money back, to be fair, and I think people will be looking to collect as many game as they can during the holidays. Especially with people getting new xboxes and stuff for Christmas.

Oh, and be spontaneous

I’m going to bed now, but before I do I want to write up what I should be doing tomorrow:

  • More work on KoA, specifically…
    • User profiles page
    • High score table for the one and only game that’s currently on there
    • Switch the Inventory page to use Prototype (at least)
  • Find out how to fix the MySQL/VB problem
  • Find out have to fix “5 clients are using or haven’t closed the table properly” problem that MySQL has on Trinity, and exactly what causes it. (Start here.)
  • Write chapter six of my database design portfolio out
  • Wash up, and wash clothes
  • Write out a better plan for my VB tutorials