How to access a remote MySQL server via an SSH tunnel

gistfile1.md

For security reasons, many hosting providers restrict external access to the default MySQL port 3306. But if you have SSH access to the database server, you can access the MySQL server from your local machine by using an SSH tunnel. An SSH tunnel forwards bits back & forth between a port on your machine and a port on the remote machine, all lovingly encrypted with SSH.

Here's how you do it! But first, my assumptions:

1. You already have configured SSH access to the remote server.
2. You want to access port 3306 on the remote server.
3. You want to access port 3307 on your local machine. This is simply because many devs (myself included) routinely have MySQL running on port 3307.

Here is the Unix command to open an SSH tunnel to hostname_or_ip with the user account username

   $ ssh username@hostname_or_ip -L 3307:localhost:3306

And if you add this handy function to your bash profile: https://gist.github.com/2001348 You can even do this!

   $ tunnel username@hostname_or_ip

And if you add an alias for that username and hostname to your ~/.ssh/config

Host foo
  Hostname  hostname_or_ip
  User      username

Then you can open the tunnel with an INSANELY small amount of typing.

   $ tunnel foo