Brennan’s Blog

I have been dealing with the trouble of running a remote MySQL server. At home and at work I could get a different IP address which can be troublesome since I firewall port 3306 on the MySQL server and only allow in specific IP addresses. I had to firewall that port because I found attempted exploits were taking place on the MySQL service. The firewall stops the abuse, but then requires me to log in as root each time I have a new IP address and update the allow list for port 3306. It was not a pretty solution, but I have now found away around it.

I knew that people could use SSH to forward ports, but I always thought it would be a complex thing to do. But I figured I would consider it. An alternative would be to install phpMyAdmin, a web interface. That would get past the firewall but also presented me with the problem of having to log in insecurely. A quick search on Google directs me to Tunnelling MySQL Over SSH [chxo.com] which explains how simple it can be done. I just have to run this command:

ssh -fNg -L 3307:127.0.0.1:3306 brennan@brighton.offwhite.net

Of course I already had Cygwin installed in this WinXP machine with SSH and the other goodies that come with it, the hard work was already done. Then I was able to reconfigure MySQL Control Center to connect to the local port, 3307, and allow it to connect through the tunnel. It worked right away.

It also seems to be extremely fast. I guess I was expecting a little overhead with the tunnel but I saw a very responsive application whipping through table after table in each database.

To make this work a little more automatic I can place that command in a script which is run at startup so I know the tunnel is always there. If I do that I would also want to set up the keys so that SSH automatically recognizes my connection and does not require a password. I have already done that on my iBook at home so that will be automatic for me when I run this on that Mac, which just so happens to have SSH and all the other goodies installed automatically like any half decent OS should. No Cygwin necessary.

I feel pretty good about using the tunnel. It prevented me from the hell of setting up a PHP application on the web server. That is always a good thing.