Sunday, March 10, 2013

SSH Tunnel to Remote MySQL (Port Forwarding)

ssh -f -L 13306:localhost:3306 root@example.com -p 10022 -N

We block access to production MySQL servers over port 3306 for security reasons.  Here is an example connection string that proxies localhost port 13306 to port 3306 on the target system.

We use a few switched to make this easier to use:

Use -f to force SSH to go into background just before the SSH session starts.  This way you can get still be prompted for passwords but then run this in background.  We like this because then we don't have to have an open terminal all the time.

The -L switch indicates port:host:remoteport format. In this case, listen to socket 13306 on localhost and proxy to remote host port 3306.

The -p switch is special.  You might have to connect to SSH on a remote host on a port other than the standard 22.  It is starting to become common practice to change the SSH port on remote hosts to something else for security reasons.  In this example, the remote host only responds to SSH on port 10022.

Using -N tells SSH to NOT execute remote commands.  Useful when all you want this SSH session to do is port forwarding.

2 comments:

  1. Nice tip! I too used to move SSH off the default port; almost ironically I too used port 10022 as well. Instead of allowing passwords now I generally use private key encryption now since it is more secure than a password. Also, "hiding" the SSH port is not quite as safe as it used to be.

    A lot of SQL clients also have the SSH tunnel built in (SQLYog and HeidiSQL come to mind). Definitely makes life much easier. I try to avoid opening up any more ports than I absolutely have to.

    ReplyDelete
  2. Thanks Robert. My favorite client -- Aqua Data Studio -- doesn't do SSH tunneling so this is what I use. I blogged this because a friend of my was asking about it over dinner tonight.

    We actually use YubiKey's OTP (one-time-password) PAM on our servers. So it's two-factor authentication. It's more portable than private key encryption however your suggestion of using private key encryption is a good idea.

    ReplyDelete