74

I've got ~50 databases all set up at different host names, with the requirement that I connect to them through an SSH tunnel.

For example:

  • SSH host at ssh.example.com

  • MySQL host at mysql1.example.com

I have managed to create the tunnel using autossh (web server running Debian), but I can't seem to figure out how to connect to a specific MySQL hostname "beyond" the SSH tunnel.

Typing lsof -i -n | egrep '\<ssh\>' confirms that the tunnel is working (sends port 3307 to ssh.example.com port 3306)

So when I try mysql -h 127.0.0.1 -P 3307 I get Connection refused. Not too weird since it's not an MySQL server.

My question to you guys:

How do I specify the mysql1.example.com host AFTER creating the SSH tunnel? I've tried searching everywhere but can't seem to figure it out.

Trying
  • 14,004
  • 9
  • 70
  • 110
Mad Marvin
  • 3,309
  • 3
  • 18
  • 18
  • I'm not familiar with 'autossh', but if you have a working ssh-tunnel you are using, i'd say just replace `127.0.0.1` with the actual target? – Nanne Aug 22 '13 at 06:48
  • @Nanne, thank you for your comment. However 127.0.0.1 is required to tigger the tunnel, otherwise it'll just try to connect directly to the remote host. – Mad Marvin Aug 22 '13 at 06:52

4 Answers4

144

Solved it! The thing was to connect to the correct server when creating the tunnel itself - should've seen that one coming.

ssh -f user@ssh.example.com -L 3307:mysql1.example.com:3306 -N

Then mysql -h 127.0.0.1 -P 3307 worked as intended. :)

Mad Marvin
  • 3,309
  • 3
  • 18
  • 18
  • what's the `-N` option for? – abbood Dec 23 '13 at 06:20
  • 3
    @abbood the -N option is to only create the tunnel, nothing more after that. – Mad Marvin Jan 13 '14 at 07:20
  • 1
    More specifically, you need to specify the address to which the mysql server is bound to. Look up bind-address in your my.cnf file. That's what goes between the two colon separators. – Josh Liptzin Jul 23 '14 at 18:19
  • 24
    Just a note: it is important to use `127.0.0.1` in the `mysql` command because if you use `localhost` the `P` param will be ignored. It took me some headaches to figure out this. – fguillen Sep 09 '15 at 16:38
  • Thanks ! All the documentation I saw let me think that you couldn't, and that Mysql Workbench wasn't using such an ssh tunnel. You (almost) saved my life – Pierre-Olivier Vares Apr 14 '16 at 07:44
  • 3
    You can also use `--protocol=tcp` when connecting with the mysql client. So the command would look like so `mysql -P 3307 --protocol=tcp` – trendsetter37 Aug 25 '16 at 18:02
  • If we assume your example, in my case, when I wrote `mysql -h 127.0.0.1 -P 3307 -u user -p`, it says `Access denied for user 'user'@'ssh.example.com'`. It tries to connect `ssh.example.com` instead of `mysql1.example.com` – Onur Demir Oct 06 '17 at 11:32
  • Okey I've solved it. The password was wrong. `'user'@'ssh.example.com'` part of the error confused me. I thought that it was trying to connect to the db on `ssh.example.com`. – Onur Demir Oct 06 '17 at 12:56
  • This is just what I was looking for, quick question - how do i close the tunnel? – Matt The Ninja Apr 20 '18 at 19:56
  • @MattTheNinja First run `ps aux | grep 3306` and then kill the process ID (pid) with `kill -9 (pid)` – Mad Marvin Apr 23 '18 at 11:17
  • If MySQL server is the same as the SSH host, `ssh user@ssh.example.com -L 3307:localhost:3306` to open a shell (`exit` manually or automatically when connection is closed, save the hassle to run `ps aux` and `kill` later) and on local run `mysql -P 3307 --protocol=tcp` or `mysql -P 3307 -h 127.0.0.1` as @fguillen pointed out `-P` is ignored when `-h localhost` – Levon Apr 12 '22 at 14:50
19

When you don't have direct access to mysql-server, you use jump-server.

From your machine, you connect(ssh) to jump-server and from there you connect to your mysql-server.

This can be avoided by using ssh- tunneling.

Suppose your 

       jump server is `jump-ip`
       mysql server is `mysql-ip`
       your machine is `machine-ip`

Just open ssh client(Putty in windows or terminal in linux/ios).

Type:

    ssh -L [local-port]:[mysql-ip]:[mysql-port] [jump-server-user]@[jump-ip]

After this, you can use your localhost and local-port to access mysql-server on the remote machine directly.

Eg. Your Jdbc url to access mysql database, in that case, will be

jdbc:mysql://localhost:[local-port]/[database-name]

For Windows Users, Using Putty to connect to remote MySQL Server via Tunneling

Step1: Enter your Jump server Host/IP in the session tab Step1

Step2: Go to SSH tab--> Tunnels: Enter Your MySQL server HostName: Port as destination and Source port as your local port where you want to tunnel that service and click on Add Step2

Step3: Go back to Session tab and click on Open and Enter your credentials, if it is Username/Password based.

And use same credential as mentioned above:

jdbc:mysql://localhost:[local-port]/[database-name]
ketankk
  • 2,578
  • 1
  • 29
  • 27
16

I got a nice blog about how to connect to MySQL using SSH tunnel. It is very well explained here.

The command to connect to SSH tunnel:

ssh -L [listening port]:[REMOTE_MYSQL_HOST]:[REMOTE_MYSQL_PORT] [SSH_USER]@[SSH_HOST]

The command to connect to MySQL:

localhost:[listening port]
Mr-IDE
  • 7,051
  • 1
  • 53
  • 59
sandra kevin
  • 199
  • 2
  • 6
6

I created a tunnelto the DB using this command

ssh  -L 10000:localhost:3306  user@<ip addess to connect DB> -N -f

-L is local host port it is user defined u can provide any port number

between 0 to 65535. 0 to 1023 are reserved.

whether you are using key based authentication to the server you should

mention the key like this.

ssh -i <path of the private key>  -L 10000:localhost:3306  user@<ip addess to connect DB> -N -f
Javeed Shakeel
  • 2,926
  • 2
  • 31
  • 40