Simple and secure MySQL database management using SSH

I manage a number of MySQL databases on a daily basis.  While I feel more at home on the command-line and am used to manipulating MySQL databases using the standard mysql CLI tool- others may be more comfortable with using their GUI based tools.

I get many requests to open up the database for remote access.    For a test or development environment, this is OK - but I usually deny this request for production servers unless the connection being made will be secured in some way.  MySQL databases are not typically setup securely (it can be, with SSL) and the connection is sent over the wire in the clear; this means an attacker can assemble the packets and playback what was being transferred.  Most of the time, the connection to the MySQL database is made locally by applications running on the same server so this is of little concern.

But there is a simple and convenient solution to allowing access in insecure environments.  On Linux and Mac systems, you can do the following:

ssh -L localport:localhost:remoteport user@remotehost.com

The -L flag sets up local port forwarding from the local port to the remote port of the remote server.

Then configure your MySQL client to connect to the database locally on port 3306.  It works because port 3306 on the localhost is set up to forward to 3306 on the remote host. 

For example,  I will do "ssh -L 3306:localhost:3306 user@server.com" to setup a local port forward to the default MySQL port on my computer to the remote server's port 3306. Once the connection is established, it looks like any other SSH connection.

Of course, a requirement for this to work is that port 3306 on my local machine must not be occupied by any other running service.  If you are already running MySQL on your local machine on port 3306, it will fail because port 3306 is likely occupied.  If this is the case, you simply have to change the "localport" to something else.

Once my port forwarding session is established, I will use my "favorite" MySQL tool and connect to localhost on port 3306 using the credentials of the database I am connecting to.