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
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 User and
SSH Key in the
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
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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 <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="2245574350464b434c6251434e560c554d4d44554d4d440c414d4f">[email protected]</a>
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
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