Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

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

by nysus (Parson)
on May 09, 2017 at 10:58 UTC ( [id://1189878]=note: print w/replies, xml ) Need Help??


in reply to Re: 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

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

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

    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.

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

        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 + | |----------------------| |----------------------- +-----|

        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.

Re^3: How do you use Net::OpenSSH to query mysql database via ssh tunnel
by nysus (Parson) on May 09, 2017 at 11:39 UTC

    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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://1189878]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others having an uproarious good time at the Monastery: (4)
As of 2024-03-28 22:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found