5 Comments

How To: remote mySQL access, via SSH port forwarding

June 20, 2007 by in Geeky,Howto,Tech with 5 Comments

(NOTE: I’ve only tested this on Linux and OS X, Windows users running Putty, I’m not sure of the success rate or if it even works, give it a shot and drop it in the comments)

I’ve been doing web development for damn near a half decade now. Although I can navigate mySQL via command line over an SSH connection I’m a big GUI fan when it comes to building tables, configuring indexes and backing up catalogs or importing/creating dumps. With that said I know 24234 of you just said “Dude, PHPmyAdmin Newb!”… and now my turn… I hate PHPmyAdmin, you have a better chance of seeing me rooting around TuSSH on my Treo updating a schema than you do to see me using PHPmyAdmin… no seriously, I’m not joking I hate it that much.

Now with that bit of foreshadowing in place I will continue the article. I have a re-occuring problem, often times (smart) hosting companies lock down remote mySQL connections. Sometimes they won’t allow them at all, sometimes you have to call and manually give them an your IP address after proving who you are… usually to like 13 people. Anyway it’s a hassle to get the remote mySQL access opened up and reliably working in most cases, where as getting SSH only requires a few emails, a credit card and drivers license (if your hosting company is good). In my constant quest to update projects I have been locked out many a times, thus having to revert to my primal Command line instinct. Well… NO MORE MY FRIENDS!

Step 1: Open Up a Terminal Window

Step 2: Type in this command

sudo ssh -L 7777:127.0.0.1:3306 username@remoteserver.com

sudo or su is required to forward local ports
Whats Going On Here?
Lets dissect our request

sudo
requesting super user access in Unix (required for local port forwarding)

ssh
creating an encrypted secure connection between your machine and your server

-L 7777:127.0.0.1:3306
-L forward a local port
7777: the local port
127.0.0.1: your local machine

3306 what port on the server it should point to

username@remoteserver.com
login using this username at this server address

What did we just do?
We’ve created a secure tunnel between your local machine and your server. Meaning if I go to 127.0.0.1:7777 I’m really going to end up at remoteserver.com:3306. Meaning now if you have a mySQL GUI tool of your choice (mySQL Query Browser, mySQL Admin, Navicat, Etc.) and type in:

Host: 127.0.0.1
Port: 7777
User: yourusername
Pass: yourpassword

You will be connected to your servers mySQL schema.


  • jar_jar

    Very tangential, but so long as you are sshing from your treo, you should really check out pssh. I find it to be a lot more reliable than tussh.

  • Terry

    Hey, might want to check out Navicat.. its a GUI to mysql (client not a web app) and has several nifty ways to access a remote database.

    It supports SSH tunnels (very easy to setup in the program and you dont need an SSH client installed)
    It supports uploading a php file to the server which it then integrates with (No ssh? no problem)

    I have been using it a while now and have to say I love it, give it a try and you wont have to setup your ssh tunnels anymore just for database access.

  • David

    This works great, but you can also put the forwarding scheme in your ssh config file, located at ~/.ssh/config, using the directives listed in the man pages for ssh_config.

    My host, however, doesn’t have mySQL running locally, so just forwarding the local port to the remote machine isn’t sufficient. Instead, mySQL connections that originate from the server go through another internal hostname on 3308 to the independent mysqld process. That means that I need a remote tunnel to that hostname before I can setup the port forwarding on my local machine. Yay for security! woot.

    [Edit] – Noticed you weren’t passing comments through Textile… ;)

  • http://www.windwanderer.com Jesse

    Beautiful explanation! Unfortunately, my host doesn’t have mySQL running locally either, I get an Access Denied for user@localhost Error. David how did you make a remote tunnel to the mysql server?

    PS. I used Putty and it worked fine. There are instructions here http://www.cs.uu.nl/technical/services/ssh/putty/puttyfw.html
    I don’t know why I didn’t get the error when using Putty, perhaps Putty somehow alters the originating hostname or something?

  • http://www.windwanderer.com Jesse

    OK problem solved. The port was forwarded to 127.0.0.1 not to localhost. When I configured odbc for 127.0.0.1 instead it worked like a charm.

Privacy Policy | About Us | Contact Us | Write for us