accessing a remote MySQL server locally using SSH port forwarding

mysql logo

If you have a shared hosting or VPS hosting account, you probably have a MySQL server on it. And in order to access it (run SQL commands), you typically end up firing up the command line 'mysql' client. No matter how handy you are with the command line, at least of database access and manipulation, it is easier and more productive to use a graphical client such as MySQL Admin or, the excellent, HeidiSQL GUI client. But in order to use these tools, they must be able to connect to the MySQL server, which they won't if there are firewalls between your PC and the MySQL server (there always are).

SSH to the rescue!

We solve this problem by mirroring the remote port on which MySQL server is listen to a local port on your PC. Here's how:

ssh -L 3307:localhost:3306 youruser@hostingprovider.com

In the above command ...

3307 Local port number that will mirror the remote port. This is where you will point your GUI client to. We could use any local port number, including 3306 - the usual MySQL port, but I didn't use that one since you might have a local MySQL server already listening on that port.
3306 Remote port where MySQL is listening (as seen by your hosting provider). This is the port that gets mirrored.
localhost Hostname where the remote port exists - as seen by your hosting provider. This is where you would point your command line MySQL client were you connecting from your hosting provider. Replace this with dbserver.yourhostingprovider.com if your hosting provider runs the MySQL server on a host other than where you SSH into.
youruser@yourhostingprovider.com The user account and hostname that you usually connect to when you SSH into your hosting provider.

Now, all you have to do is fire up your MySQL GUI client and point it to localhost:3307 (because one end of SSH tunnel is running on localhost:3307, while the other end is running on locahost:3306 - please note that the second 'localhost' is not your own localhost, but the one seen by yourhostingprovider.com).