in reply to Re^2: How do you use Net::OpenSSH to query mysql database via ssh tunnel
in thread How do you use Net::OpenSSH to query mysql database via ssh tunnel

Your database client needs to connect to a local network port (127.0.0.1:12345).

This local network port is forwarded by OpenSSH to a remote network port (127.0.0.1:12345 -> (openssh) -> 127.0.0.1:3306).

The 127.0.0.1:3306 is on the remote server where MySQL runs.

You cannot pass a socket variable in a string to the MySQL client library. You need to pass it as host:post.

In my example, this is 127.0.0.1:12345.

Replies are listed 'Best First'.
Re^4: How do you use Net::OpenSSH to query mysql database via ssh tunnel
by nysus (Parson) on May 09, 2017 at 11:28 UTC

    OK, that helped. So this code works:

    my $ssh = Net::OpenSSH->new('me@10.0.1.17');
my ($socket, $pid) = $ssh->open_tunnel('10.0.1.17', '3306');
my $dsn = "DBI:mysql:database=calendar;host=127.0.0.1:3306";
my $dbh = DBI->connect($dsn, 'user', 'password');

    [download]
    UPDATE: I think this is just connecting me to my local database, not the remote one.

    Many thanks!

    $PM = "Perl Monk's";
    $MCF = "Most Clueless Friar Abbot Bishop Pontiff Deacon Curate";
    $nysus = $PM . ' ' . $MCF;
    Click here if you love Perl Monks

[reply]
[d/l]

      Why do you connect your local MySQL client to 127.0.0.1:3306?

      What do you think the $socket value is in the return value of ->open_tunnel?

      You will need to find the local port number of $socket and pass that as a number to your local MySQL client library.

      Maybe it will help you to draw a picture like the following, but on paper, and to add the IP addresses and port numbers:

      
|---- Nysus Desktop ---|                      |---- remote MySQL serve
+r ---|
| 192.168.1.1          |                      |    10.0.1.17:3306     
+     |
| 127.0.0.1:xxx ]==========( ssh tunnel ) =======[ 127.0.0.1:3306     
+     |
|----------------------|                      |-----------------------
+-----|

      [download]

      Update: I don't think you can do what you want using Net::OpenSSH in the way you do, because the documentation says:

      Unlike when ssh -L options is used to create tunnels, no TCP port is opened on the local machine at any time so this is a perfectly secure operation.

      The MySQL client library will want to talk to a local IP socket, so you need the equivalent of -L instead.

      Maybe some other function of Net::OpenSSH provides that functionality.

[reply]
[d/l]
[select]

        The way I understand it, what appears to be going on is this: Net::OpenSSH is setting up a new ssh process on the local machine and giving it the -W option (ssh). This causes the ssh process on the local machine to establish a tunnel to the remote side, where it connects to the given remote address+port. On the local end, it provides that TCP connection over the ssh process's STDIN and STDOUT. Net::OpenSSH then creates a new socketpair, attaches the STDIN and STDOUT streams of the slave SSH process to one end and returns the other as $socket (I'm partially quoting from the option stdinout_socket).

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom