Re: 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]

Comment on Re: How do you use Net::OpenSSH to query mysql database via ssh tunnel Select or Download Code

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]
Re^3: How do you use Net::OpenSSH to query mysql database via ssh tunnel by haukex (Archbishop) on May 09, 2017 at 18:52 UTC
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]
Re^4: How do you use Net::OpenSSH to query mysql database via ssh tunnel by salva (Canon) on May 10, 2017 at 06:53 UTC
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
This person claims to have a way to avoid the `sleep` command: Opening and closing an SSH tunnel in a shell script the smart way $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^3: How do you use Net::OpenSSH to query mysql database via ssh tunnel by haukex (Archbishop) on May 09, 2017 at 18:39 UTC
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]
Re^3: How do you use Net::OpenSSH to query mysql database via ssh tunnel by nysus (Parson) on May 09, 2017 at 15:12 UTC
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]


more useful options
	PerlMonks