How to create a ssh tunnel in ruby and then connect to mysql server on the remote host

I would like to create a ruby script that I can run mysql commands on a remote server through a ssh tunnel.

Right now I have a manual process to do this:

1. Create a tunnel -> ssh -L 3307:127.0.0.1:3306
2. run rub开发者_Python百科y script.
3. Close tunnel.

I would love to be able to automate this so I can just run the script.

example:

require 'rubygems'   
require 'net/ssh/gateway'  
require 'mysql'  


#make the ssh connection -> I don't think I am doing this right.

Net::SSH.start('server','user') do |session|

  session.forward.local(3307,'127.0.0.1', 3306)<br>
  mysql = Mysql.connect("127.0.0.1","root","","",3307)

  dbs = mysql.list_dbs<br>
  dbs.each do |db|<br>
    puts db <br>
  end

  session.loop(0){true}<br>
end

An update - 2010-11-10:

I'm really close with this code:

require 'rubygems'  
require 'mysql'  
require 'net/ssh/gateway'  

gateway = Net::SSH::Gateway.new("host","user",{:verbose => :debug})
port = gateway.open("127.0.0.1",3306,3307)

#  mysql = Mysql.connect("127.0.0.1","user","password","mysql",3307)  
#  puts "here"  
#  mysql.close  

sleep(10)  
gateway.close(port)

When its sleeping, I am able to open a terminal window and connect to mysql on the remote host. This verifies the tunnel is created and working.

The problem now is when I uncomment the 3 lines, it just hangs.

I was able to get this to work without a fork using the mysql2 gem

require 'rubygems'
require 'mysql2'
require 'net/ssh/gateway'

gateway = Net::SSH::Gateway.new(
  'remotehost.com',
  'username'
 )
port = gateway.open('127.0.0.1', 3306, 3307)

client = Mysql2::Client.new(
  host: "127.0.0.1",
  username: 'dbuser',
  password: 'dbpass',
  database: 'dbname',
  port: port
)
results = client.query("SELECT * FROM projects")
results.each do |row|
  p row
end
client.close

This might be one possible solution:

require 'rubygems'  
require 'mysql'  
require 'net/ssh/gateway'  


gateway = Net::SSH::Gateway.new("server","user")  
port = gateway.open("127.0.0.1",3306,3307)

  child = fork do  
    mysql = Mysql.connect("127.0.0.1","user","password","mysql",port)  
    sql = "select sleep(5)"  
    mysql.query(sql)  
    mysql.close  
    exit  
  end  
  puts "child: #{child}"  
Process.wait  
gateway.close(port)  

Maybe there is a better way, but this works for what I was trying to do.

I've been trying out the gateway code above, one main difference being I have to use ssh keys for passwordless access, but also found the code handing on the Mysql.connect statement. However, when I replaced

mysql = Mysql.connect("127.0.0.1",...)

with

mysql = Mysql.connect("localhost",...)

it worked fine.

My final code looks like this:

require 'rubygems'
require 'mysql'
require 'net/ssh/gateway'

gateway = Net::SSH::Gateway.new('host',
           'user',
           :keys => ['myprivatekey.pem'],
           :verbose => :debug)
port = gateway.open("127.0.0.1",3306,3307)

mysql = Mysql.connect("localhost","dbuser","dbpassword","dbname",3307)
puts "here"
mysql.close

gateway.close(port)
gateway.shutdown!

Usually, when a tunnel is up binding a local port to the remote application port, you just connect to the local port as if it were the remote one. Remember that MySQL has access policies based on the source location of the connection, so you might want to keep that in mind. In my opinion, there's no session.forward.local nessessary.

Of course, you still don't speak the MySQL connection protocol so this might not be what you want. It might be easier to drop whatever queries to run into a file, then run mysql -u"user" -p"password"

You can also try this nice ruby gem: https://github.com/progrium/localtunnel