SSH tunnel: local => gateway => MySQL server

Question

I need to access a MySQL database on a remote server at my lab. The server is only accessible once I log in to a gateway server on the remote network:

local server => gateway server => MySQL server.

I can ssh to the gateway using port 24222.

I am using the PERL DBI module. This is what I use to connect when I am at the lab:

my $host="1.2.3.4";
my $database="dbname";
my $user="user";
my $pw="pass";
my $table="table";    

I imagine I have to set up a tunnel through the gateway server to the database server. How do I go about doing that? If the MySQL database were on the gateway, I could open a tunnel like so:

$ ssh -f user@gateway -L 3307:127.0.0.1:3306 -N 

How can I modify this to tunnel through the open port 24222 on the gateway through to the MySQL server on 1.2.3.4?

---

UPDATE:

Using @anttir's answer I got it to work as follows.

1. Set up the tunnel:

   $ ssh -fN -p 24222 [email protected] -L 3307:1.2.3.4:3306
   

2. Set up the script variables:

   my $host="127.0.0.1";
   my $port = 3307;
   

Solution 1

With the command:

$ ssh -f user@gateway -L 3307:1.2.3.4:3306 -N 

This states that all connections to client localhost 3307 will be forwarded via the SSH tunnel to gateway and then connected to host 1.2.3.4 to port 3306.

edit: If the SSH is on port 24222 then

$ ssh -f user@gateway -p 24222 -L 3307:1.2.3.4:3306 -N 

Solution 2

If You need to use multiple hops to access MySQL server I first recommend to create .ssh/config file and use ProxyCommand like so:

  Host gateway
     HostName example.com
     User foo
     Port 22
     IdentityFile ~/.ssh/id_rsa.pub

  Host mysql_access_server
      HostName example-web.com
      Port 22
      User foo
      ProxyCommand ssh -A gateway nc %h %p

Then forward port like so:

ssh -f mysql_access_server -L 3309:sqlmaster.example.com:3306 -N

Then You can access MySQL server like so:

mysql --user=root --host=127.0.0.1 --password=root --port=3309 some_db_name

Solution 3

Using the tunnel:

ssh -f user@gateway -L 3307:1.2.3.4:3306 -N 

you will be able to connect to the database on localhost port 3307

Comments

[Antti Rytsölä]

Then use PERL DBI to connect to localhost 3307

[terdon]

Please see updated question. Doesn't the tunnel command you gave me map port 3306 on the db server to port 3307 on the gateway? Don't I need to bring in my localhost at some point?

[Antti Rytsölä]

No. the -L presumes that the listening port is in the client computer. Then connecting to localhost the mysql defaults to using mysql socket instead of IP to localhost. use -h 127.0.0.1 ( or -h localhost )

[Antti Rytsölä]

The counterpart of -L 3307:1.2.3.4:3306 is -R 3307:1.2.3.4:3306, which opens up a port in the SSH target computer, forwards it via SSH and opens a connection from SSH client to 1.2.3.4. SSH has no option to open a port in SSH target computer and make a connection from there. There's point in it because it doesn't go through SSH. You can accomplish such with tcpforward or a few netcats.