Solved

How to set up SSH tunnel  in MySQL Administrator GUI ?

Posted on 2013-01-04
12
522 Views
Last Modified: 2013-01-11
How do I connect to a remote database through SSH tunnel in MySQL Administrator GUI tool ?

Can anybody please tell me the steps  to try ?



What is the solution ?
0
Comment
Question by:cofactor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 78

Expert Comment

by:arnold
ID: 38745830
ssh -L 4545:localhost:3306 user@remotehost

using MySQL admin GUI you connect to localhost:4545
note that remotehost sshd configuration must allow port forwarding.
0
 

Author Comment

by:cofactor
ID: 38746077
Hi arnold

Can you please clarify  few things  in your command

ssh -L 4545:localhost:3306 user@remotehost


Q1:

Do I need to run this command  in command line (cmd)  ? ( I'm using Windows )  ?
OR  
I need to put this command somewhere in the MySQL admin GUI ? (if so where ?)



Q2:

 user  is the user id  of the remote box   ?  
OR
user  is the user id  of the remote database   ?  

Q3:  remotehost  is the IP of the remote box ?

Q4: You are not using   password of  remote box ...don't we require the remote box password ?
0
 
LVL 78

Expert Comment

by:arnold
ID: 38746096
You can use putty as the ssh client.

You would use local for the type of tunnel if you want acces from local to remote.
http://oldsite.precedence.co.uk/nc/putty.html
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:cofactor
ID: 38746210
Hi arnold,

What I don't understand is  ..do I need to fire this command  in Windows command line ?
ssh -L 4545:localhost:3306 user@remotehost

Yes/No ?
0
 
LVL 78

Expert Comment

by:arnold
ID: 38746243
No. On windows you do not have an ssh command unless you have cygwin install.

The link I provided is a reference to putty (http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html) which is an ssh client that you can run on your windows system and configure to have a tunnel upon connection to the remote system. Once the putty/ssh connection is established you will be able to access the mysql service on the remote system usng mysql admin GUI.
You would select the local option. The source port represents the local port to which you will configure mysql admin to connect (localhost:sourceport) with the destination as localhost:3306. Note localhost used in destination is a relative reference to the remote server.
You can use this tunnel, to establish connections to other systems/services on the remote network. Destination server:port will route.
0
 

Author Comment

by:cofactor
ID: 38749891
I have  made a SSH connection to remote box using putty.
Port opened is  10000


When I put  the following details in the  MySQL GUI tool ..it does not connect.

DB :  localhost
port: 10000
username: db_username
password:db_password


It does  not connect.





I also tried with this ..

DB :  localhost
port: 3306
username: db_username
password:db_password

It does  not connect.
0
 
LVL 78

Expert Comment

by:arnold
ID: 38749931
Establish an ssh connection.
Then click on putty to get the drop down and select change.
Navigate to ssh, tunnels.
Add a local source 1234
destination localhost:3306

Apply the change.
Open a new session in putty choose to use the telnet to local host port 1234
Do you get a mysql response?

The issue might be that the username/password have no access rights on localhost.

You've not said what error if any was received.
0
 

Author Comment

by:cofactor
ID: 38762379
Its not working .

I use SQLYog .  in SQLYog I connect remote database easily.  You may see how I do it   in SQLYog ..Please see the steps so that you'll be able to know my hardware systems.


In putty :
Host:  <IP1>  port : 22
Tunnel:    Add source port: 10000   Destination: <IP2>:  22

Click connect on Putty. ...... Give username / pwd of the box  <IP2>   in putty window .....SSH connection established.

Now in SQLYog editor there is a SSH tab ...I put SSH  host: localhost   port: 10,000
and  in MySQL connection I put host: localhost  port: 3306

I can connect to remote mysql database....no problem in SQLYog


However I want to  switch to MySQL GUI . .. .I am not sure how do I connect through MYSQL GUI for the same system.

Can you please tell how do I connect to the remote DB  using MySQL GUI  tunnel with this system architecture ?
0
 
LVL 78

Expert Comment

by:arnold
ID: 38762803
Please follow the instruction provided earlier.
In putty, add a tunnel local source 10000
Destination localhost:3306
Establish the ssh session.
Open mysql GUI
Connection to localhost:10000

Is the mysql to which you want to connect running on the system to which you establish the first ssh connection?

Can you provide a clear picture?
Local workstation system from which you want to run mysql GUI.
Remote system to which you establish an ssh session with a local mysql that you want to connect to?
Or do you have yet another system where mysql runs that is on the remote LAN?
0
 

Author Comment

by:cofactor
ID: 38765704
>>> do you have yet another system where mysql runs that is on the remote LAN?

Yes.

I have two  remote boxes . on second remote box mysql runs.

I use Forticient VPN to enter into remote LAN  and then do  the above SSH connection in SQLYog.


Please see the steps I posted  for SQLYog which works fine,.  I want to do the same for MYSQL Gui...... I like MySQL GUI
0
 
LVL 78

Accepted Solution

by:
arnold earned 300 total points
ID: 38765742
You have to setup the tunnel within putty as follows
Localport 1234
Destination: remotesystemwithmysql:3306
Then connect to the remotehost.

A connection from mysql administrator localhost:1234 will get your connection through the ssh tunnel to the remotehost and to the remotesystemwithmysql:3306.

Your prior example creates a tunnel and then sqlyog then establishes an ssh session within the putty tunnel with yet another tunnel.
0
 

Author Closing Comment

by:cofactor
ID: 38767974
This worked fine.  Thanks.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question