3

I have a situation somewhat similar to "How to create a ssh tunnel in ruby and then connect to mysql server on the remote host."

From my local machine, I want to run a query against a production MySQL database host. My local machine cannot directly connect to the database host. However, if I SSH to a gateway machine, I can run the MySQL client and connect to the database host.

I'm trying to figure out how to programmatically to run a query from my machine that is forwarded to the gateway machine, which forwards it to the database server, and have results returned. Basically I want to have LOCAL => GATEWAY => DB_HOST forwarding.

I have a hacky solution that runs ssh.exec and MySQL on the command line, shown below, but, I'm trying to find a way that does port forwarding twice. I tried, but haven't been successful so far. 

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

gateway = Net::SSH::Gateway.new(
  $gateway_host, $gateway_user,:password => $gateway_pass)

# This works
ssh = gateway.ssh($db_host, $db_login_user)
data = ssh.exec!("mysql -u#{$db_user} -p#{$db_pass} #{$db_name} -e '#{$sql_query}'")
puts "#{data.class} is the class type"
puts data

# Want to do something like this with port forwarding
# client = Mysql2::Client.new(:host => $db_host,
#                            :username => $db_user,
#                            :password => $db_pass,
#                            :database => $db_name,
#                            :port => port)
# results = client.query($sql_query)

puts "end stuff"
ssh.close

Any suggestions?

Community
  • 1
  • 1
Chris
  • 748
  • 2
  • 8
  • 23

1 Answers1

0

Your diagram lays it out fairly well, you would need a tunnel from the Gateway to the Db_Host; and a second tunnel from your local machine to the gateway. The two tunnels would effectively connect your local machine to the db_host by way of the gateway.

Here's a reference specific to tunneling MySQL over SSH

STW
  • 44,917
  • 17
  • 105
  • 161