Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

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

by nysus (Parson)
on May 09, 2017 at 10:46 UTC ( #1189875=perlquestion: print w/replies, xml ) Need Help??

nysus has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to figure out how run mysql queries on a remote server over SSH using a tunnel. Here's what I got so far, but I have no idea if I'm on the right path so I could be way out to lunch:

use Net::OpenSSH; use DBI; use Modern::Perl; use Data::Dumper; my $ssh = Net::OpenSSH->new('me@10.0.1.17'); my ($socket, $pid) = $ssh->open_tunnel('10.0.1.17', '3306'); print Dumper $socket; print Dumper $pid; my $dsn = "DBI:mysql:database=calendar;mysql_socket=$socket"; my $dbh = DBI->connect($dsn, 'user', 'pass');

This throws an error:

$VAR1 = \*{'Net::OpenSSH::$win'}; $VAR1 = 12877; DBI connect('database=calendar;mysql_socket=GLOB(0x20f38e0)','user',.. +.) failed: Can't connect to local MySQL server through socket 'GLOB(0 +x20f38e0)' (2) at t/XMLRPC.t line 27. at /usr/lib/x86_64-linux-gnu/perl5/5.20/DBI.pm line 685.

I'm not sure how to dereference that glob in a way that would be useful to DBI. Thanks.

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

Replies are listed 'Best First'.
Re: How do you use Net::OpenSSH to query mysql database via ssh tunnel
by haukex (Archbishop) on May 09, 2017 at 12:20 UTC

    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);
      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']})

        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;

      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

        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.

        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');

        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

      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

Re: How do you use Net::OpenSSH to query mysql database via ssh tunnel
by Anonymous Monk on May 09, 2017 at 10:52 UTC
    Looks like you're opening a TCP tunnel, TCP != UNIX sockets. Try connecting to the MySQL DB via TCP.

      I'm not sure I follow. But here's why I'm trying to do it via an SSH tunnel: the database server I eventually want to connect to is locked down so that it only accepts queries from trusted IP addresses. I want a solution that will work from any machine.

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

        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.

        Wait, crap. I think I'm just connecting to my local database with my code above, not the remote one.

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

Re: How do you use Net::OpenSSH to query mysql database via ssh tunnel
by Anonymous Monk on May 09, 2017 at 16:32 UTC
Re: How do you use Net::OpenSSH to query mysql database via ssh tunnel
by Skeeve (Parson) on Sep 16, 2019 at 12:46 UTC

    Reviving an old threadů

    I came here because I also wanted to connect to a mysql DB running on a remote host, not publicly exposing its port.

    My solution is as follows:

    1. Set up the environment to contain the required information
    2. Have a module which opens a tunnel and connects to the DB

    This is the module:

    package TUNNEL_DB; use DBI; use Net::OpenSSH; my $DB_PASSWORD= $ENV{DB_PASSWORD}; my $DB_USERNAME= $ENV{DB_USERNAME}; my $DB_DATABASE= $ENV{DB_DATABASE}; my $DB_PORT = $ENV{DB_PORT}; my $REMOTE_HOST= $ENV{REMOTE_HOST}; my $LOCAL_PORT = $ENV{LOCAL_PORT}; sub new { my $class= shift; $class= ref $class || $class; my $ssh = Net::OpenSSH->new($REMOTE_HOST, master_opts => "-L127.0. +0.1:$LOCAL_PORT:localhost:$DB_PORT"); $ssh->error and die "Couldn't establish SSH connection: ". $ssh->error; my $dsn = "DBI:mysql:database=$DB_DATABASE;host=127.0.0.1;port=$LO +CAL_PORT"; my $dbh = DBI->connect($dsn, $DB_USERNAME, $DB_PASSWORD); my $self= { dbh => $dbh, ssh => $ssh, }; bless $self, $class; return $self; } sub disconnect { my ($self)= @_; $self->{dbh}->disconnect; $self->{ssh}->system('exit'); } sub DESTROY { disconnect(); } sub AUTOLOAD { my $self= shift; return if $AUTOLOAD=~ /::DESTROY$/; $AUTOLOAD=~ s/^.*:://; $self->{dbh}->$AUTOLOAD(@_); } 1;

    And this is the example code, using the module:

    #!/usr/local/bin/perl use strict; use warnings; use DBI; use TUNNEL_DB; my $dbh= TUNNEL_DB->new(); my $sth = $dbh->prepare( 'SELECT id,domain from domains') or die "prepare statement failed: $dbh->errstr()"; $sth->execute() or die "execution failed: $dbh->errstr()"; print $sth->rows . " rows found.\n"; while (my $ref = $sth->fetchrow_hashref()) { print "Found a row: id = $ref->{'id'}, fn = $ref->{'domain'}\n"; } $sth->finish; $dbh->disconnect;

    So the whle connection is defined in the environment.

    After connecting, thanks to the autoload, my object can be handled like any DBI object. No special steps are required and my script doesn't even know there is a tunnel in-between.


    s$$([},&%#}/&/]+}%&{})*;#$&&s&&$^X.($'^"%]=\&(|?*{%
    +.+=%;.#_}\&"^"-+%*).}%:##%}={~=~:.")&e&&s""`$''`"e

      is there a problem if you already have an instantiated object (forwarding LOCAL_PORT) and try to create a new connection?

        I'm sure there is because the port already is in use.

        I didn't try to create the ultimat CPAN-ready Module, but just a module for my problem-at-hand.


        s$$([},&%#}/&/]+}%&{})*;#$&&s&&$^X.($'^"%]=\&(|?*{%
        +.+=%;.#_}\&"^"-+%*).}%:##%}={~=~:.")&e&&s""`$''`"e
Re: How do you use Net::OpenSSH to query mysql database via ssh tunnel
by Anonymous Monk on May 09, 2017 at 17:38 UTC
    In the Real World you add an API layer.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1189875]
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (3)
As of 2023-01-27 07:30 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?