8

my new production server is only visible via ssh double tunnel. I can reach targetHost over Ubuntu's 14.04 console simply executing command ssh targetHost and than executing mysql cli on remote machine but I'd like to get to MySQL db over gui Workbench. How to configure connection in Workbench to achieve it?

My .ssh/config:

Host targetHost,  firewallHost
    User username
    IdentityFile /home/michalszulc/.ssh/id_rsa

Host targetHost
    proxycommand ssh -W %h:%p firewallHost

Host firewallHost
    hostname firewallHost.example.com
    port 2201
    passwordauthentication no
    controlmaster auto
    controlpath ~/

Host targetHost
    Hostname 123.123.123.123
4

The process it's simple. Create a new connection and choose the connection method Standard TCP / IP over SSH. Values ​​for the SSH server that provides the tunnel:

  • SSH Hostname: the address of the server that makes SSH tunnel (SSH server and MySQL can be the same host or the same network address, this will depend on the configuration).
  • SSH Username: username in the SSH server.
  • SSH Password: User password in the SSH server (if configured mode password authentication).
  • SSH Key File: A file containing the private key for authentication on the server if you have enabled PasswordAuthentication policy on the SSH server has the value no).

Values ​​for connection to DBMS MySQL.

  • MySQL Hostname: The address of the host on which MySQL is running the service, with respect to the SSH server. If the SSH and MySQL service is running on the same host, the eastbound value is localhost or 127.0.0.1, because once the connection through the tunnel is established, the MySQL service listens for requests from localhost (depending on the value set in the directive bind_adress MySQL).
  • MySQL Server Port: connection port MySQL, generalemente is 3306.
  • MySQL Username: User name by which it will connect to MySQL, ie it has to be a valid user of MySQL and be authorized for connection from the tunnel.
  • Default Schema: Equivalent to the SQL USE.
  • I'm still stuck. For example: my tunnel server is firewallhost and host with db is targethost. db is only available from localhost from targethost (should be establish ssh connection with targethost first). With your config it tries to establish connection with db on firewallhost and it's clearly not ok. – Michal_Szulc Oct 6 '16 at 12:11
  • try: ssh -L 33000:targethost:3306 user@targethost – malyy Oct 7 '16 at 6:13
1

Not to clear, if we get your setup correct. So I assume firewallhost is the host reachable from your Workbench. So It's needed to create an open port on this server, that can access the 'real' mysql Server behind this firewall.

That is quite ok, if you set up an persistent ssh tunnel on the firewallhost. That is done like this on firewallhost

ssh -L localhost:33060:localhost:3306 someshelluser@databasehost

This creates an openssh tunnel for localhost (not public on any nic) on port 33060 (or whatever port you have free here) on firewallhost which is in return connected over this tunnel with the 3306 port (again on localhost but on machine databasehost) because its connected as someshelluser on databasehost.

And now you can connect with workbench:

  • SSH Hostname: firewallhost
  • SSH Username: some valid user on firewallhost
  • SSH Password: your pass for some valid user
  • SSH Key File: alternative a keyfile

  • MySQL Hostname: localhost (as we opened a tunnel on firewallhost's localhost)

  • MySQL Server Port: 33060
  • MySQL Username: your mysql user

To have the ssh tunnel on firewallhost persistent, you can start it there like the following way with a keyfile (for accessing databasehost) and an detached process thru screen. This can be put into your rc.local or whatever you use on firewallhost.

screen -d -m /usr/bin/ssh -v -o "StrictHostKeyChecking=no" -o "UserKnownHostsFile=/dev/null" -o "BatchMode=yes" -i /your/.ssh/ssh_host_dsa_key -q -L locahost:33060:localhost:3306 someshelluser@databasehost

You must just 'imagine' that such an ssh tunnel can 'transport' any port stream from one machine to another by opening a 'new' port on the machine you create that ssh tunnel (here firewallhost). This can be even done on the public nic of firewallhost, but you want it on localhost as you don't want expose your mysql port to the public ;)

UPDATE:

Ok, as you have no direct access to firewallhost for creating an persistent tunnel, try another way. You said you are able to connect with proxycommand. As you wrote it inline and not as '-o' Option, I assume you have this in your ssh_config.

If your ssh_config looks like this:

Host server
    Hostname mysqlserver.example.org
    ProxyCommand ssh -W %h:%p firewallhost.example.org

Then you could try the following as 'SSH Hostname' in workbench. With chance, you are allowed to give ssh params with it. I did not test it, but it is not unusal for application to pass the content of Hostname fields right into the ssh call.

SSH Hostname:

-o ProxyCommand="ssh -W %h:%p firewallhost.example.org" mysqlserver.example.org

This should bring you onto your mysqlserver thru your firewallhost. From here your mysql settings can be used as if you would directly connect to mysqlserver as the workbench is expecting.

UPDATE2:

As you wrote, you are on the same machine (Ubuntu 14) that is able to connect to your mysql database behind firewallhost, you should just use the values in workbench that you use on the console. The workbench will use your .ssh/config if you call workbench with the same user that the .ssh/config is for.

Finally:

SSH Hostname would just be 'targetHost' where the proxycommand from your .ssh/config will tunnel you to 'targetHost' thru 'firewallHost'. SSH Username is 'username' (from .ssh/config again).

MySQL Hostname should be localhost and port 3306 - that should be exactly like you do when accessing the 'targetHost' by ssh as you wrote in your question. Because workbench will use these values after(!) it connects by ssh.

  • I cannot change anything on firewallhost. That's why I have provided example proxycommand which I have obtained from sysadmin. I'm looking for solution from my localhost without interfering proxy and target host. – Michal_Szulc Oct 7 '16 at 11:52
  • Ok, then please provide more infos: - is nc installed on firewallhost? - As '-o ProxyCommand' is an ssh Option and not a real command, please provide the exact way how you reach your mysql by Ubuntu console, as you stated. Then maybe we can tell you, how to put this into the WorkBench. Of course no real IPs/Hostnames are needed, but show it like someuser@somehost.tld to get sure we understand what is a real command, what an alias etc. – Jimmy Koerting Oct 7 '16 at 18:25
  • @Michal_Szulc Did you tried, what I wrote in my update? Does it work for you? – Jimmy Koerting Oct 10 '16 at 16:14
  • Yes, I tried. Unfortunatelly I got message: Failed to Connect to MySQL at localhost:3306 through SSH tunnel at username@-o proxycommand="ssh -W %h:%p firewallhost" targethost with user root – Michal_Szulc Oct 10 '16 at 18:22
  • ok, than workbench does it with the user@ syntax which makes it impossible to add ssh params. Is it an Linux Box you are working at, or windows? If it is Linux - make your ssh tunnel on your local box and tunnel thru 2 machines (your box tunneling into firewallhost into mysql server). That is the same. If you are on a Linux Box, let me know. Than I will give you some lines what to do. – Jimmy Koerting Oct 10 '16 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.