Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

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 ( [id://11106237]=note: print w/replies, xml ) Need Help??


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

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

Replies are listed 'Best First'.
Re^2: How do you use Net::OpenSSH to query mysql database via ssh tunnel
by bliako (Monsignor) on Sep 16, 2019 at 13:09 UTC

    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

        ok, (i was not trying to be pedantic).

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others surveying the Monastery: (5)
As of 2024-03-29 10:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found