jump to navigation

Remotely Administration of Mysql Databases via SSH Tunnel (using PUTTY – Mysql’s WorkBench – Terminal ) June 15, 2012

Posted by Tournas Dimitrios in Mysql.

MySQL is a database that is synonymous with ease-of-use , and most database-driven Web applications use it as their persistent storage engine  . To be honest , there are dozens of tools available (free or paid)  , for administration of Mysql databases , some of  them embeds unique features . PhpMyadmin , Terminal or Mysql’s WorkBench  are my preferred tools . These tools are free , simple to use , and probably most common on local / remote host “environments” . From the perspective of security , developing on local machines is carefree , as our router/firewall  handles all possible security flaws .
A professional developer should always take  security measures when he/she has to administrate a remote  Mysql database . Today’s article will demonstrate how to set-up and use the aforementioned tools for a secure – remote Mysql database administration . We will set-up an SSH-tunnel , through this tunnel ,  all our data transfer is encrypted . I’ll skip many details , so you should be already familiar with the basic concepts of SSH and Tunneling . This Blog has posted a few articles that might be good starting point for new IT-administrators   :

To begin with ,  your hosting administrator should have give you a secure shell access that accepts remote-shell database administration . Free web host’s doesn’t give this feature , while  premium accounts give only secure file administration (ssh-terminal) , though a bit of negotiation before signing a contract with a hosting provider  might enable this functionality .  Of course , VPS or dedicated  machines have no limitations as you are free to set-up an environment of any desire .

Let’s accept that you made a good plan and your web host has given you a ssh database access with public/private key authentication instead of a password . The remote server you wish to access only needs to know your public key which it uses to encrypt information. Only a person knowing the private key can login to the server and decrypt that information . Usually , host’s give you a web-interface to generate or upload your own key-pair .

Create the key-pair and download / store the “private key” on your computer , as it will be used by our “tools” for automatically authenticate into the remote database server . Alternatively , generate the key-pair on your local computer and upload the public-key to your host . PUTTY , has a nice utility (puttygen.exe) for generating private / public keys . Don’t forget to set a pass-phrase , it’s just an extra security measure .

At this point our key-pair is generated , the public-key is uploaded to our web-host and the private-keyis stored into a directory of our computer  . First , a ssh tunnel has to be established , this tunnel will encrypt / transfer all the data to the remote database server . Let’s configure Putty for a ssh-tunnel session .


Host Name (or IP Address ) is the location of our remote database server (these details are give by the administrator ) . Leave port on 22 as it is the most common use for SSH connections . In the “Category” section double-click on “SSH” and check “Don’t start a shell or command at all” and “Enable compression“.

Configure tunneling : In the Category section click on “Tunnels” and enter the  details like :

  • Source port : 13306 , this is the input port of the tunnel . Our local tunnel will listen on this port . All traffic will be tunneled through this gateway .
  • Destination : This is the location of our remote server ” blackarrow-db.my.remotehost.com:3306 ” .These credentials are give to us by the administrator .
  • enable these two tabs : local and auto .
  • Now click on the “add” button .

Configure Proxy : In the Category section click on “Connection->Proxy” then click on “HTTP” and enter your database fully qualified domain name (FQDN) into “Proxy hostname” and port (these are given by the administrator) .

Configure authentication using access key : Click on “Auth” and enter the location of the file that contains your  private key from stage 1. You can click on “Browse…” and locate it if you don’t know the exact path . You can leave the other check boxes as they are .

Store the session configurations for future connection . Give your new session a name and enter it to the “Saved Sessions” field and click on “Save”. You can now connect to your new “Saved Session” by clicking on “Open”. Leave this tunnel / session open , as it will be used by our other applications (terminal , Workbench) to “tunnel-through”  our data .

Second part : Configuring Mysql’s WorkBench GUI tool  : 

MySQL Workbench enables a DBA , developer , or data architect to visually design , model , generate , and manage databases . It includes everything a data modeller needs for creating complex ER models , forward and reverse engineering, and also delivers key features for performing difficult change management and documentation tasks that normally require much time and effort . Let’s configure this exceptional tool for remote database administration via a SSH-tunnel . Star-up the application and click on “New Connection” .

The “New Connection” wizard has the following configuration options :

  • Connection Name : This is just a random name to identify this connection , as admins , we have many databases to administer . So give it an unique name .
  • Hostname : This is set to “localhost” , our tunnel gateway .
  • Port : the port of our gateway , it was set on 13306
  • Username / Password : the username / password  of our remote host .

Third part : Connecting to the remote database server with Window’s Terminal : 

For those administrators that can’t stay far away their computer’s keyboard , just use Windows Terminal application .
Enter the following code into the terminal :
mysql -ublackarrow -p -h localhost -P 13306
 You will be prompted to enter a password , your remote server pass , and BINGO you have remote secure access .

Happy coding🙂 .


1. Tod's Linux Server - June 15, 2012

[…] Remotely Administration of Mysql Databases via SSH Tunnel (using PUTTY – Mysql's WorkBenc… MySQL is a database that is synonymous with ease-of-use , and most database-driven Web applications use it as their persistent storage engine  . To be honest , there are […] […]

2. buline - June 18, 2012

tournasdimitrios1.wordpress.com is the perfect blog for anyone who wants to know about this topic. You know so much its almost hard to argue with you (not that I really would want…HaHa). You definitely put a new spin on a subject thats been written about for years. Great stuff, just great!

3. Albenosia - July 12, 2012

information was very great to read.

4. SHILPI - June 20, 2013


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s