Remote MySQL GUI with SSH

Posted by Luke
on Tuesday, August 28

Back in my PHP/MySQL days I used to be quite the MySQL console jockey. I used it for all kinds of stuff. Then I got a new job, moved to DB2 and thankfully forgot as much as I could about MySQL. Now I’m doing Rails and working with MySQL again. But these days I use CocoaMySQL for nosing around the database on my local machines.

On remote servers, I was still using the console, but I recently found this trick which allows you to open up CocoaMySQL on a remote database using an SSH tunnel. The database doesn’t have to be configured to accept connections from outside of localhost.

Here’s how it works.

First, create an SSH tunnel.

ssh -L 8888:example.com:3306 user@example.com

Here I’m connecting the free port 8888 on my local machine to 3306 (the MySQL port) on the remote server, logging in as user.

Then configure CocoaMySQL to use the tunnel. Set the host to 127.0.0.1 and the port to 8888. The user, database, and password will be that of your remote server.

(There’s a section in the config screen to use an SSH tunnel, which I think is supposed to create the tunnel automatically, but I wasn’t able to get that to work.)

I’ve found this tip useful in my work. Hopefully you will too!

Comments

Leave a response

  1. Isaac – August 28, 2007 @ 07:55 PM

    If anyone is on slicehost you might want to look at this wiki page

    I had to use “127.0.0.1” as the host in CocoaMySQL to get it to work.

  2. Luke Francl – August 29, 2007 @ 02:47 PM

    Ack, you’re totally right Isaac. I’ve updated the example to use 127.0.0.1.

    That’s what I get for not exactly copying the connection info I’m using for real.

  3. bruno – September 11, 2007 @ 03:00 PM

    I’ve been able to get Cocoa’s built-in tunneling to work, but I think it’s a version thing: I’m running Version 0.7b3 (0.7b3), which I believe is a beta version.

  4. Luke Francl – September 12, 2007 @ 11:22 PM

    Bruno: I got the built-in SSH tunneling to work, finally. The trick was to set the Host to the same thing as the SSH Host and remove the Port.

  5. caroline – September 13, 2007 @ 02:30 AM

    Hey there,

    This is Caroline from SocialRank.

    I am trying to get in touch with you but couldn’t find your email address.

    We’re launching a new Web 2.0 site dedicated to Ruby On Rails and we have started indexing your blog posts as part of our

    content filter.

    I’d like to send you an invite to a beta preview. Can you get back to me with your email address.

    Mine is caroline@rubygalore.com

    Kind regards,

    Caroline

    www.SocialRank.com