accessing a remote MySQL server locally using SSH port forwarding

accessing a remote MySQL server locally using SSH port forwarding

Submitted by Jitesh Doshi on Sat, 07/21/2012 - 08:19
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!

(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).

Jitesh Doshi

Profile picture for user Jitesh Doshi
Managing Partner & CTO
  • A seasoned technology entrepreneur and enthusiast
  • A regular speaker at industry conferences and universities
  • Host and organizer of technology user groups
  • Active in management of non-profit organizations serving the local community
  • Leader and contributor for multiple open-source projects
  • Expert in cloud, application integration, web and mobile technologies
  • Author of open-source projects, including on Drupal.org - Popular Tags and PRLP.
  • Developed several highly successful software platforms and frameworks for clients