netssh and mysl

[Ad Ad]

hi is there a way to run netssh and mysql.
some thing on the lines:

Net::SSH.start("ipaddress", "username", assword=>"psswd") do |ssh|
ssh.exec("mysql -uusername -ppassword -P3306 dbname")
ssh.exec("use dbname;")
ssh.exec("select * from tablename;")

 

[Sebastian W.]

hi is there a way to run netssh and mysql.
some thing on the lines:

Net::SSH.start("ipaddress", "username", assword=>"psswd") do |ssh|
ssh.exec("mysql -uusername -ppassword -P3306 dbname")
ssh.exec("use dbname;")
ssh.exec("select * from tablename;")

Hi Ad,
I'm not an expert on MySQL, but I believe there is a way you could pipe
a SQL script to the database via command line and get results back. That
might work.

Alternatively, instead of using net-ssh to get information from that
database, you could use one of the MySQL libraries. Connecting to a
database remotely should be a very supported feature.

I believe the recommended gem is just called "mysql". You could then use
that to write a simple MySQL client.

I'll defer to others on this list who are MySQL experts. : )

Hope this helps!

 

[Ad Ad]

Hi,

the box on which mysql is staged accepts connections only from
localhost. I couldn't get mysql to connect directly using the mysql gem.

But I did use netssh to send commands to mysql through ruby and get some
results back.

ssh.exec("mysql -u username -ppasswd --batch -e \"select * from table\"
schemaname > /tmp/mydata.txt")



which is good enough for now.

thanks for your input.

 

[Brian Candler]

the box on which mysql is staged accepts connections only from
localhost. I couldn't get mysql to connect directly using the mysql gem.

Sounds like ssh tunneling is what you want.

To demonstrate using the command-line ssh, type the following on the
'client' host:

ssh -L12345:127.0.0.1:3306 remote.host.com

While this ssh session is open, point any Mysql client (such as the Ruby
mysql client) at localhost:12345, and the connection will be redirected
down the ssh tunnel and connect to the mysql server at the other end.
The connection at the other end will originate from the ssh daemon, so
is "local" as far as the mysql server is concerned.

The advantage of this approach is you have full native SQL access,
rather than having to mess around with sending command lines, and so
higher layers will run on top of this too (like ActiveRecord)

Once you have this working to your satisfaction, you may wish to improve
this by using Net::SSH to set up the tunnel from within Ruby as well.
I've not done this, but I believe it's possible. Read the docs, and ask
if you can't work it out.

 

[badboy]

Sounds like ssh tunneling is what you want.

To demonstrate using the command-line ssh, type the following on the
'client' host:

ssh -L12345:127.0.0.1:3306 remote.host.com

While this ssh session is open, point any Mysql client (such as the Ruby
mysql client) at localhost:12345, and the connection will be redirected
down the ssh tunnel and connect to the mysql server at the other end.
The connection at the other end will originate from the ssh daemon, so
is "local" as far as the mysql server is concerned.

The advantage of this approach is you have full native SQL access,
rather than having to mess around with sending command lines, and so
higher layers will run on top of this too (like ActiveRecord)

Once you have this working to your satisfaction, you may wish to improve
this by using Net::SSH to set up the tunnel from within Ruby as well.
I've not done this, but I believe it's possible. Read the docs, and ask
if you can't work it out.

another method could be to install the mysql-gem on the remote server
and then execute a ruby script via the ssh.exec method in your local
script =D