Note: this is similar to Use Ruby on Rails and SSH to access remote MySQL database on remote server, but the OP didn't provide much info, and the only answer given doesn't answer the question.
We recently switched our remote database from password authentication to ssh key based authentication. I have verified that I can access the db through the elegant Sequel Pro graphical db client with the following settings (some names intentionally obfuscated):
MySQL Host: woofwoof.us-west-2.rds.amazonaws.com
Username: bowser
Database: canine
Port: 3306
SSH Host: salt.woofwoof.com
SSH User: guardian
SSH Key: ~/.ssh/id_rsa
Now I need Rails to connect to the same database, also using ssh key-based authentication.
What goes in my config/database.yml
file?
So far I have:
canine:
adapter: mysql2
database: canine
username: bowser
host: woofwoof.us-west-2.rds.amazonaws.com
port: 3306
... but how do I specify SSH Host
, SSH User
and SSH Key
in the config/database.yml
file?
Back when our database had password authentication, the following worked:
canine:
adapter: mysql2
database: canine
username: bowser
password: *secret*
host: woofwoof.us-west-2.rds.amazonaws.com
port: 3306
First, you need to establish an SSH tunnel the MySQL server. On the client machine, run:
ssh -fNg -L 3307:127.0.0.1:3306 [email protected]
That will establish an SSH tunnel to the salt.woofwoof.com server. Any connections to localhost port 3307 will get sent through the tunnel to the remote host on port 3306.
Then just configure your database.yml like you would for a local connection, but specify the forwarded port 3307:
canine:
adapater: mysql2
database: canine
username: bowser
password: *secret*
port: 3307
You may also want to add the ssh tunnel setup to /etc/inittab so that the tunnel is establish after boot. See http://chxo.com/be2/20040511_5667.html for one example of how to do that.
There is also a pure rails solution
add the following to your Gemfile
gem 'net-ssh-gateway'
then create a class
module RemoteConnectionManager
SSH_USER = 'YOUR_SSH_USER'
def self.port_through_tunnel(remote_host, port, local_port: nil, db_host:'localhost')
return Net::SSH::Gateway.new(remote_host, SSH_USER)
.open(db_host,port,local_port)
end
end
last change your database.yml
adapter: mysql2
host: 127.0.0.1
port: <%= RemoteConnectionManager.port_through_tunnel('your_ssh_host', 3306, db_host: 'your_db_host_eg_some_aws_rds_db' ) %>
username: your_db_username
password: your_db_password
database: your_db_name
if local_port is nil Net/ssh will pick a free one
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With