How to Administer a Remote MySQL Database using SSH Tunneling

Contributing Editor

MySQL tunnelMost MySQL developers have a copy of their databases running on a local PC. Although the MySQL command line may be fun for a while, it is no substitute for a decent database design application such as Workbench or SQLyog.

Connecting to a local database on localhost’s port 3306 is easy. However, when it comes to configuring the remote database, many developers resort to SSH or slower web applications such as phpMyAdmin. Fortunately, SSH tunneling allows you to use your favorite desktop design application to access your remote MySQL database.

What is Tunneling?

Wikipedia has a great description of tunneling – but life is too short to understand it. In brief, a “tunnel” is created between your PC and the server and it appears as though the remote MySQL database is run locally. If your web host supports SSH, you should be able to configure tunneling.

Tunneling Software

Windows users can download Putty and Plink from the Putty website. Copy both files to a folder of your choice.

Mac or Linux users have SSH built in; use the following Windows instructions as a guide and refer to your documentation.

Digging the Tunnel

Start a command line console, CD to the Putty/Plink folder, and enter:
plink -L 3307:localhost:3306 myusername@mydomain.com

Where:

  • 3307 is the local port used to connect to the remote database. You can use 3306, however, this could conflict with your PC’s MySQL installation.
  • localhost and 3306 is the MySQL address from the remote server (most people will not need to change this)
  • myusername is your SSH user name
  • mydomain.com is the remote server’s domain or IP address.

The first time you connect, you will be asked if the SSH key can be added to the cache. You will then be asked for your SSH password before the tunnel is established.

(Tip: copy the Plink command in to a .bat file so it can be launched with a click.)

Attaching to the Remote Database

You can now connect to the remote database from SQLyog or any other database client:

MySQL connection

The connection details are:

  • ‘localhost’ for the MySQL host address (your end of the tunnel)
  • ’3307′ for the local port to the remote database (specified in the Plink command)
  • the remote database name, and
  • the remote username and password, i.e. a MySQL account with permissions to access and configure the database.

All going well, you should now be able to manage your remote database as easily as a local version.

Does anyone have further tips or advice regarding remote MySQL administration?

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • Peter Mescalchin

    Nice little how-to.

    Navicat mySQL for Windows http://mysql.navicat.com/ which can do a similar SSH tunnelling out of the box.

  • heggaton

    For OS X, I’d recommend SSH Keychain: http://www.sshkeychain.org/

    It’s a nice, quick way to create tunnels – especially for creating regular tunnels.

    Cheers

  • http://aldoblog.com/ Alderete

    For Mac OS X users, you should be aware that you need to use the ssh command, not the plink command, e.g.:

    ssh -L 3307:127.0.0.1:3306 myusername@mydomain.com

    For whatever reason, when tunneling for MySQL I have more success using the loopback IP address, rather than localhost, hence the other change in the line above.

    Finally, and also for Mac OS X users, there’s a nice (and free) GUI tool called SSH Tunnel Manager, look for it on VersionTracker.com for download link, etc. It makes setting up and saving all of your SSH tunnels simple, adds further options, and will even show you the equivalent command line command, if you need to use it in a script or something.

  • Rohit Nadhani, Webyog

    Thank you for mentioning SQLyog.

    SQLyog Enterprise takes care of SSH tunneling and you don’t have to set up the tunnel manually by using Putty/Plink or something similar.

    Also, many shared hosting providers don’t support SSH tunneling. Notable examples of such hosting providers are Yahoo Small Business Hosting and GoDaddy. For such setups, SQLyog Enterprise provides HTTP tunneling wherein you upload a PHP page to your web-server and configure SQLyog Enterprise to communicate with that PHP page.

    A detailed tutorial is available at: HTTP Tunneling

  • http://www.optimalworks.net/ Craig Buckler

    Thanks for the OS X tips. Any Linux users out there with helpful tips?

  • Oluwasogo

    well have done this thing severally using navicat and its not really a big deal if u ask me….all i did was use the remote connection parameters as is….and that was all….anyways keep it coming

  • http://www.optimalworks.net/ Craig Buckler

    @Rohit Nadhani
    I’ve been using SQLyog for several years, and I can confirm that it’s an excellent product. If I remember correctly, the Enterprise version can use a PHP proxy so you just need to upload a single file to your server.

  • Rohit Nadhani, Webyog

    @Craig,

    Yes. You just need to upload a single PHP file for the proxy. We call this feature HTTP Tunneling.

  • dele454

    For those who perhaps have a virtual private server hosted somewhere. port 3306 needs to be included in the TCP_IN so your firewall can grant remote access via that port to mysql server.

    Mysql Query browser also does a good job in connecting remotely to your remote mysql server. same details are reqiured as that of the screenshot above.

  • cantera25

    Peter M. mentioned Navicat MySQL – does anyone know if Navicat can handle PHP functions also, or is it strictly for MySQL database dev/admin?

  • marlee

    I have just purchased a full featured version of navicat. It does cost some money but you can try it for free to see if you like it. I personally think it is the cat’s pajamas and I use it to admin my remote MySQL everyday.

    A tutorial could be found at: http://mysql.navicat.com/SSH-Tunnel-for-MySQL-Server-management/SSH.html