125

I opened a ssh tunnel as described in this post: Zend_Db: How to connect to a MySQL database over SSH tunnel?

But now I don't know what I actually did. Does this command affect anything on the server? And how do I close this tunnel, because now I can't use my local mysql properly.

I use OSX Lion and the server runs on Ubuntu 11.10.

Community
  • 1
  • 1
Jacob
  • 1,763
  • 2
  • 11
  • 13

3 Answers3

293

Assuming you ran this command: ssh -f user@mysql-server.com -L 3306:mysql-server.com:3306 -N as described in the post you linked.

A breakdown of the command:

  1. ssh: that's pretty self-explanatory. Invokes ssh.

  2. -f: (From the man ssh page)

    Requests ssh to go to background just before command execution. This is useful if ssh is going to ask for passwords or passphrases, but the user wants it in the background.

    Essentially, send ssh to background once you've entered any passwords to establish the connection; it gives the shell prompt back to you at localhost rather than logging you in to remote-host.

  3. user@mysql-server.com: the remote server you'd like to log into.

  4. -L 3306:mysql-server.com:3306: This is the interesting bit. -L (from the man ssh page):

    [bind_address:]port:host:hostport Specifies that the given port on the local (client) host is to be forwarded to the given host and port on the remote side.

    So -L 3306:mysql-server.com:3306 binds the local port 3306 to the remote port 3306 on host mysql-server.com.

    When you connect to local port 3306, the connection is forwarded over the secure channel to mysql-server.com. The remote host, mysql-server.com then connects to mysql-server.com on port 3306.

  5. -N: don't execute a command. This is useful for "just forwarding ports" (quoting the man page).

Does this command affect anything on the server?

Yes, it establishes a connection between localhost and mysql-server.com on port 3306.

And how do I close this tunnel...

If you've used -f, you'll notice that the ssh process you've opened heads into the background. The nicer method of closing it is to run ps aux | grep 3306, find the pid of the ssh -f ... -L 3306:mysql-server.com:3306 -N, and kill <pid>. (Or maybe kill -9 <pid>; I forget if just kill works). That has the beautiful benefit of not killing all your other ssh connections; if you've got more than one, re-establishing them can be a slight ... pain.

... because now I can't use my local mysql properly.

This is because you've effectively "captured" the local mysql process and forwarded any traffic that attempts to connect to it, off to the remote mysql process. A much nicer solution would be to not use local port 3306 in the port-forward. Use something that's not used, like 33060. (Higher numbers are generally less used; it's pretty common to port-forward a combination like this: "2525->25", "8080->80", "33060->3306" or similar. Makes remembering slightly easier).

So, if you used ssh -f user@mysql-server.com -L 33060:mysql-server.com:3306 -N, you'd then point your Zend connect-to-mysql function to localhost on port 33060, which would connect to mysql-server.com on port 3306. You can obviously still connect to localhost on port 3306, so you can still use the local mysql server.

simont
  • 68,704
  • 18
  • 117
  • 136
  • 8
    Best explanation I read in a while. This is very useful when accessing a remote database from locally installed environments like R for example. Works well with public / private key authentication. Not with passwords because I did not find a way to pass the passwords. – Matt Bannert Sep 28 '13 at 09:34
  • Accepting this answer as best because of the thorough explanation. – Jacob Nov 19 '13 at 15:25
  • 1
    Nice answer! By the way, `-9` is not needed for `kill`, considering the process is still working fine ;-) – Lucio Paiva Sep 27 '17 at 00:00
  • is there a official way to stop it? instead of the regular kill? – Chop Labalagun Aug 18 '21 at 17:03
  • This is the [code](https://replit.com/@KaushikDonthi/Killing-Process-Code#main.sh) for what @simont described – Alaska Jan 17 '22 at 19:10
65

This will kill all ssh sessions that you have open from the terminal.

sudo killall ssh
thenetimp
  • 9,487
  • 5
  • 29
  • 42
  • 'No matching processes were found' it says. – Jacob Feb 25 '12 at 20:43
  • It seems like it is. Mysql worked fine as well, but then Apache started to complain. I did a reboot and everything works as expected. Issue resolved I guess :) – Jacob Feb 25 '12 at 20:55
  • 8
    Well you do not want do this if it is a prod environment... you will kick out all other admins – SwiftMango Jan 14 '15 at 20:21
  • 27
    Running `killall ssh` is a rather reckless command. I advise searching your process list (i.e. `ps aux | grep ssh` as suggested by @simont above) to discover the specific process id of your ssh tunnel process. Then you can kill pid that specifically. – Ben May 25 '16 at 16:18
  • 1
    There is a good chance you won't want all of them killed. – wobbily_col Jan 11 '18 at 11:58
26

Note: adding as answer since comments don't support code blocks.

In my opinion it is better to NOT use -f and instead just background the process as normal with &. That will give you the exact pid you need to kill:

ssh -N -L1234:other:1234 server &
pid=$!
echo "waiting a few seconds to establish tunnel..."
sleep 5
... do yer stuff... launch mysql workbench whatever
echo "killing ssh tunnel $pid"
kill $pid

Or better yet, just create this as a wrapper script:

# backend-tunnel <your cmd line, possibly 'bash'>
ssh -N -L1234:other:1234 server &
pid=$!
echo "waiting a few seconds to establish tunnel..."
sleep 5
"$@"
echo "killing ssh tunnel $pid"
kill $pid

backend-tunnel mysql-workbench

backend-tunnel bash

aaron
  • 1,068
  • 12
  • 14
  • 13
    Using `-f` allows the `ssh` session to continue even when the terminal session is closed, unlike pushing to background. Grabbing the `pid` using `ps aux | grep ssh | grep ` is pretty trivial. – simont Apr 23 '13 at 03:56
  • 1
    @simont using the explicit pid returned from the background job is safer, regardless of how you background it. if you have more than one ssh process running _you're gonna have a bad time_ – aaron Jan 17 '15 at 19:31
  • 1
    Using `-f` also has the advantage of prompting the local user for the login password, if needed. If you use `&`, that prompt is not seen by the user unless they bring the process to the foreground (e.g. by using `fg`). – Bdoserror May 12 '15 at 22:01
  • See also [Opening and closing an SSH tunnel in a shell script the smart way](https://gist.github.com/scy/6781836) – here Jul 08 '15 at 22:35
  • @simont `ps aux | grep ssh | grep ` was **exactly** what I was looking for. I've tried so many other ways that never worked. Turns out the simplest way _can_ be the best way :) – Pierce Aug 15 '15 at 23:36
  • 4
    the first time your occurs in the pid of a process which also contains the text 'ssh' (such as another ssh or sshd process or editing a file with 'ssh' in the name) you'll realize that this approach, while convenient in simple scenarios, is not bulletproof – aaron Aug 17 '15 at 15:52