Remotely Administration of Mysql Databases via SSH Tunnel (using PUTTY – Mysql’s WorkBench – Terminal ) June 15, 2012Posted 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 :
- Basic ssh concepts and more –part 1
- Basic ssh concepts and more –part 2
- Basic ssh concepts and more –part 3
- Dynamic Port Forwarding with SOCKS over SSH
- Linux admin tools
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 🙂 .