Ruby SSH MySQL Sequel (or DataMapper) remote connection with keys using Net::SSH gem

My solution, in two parts:

Well I have figured how to make the Net::SSH::Gateway gem using a specified keyfile, and then connect to the VPS through ssh via a port other than 22:

Part 1: Net::SSH::Gateway key authentication

First you must generate the keyfiles you want to use, copy the .pub to the remove server and append it to the ~/.ssh/authorized_keys file (cat sql_rsa.pub >> authorized_keys), and then make sure user_sql (the user I created on the VPS to be used only for this purpose) has been added to AllowUsers list in sshd_config. Make note of port used for ssh (25000 for this example) and use the following code to establish the connection:

ssh_gate = Net::SSH::Gateway.new('192.n.n.n','user_sql', {port: 25000, keys: ["sql_rsa"], keys_only: true})

That will read the keyfile sql_rsa in the same directory as script file, then create a new ssh gateway for 'user_sql'@'192.n.n.n' on port 25000.

I can successfully execute raw shell commands on the remove VPS with:

ssh_gate.exec("ls -la")

To close:

ssh_gate.shutdown!

Unfortunately I am still having problems using DataMapper (do-mysql-adapter) to use the gateway. I will update this answer if I figure that part out, but at least the first half of the problem has been solved.

These are the errors that DataMapper::Logger has reported:

When 127.0.0.1 was used:

Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) (code: 2002, sql state: HY000, query: , uri: )

When localhost was used:

Access denied for user 'user_sql'@'localhost' (using password: YES) (code: 1045, sql state: 28000, query: , uri: )

When the VPS hostname was used:

Unknown MySQL server host 'hostname' (25) (code: 2005, sql state: HY000, query: , uri: )

UPDATE (No success yet): So far the only way I can access the remote MySQL database is by using Net::SSH::Gateway to establish a gateway, and then use the .sshmethod to open a new Net::SSH connection over that gateway, like so:

ssh_gate.ssh('192.n.n.n','user_sql',{port: 25000, keys: ["sql_rsa"], keys_only: true}) do |ssh|
    ssh.exec("mysql -u sql_user -p'passwd' -h localhost -P 3306 -e 'SELECT DATABASE();'")
end

In other words, I can only execute SQL commands using the mysql command line. I cannot figure out how to get Sequel or DataMapper to use the gateway to connect.

Part 2: DataMapper/Sequel/mysql2 connection through Net::SSH::Gateway

Make sure your MySQL server is bound to 127.0.0.1 in /etc/mysql/my.cnf, setup your connection - DataMapper example:

DataMapper.setup(:default, {
  adapter: 'mysql',
  database: 'DATABASE',
  username: 'username',
  password: 'passwd',
  host: '127.0.0.1',
  port: 3307}) # local port being forwarded via Net::SSH:Gateway

Followed by any class table definitions and DataMapper.finalize if required. Note that DataMapper doesn't actually connect to the remote MySQL server until either an auto_upgrade!, auto_migrate!, or query is executed, so no need to create the forwarded port yet.

Then create a new Net::SSH::Gateway, and then whenever you need DataMapper/Sequel to access the remote database, just open a port for the process, like so:

port = ssh_gate.open('127.0.0.1',3306,3307)
  child = fork do
    DataMapper.auto_upgrade! # DM call that accesses MySQL server
    exit
  end
  Process.wait
ssh_gate.close(port)

You may want to put the Net::SSH::Gateway/.open code in a begin..ensure..end block, ensure'ing the port closure and gateway shutdown.

I had to use a fork and Process.wait to establish the connection, without it the method just hangs.