MySQL on EC2 -- my fight lastnight.

-i ec2-keyfile-used-to-start-instance -L 3305:localhost:3306 root@ec2-XXX-XXX-XXX-XXX.compute-1.amazonaws.comSo lastnight I had to put together a quick proof-of-concept of an EC2 server for a client. They needed mysql, among other things, running on an EC2 server instance. I should preface this with the fact that I've rarely been on a server-side of a mysql install. Sure, I've installed mysql on development machines for mysqlf, and I've installed MySQL on a windows server or two, but more often than not, I do a 'developer install' and don't open up port 3306 on the firewall, etc.

That said, I got everything installed on the server instance. Life was going well, but I was unable to connect to the mysql server through my local query browser. I had run "ec2-authorize" to set the 'group' that my instance was a member of, opening up port 3306 through ec2 tools. I changed the mysql config file to listen to port 3306 and had everything looking o.k. as far as I could tell.

I realize that the setup I was trying to do was the more insecure of configs, though every shared host I've used has this configuration.... more importantly, however, this is a simple, semi-unsecured proof of concept. No round-robin dns. No mounting of an S3 bucket into my local filesystem. No master-slave set-up for mysql. I just needed something up and running quickly, and holy hell if EC2 doesn't totally rock. A full server install that normally takes you hours can take a single hour or less -- and you can feel pretty good and secure about it.

Anyway, I set up and flushed my mysql users. Restarted the service. I was able to connect and run mysql commands when logged into the ec2 instance, but I was unable to connect to mysql through my local machine through port 3306.

I started then poking around the ec2 forums and found that many were opening up a sort of tunnel-port-forward through putty with command arguments:
putty.exe -ssh -i keyPairFile -L 3306:localhost:3306 root@remoteHost

(This is evidently a practice that developers and hosts should always do, which makes sense -- I just can't say that anybody I've been a client/customer of has ever done it. Encrypting data is a good thing. We're playin' with the big boys.)

So... the above -ssh argument is obvious
the -L however, was I believe where I was having problems.

I'd run the command, open mysql query browser, pointing it to localhost or 127.0.0.1 on port 3306. I knew my firewall was fine, but it would time out every time. My ssh ports on the server were cool. I went through a ton of troubleshooting. I changed the arguments to putty to every option I could think of. There is also a -L argument for putty.exe that I found which does something with SOCKS instead of port forwarding (or maybe the -D is the socks proxy... either way... I feel like I tried everything).

After an hour of fighting with a problem that I found to be ridiculous, I started looking for other quicker fixes. My first option was the best (imo), which was setting those arguments through the GUI:

set your host name and make sure ssh is selected:


put in your putty key associated w/ the -k argument (keypair) you set when booting the instance:


click "Tunnels" an set the source port and destination ip:port:

(I think the above is correct... I remember toying with it and think the above args are cool... click "Add" and it slaps that info into the box above.)

Feel free to save this config under session>saved sessions. Connect, type your user "root," allow putty to connect via ssh and leave that ssh connection open. Now, when you open your local mysql tools, connect through 127.0.0.1:3600 and the ip and port should be forwarding correctly.

I'm not sure if my -D argument was monkeyed up. Or if I needed root@localhost:3600, or, well, hell, I did this so many different ways that I really don't remember where my problem could have been..... but whatever the above set-up does in terms of putty arguments was where it was at.