MySQL via SSH Tunnel

3 October 2011

A great way to connect to a remote (often firewalled) MySQL server is through SSH. You create the tunnel locally like so:

ssh -fNg -L <local port>:localhost:<remote port> <user>@<remote host>

The remote port is usually the MySQL default, 3306. The local port can be any available port (I use 3307). The remote host is the server your remote MySQL server is running on.

One of those "fNg" options sends the command to the background, so you won't be prompted for your password. You can omit said option, but I highly recommend setting up SSH keys on the remote server to avoid having to type your password over and over again. Repeatedly. Like, the next time and the next time, too. Seriously, just stop right now and get the keys setup.

Now the SSH tunnel is running silently in the background. You can create startup scripts and such that will keep the tunnel open, so Google for that if you wish.

You can now connect to the remote MySQL server locally like so:

mysql -h 127.0.0.1 -P <local port> -u <mysql user> -p <db name>

Obviously, you have to have your MySQL users and privileges setup properly (Google it).

Now, here are some gotchas, which are the whole reason I took the time to write this up and re-invent a tutorial that exists already many times over:

1. Even though your SSH tunnel is localhost, you must specify 127.0.0.1, *not localhost* as your MySQL host in your MySQL client connection. Otherwise, MySQL will assume a socket connection and you'll be connected to your local MySQL server via a socket. D'oh!
2. If your remote MySQL server is bound to an IP on a private network, you won't be able to connect on 127.0.0.1. Rather than muck around with your local IP tables, just bind the server to 0.0.0.0 (all server IPs) and firewall any external IPs you don't want exposed.