MySQL connection over SSH tunnel - how to specify other MySQL server?

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.

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

ssh -f [email protected] -L 3307:mysql1.example.com:3306 -N

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

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

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

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]

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]

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