|
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);
| [reply]
[d/l]
[select] |
|
|
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']})
| [reply]
[d/l]
[select] |
|
|
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;
| [reply]
[d/l] |
|
|
|
|
| [reply] |
|
|
| [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] |
|
|
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.
| [reply]
[d/l] |
|
|
| [reply] |
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. | [reply] |
|
|
| [reply] |
|
|
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.
| [reply]
[d/l]
[select] |
|
|
|
|
|
|
|
| [reply] |
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
|
| [reply] |
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:
- Set up the environment to contain the required information
- 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
| [reply]
[d/l]
[select] |
|
|
| [reply] |
|
|
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
| [reply]
[d/l]
[select] |
|
|
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. | [reply] |
