accessing a remote MySQL server locally using SSH port forwarding
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!
(This requires an SSH client installed on your computer. You can use Git Bash for Windows. Mac and Linux come with ssh-clients built in.)
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).
