MySQL and SSH Tunneling for an improved quality of life

Standard

Lately I have had several projects where I need to get to a MySQL database behind a firewall on another server. In some cases I could access the database via phpMyAdmins web interface, which is useful but hardly a great user experience, in other cases it was command line access to the database only. Instead I want to use my standard MySQL GUI which means that I need to set up a SSH tunnel to the remote host. After reading way to much about SSH tunneling and trying way to many suggested commands that turned out not to work for whatever reason I finally got it right, and it has improved my quality of life immensely (OK, that might be to exaggerate just a little bit). Maybe everybody else already have figured this out, but by blogging about it at least I know that I can find my own instructions again when needed…

I run OSX 10.5 and use Sequel Pro as my MySQL GUI, but if you use something else the approach should be similar. I assume that you do have SSH access to the remote server and also access rights to the database.

  • Open a terminal window and run the command “ssh -NC <username>@<remote server> -L 3307:127.0.0.1:3306”
    • <username> is your username on the remote server
    • <remote server> is the IP address or URI to the remote server you want to connect to
  • You will be asked for the password for, write it in an leave the terminal window open. You now have a SSH tunnel to the remote server that is using port 3306 on the remote server and mapping it to your local port 3307.
  • Open Sequel Pro and connect to your remote servers database using host “127.0.0.1” and port “3307” and the username/password used on the remote database.
  • Time to do whatever you want to do in the database from within Sequel Pro (much much nicer than phpMyAdmin)

Don’t you feel a bit happier already?