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

At the moment I don't see a way to hand an existing socket object like the one returned from $ssh->open_tunnel to DBD::mysql.

It seems like what you want is the equivalent of ssh -L, but looking into the documentation of Net::OpenSSH, I'm not sure that's supported, as it has this to say about tunnels:

Under the hood, in order to create a tunnel, a new ssh process is spawned with the option -W${address}:${port} (available from OpenSSH 5.4 and upwards) making it redirect its stdio streams to the remote given address. 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.

You might be able to DIY using the ssh_opts option. I'm not an expert on Net::OpenSSH so I can't say if there's a better way, but this worked for me (tested with a different server, not MySQL, but that shouldn't make a difference):

use Net::OpenSSH;

#$Net::OpenSSH::debug |= 16;
my $ssh = Net::OpenSSH->new($host);
die $ssh->error if $ssh->error;
my $pid = $ssh->spawn({ssh_opts=>'-L 127.0.0.1:12345:127.0.0.1:3306'},
+ 'cat');
die $ssh->error if $ssh->error;

# connect to remote MySQL via TCP at local 127.0.0.1:12345
sleep 10; # do your work here

print "Ending...\n";
kill 'INT', $pid;
waitpid ($pid, 0);

[download]

Replies are listed 'Best First'.
Re^2: How do you use Net::OpenSSH to query mysql database via ssh tunnel
by salva (Canon) on May 09, 2017 at 14:57 UTC
    Net::OpenSSH does not directly supports creating TCP redirections, but they can be created using ssh forward control command: 
    $ssh->system({ssh_opts => [-O => 'forward', '-L4022:localhost:22']})

    [download]
[reply]
[d/l]
[select]

      Thanks! If I understand correctly, with this command, the tunnel just stays open as long as the master process is running?

      Revised code:

      use Net::OpenSSH;
my $ssh = Net::OpenSSH->new($host);
die $ssh->error if $ssh->error;
$ssh->system({ssh_opts => ['-O','forward',
    '-L127.0.0.1:12345:127.0.0.1:3306' ] })
    or die $ssh->error;

      [download]
[reply]
[d/l]
        Yes, it will stay open until the master process finishes or you ask it to close the tunnel sending a cancel control: 
        $ssh->system({ssh_opts => ['-O', 'cancel', '-L127.0.0.1:12345:127.0.0.
+1:3306']});

        [download]
[reply]
[d/l]
[select]
Re^2: How do you use Net::OpenSSH to query mysql database via ssh tunnel
by nysus (Parson) on May 09, 2017 at 14:39 UTC

    Very awesome. Works for me. Thanks so much.

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

[reply]
Re^2: How do you use Net::OpenSSH to query mysql database via ssh tunnel
by nysus (Parson) on May 09, 2017 at 14:54 UTC
[reply]
[d/l]
      the sleep command

      Sorry, I should have been more clear on that: As far as I can tell, the sleep is not necessary in my code, I was simply using it for testing as a placeholder instead of connecting to the DB and doing work. As far as I can tell, the tunnel should remain open until you kill the slave process. So in my code, in the place where I commented "connect to remote MySQL via TCP at local 127.0.0.1:12345 / do your work here", that's what you should do :-)

      The cat is a placeholder that I figured would just sit there and do nothing while the tunnel is being used, which indeed seems to be the case. Then again, you should probably listen to the module's author.

[reply]

      So following the advice on the link above and another page it references, I got this:

      my $pid = $ssh->spawn({ssh_opts=> '-fL 127.0.0.1:12345:127.0.0.1:3306'
+}, 'sleep 10');

      [download]

      The big advantage is no more sleep command (except on the remote machine but that doesn't delay anything). Nice.

      And, apparently, as long as you do a query within 10 seconds it will work and it will autoclose the tunnel after that.

      $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]
Re^2: How do you use Net::OpenSSH to query mysql database via ssh tunnel
by nysus (Parson) on May 09, 2017 at 14:48 UTC

    I see you manually kill the process for the ssh tunnel. On my machine, the processes appear to get killed off after the termination of the program. Is there a reason to kill the process manually?

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

[reply]

In Section Seekers of Perl Wisdom