Accessing MySQL over SSH tunnel

Lots of other people have done this; I’m just making some notes so I won’t forget later.

MySQL's logo. Converted from EPS with Scribus ...
Image via Wikipedia

Situation

Your MySQL server is located behind a firewall in your University data center. It only listens on port 3306 on localhost. In other words, outside connections are not allowed. In addition, your MySQL host is only accessible via SSH after you have authenticated via your institution’s firewall. However, you have a need to run a local client on your Mac desktop to access the remote MySQL server, for example, MySQL Workbench.

How do you do that – and how do you keep it secure?

Port Forwarding via SSH

Use the magic of port forwarding over ssh. On your Mac, do this:

  • ssh -L 9906:localhost:3306 sshuser@mysqlhost.emory.edu

substituting your user name and hostname for “sshuser” and “mysqlhost.emory.edu”. Log in to your MySQL host when prompted.

At this point, you have a secure tunnel that is redirecting traffic from port 3306 on the mysql server to your local port of 9906. All that’s left to do is to configure your local mysql client to connect to 127.0.0.1 with a port number of 9906. Now you can do backups, queries, etc. from the local desktop.

After forwarding via SSH, you can connect to a remote MySQL server as though it were running locally

I used information from the following sites to configure this. Thanks to everyone who takes time to share their information via the web!

Let me know if you have feedback on this tip.

Enhanced by Zemanta
Advertisements