SSH from Heroku to a remote server with Mysql Db

Question

I am watching a Rails application that will require setting up a remote SSH session (SSH Tunnel?) From Heroku to a remote Mysql database as part of an ActiveRecord background session. The goal would be to transfer data at different times through this channel to the application. Connecting to a remote mysql database over the Internet would not be an option.

A few questions:

• Does Heroku provide SSHing from its Dyno?
• What will be the disadvantages of this?
• Should I be concerned about SSH session duration (work may take 1 hour)?
• Finally, how can I configure Rails and Heroku to enable remote connections for mysql database.yml endpoint?

Answer 1

Well, now I can answer my question after having been dug up for several days. In short, yes.

class LegacyAccount < ActiveRecord::Base self.table_name = "account" def self.test_connection # net-ssh-gateway gem gateway = Net::SSH::Gateway.new("ip_address","ssh_user_name", password: "password", port: "port if different from 22", verbose: :debug ) port = gateway.open('127.0.0.1', 3306, 3307) establish_connection :legacy_production result = LegacyAccount.first puts "Record: #{result.to_yaml}" gateway.close(port) gateway.shutdown! result end end 

and in your .yml database:

 legacy_production: adapter: "mysql2" host: "127.0.0.1" username: "root" password: "password" database: "legacydb" port: 3307 secure_auth: false 

Answer 2

I tried using @ user1322092 and ran into a problem when several clients tried to access db, as each tried to open a connection (all but the first would get an error).

I created a monstrous solution that included spawning an SSH process on dyno and using it for all communications. Gory details are at fooobar.com/questions/276799 / .... Besides the fact that it is really confusing, it is periodically delayed every time the process starts, regardless of whether you access the remote database.

Update

So here is a better approach that seems to work well: just trap (and ignore) Errno::EADDRINUSE errors. Here is the code:

 require 'net/ssh/gateway' class Mole TUNNEL_HOST = <redacted> TUNNEL_USER = <redacted> AWS_HOST = <redacted> TUNNEL_PORT_NUMBER = 3307 attr_reader :tunnel_gateway, :tunnel_port # Call this to open an SSH tunnel from the current machine. If the # the tunnel was already opened (eg by another process) , you'll # get a warning message def open_tunnel @tunnel_gateway = Net::SSH::Gateway.new(TUNNEL_HOST, TUNNEL_USER) begin @tunnel_port = @tunnel_gateway.open(AWS_HOST, 3306, TUNNEL_PORT_NUMBER) rescue Errno::EADDRINUSE => e $stderr.puts("Warning: #{e.class}: #{e.message}") end end # You won't normally call this, because the tunnel is a system wide # resource; you don't know when other processes want to release it. def close_tunnel r = @tunnel_gateway.close(@tunnel_port) if @tunnel_gateway @tunnel_gateway.shutdown! r end end