That was the google query I searched and it took me a long time to find an answer so I’m going to contribute it back to the internet.
Most helpful resources:
http://www.sequelpro.com/docs/Connecting_to_a_MySQL_Server_on_a_Remote_Host
(almost helpful: http://it.blog.adclick.pt/linux/amazon-rds-tunnel-access-from-your-desktop/ )
Problem
I wanted to connect to a remote RDS instance from my mac computer. You normally would connect through an ec2 instance so do things differently, but if you want to connect from your local computer, the permissions make this a bit challenging. You might think you just connect by saying
mysql -u user -p -h your.rds.host
but that won’t work.
Solution
If you scour the internet there appear to two main solutions.
- Add your IP address to a whitelist on rds, but could be problematic since your IP address will probably change.
- Connect to the RDS instance using SSH tunneling.
I had not set up SSH tunneling before and for some reason had a hard time tracking it down. I use sequl pro to inspect our database sometimes, and I realize I was able to connect using SSH tunneling via their gui interface.
So, what is the solution?
There are two steps:
- Set up the SSH Tunnel
ssh -N -L 3306:your.rds.endpoint.rds.amazonaws.com:3306 sshuser@yourserver.com
-N only set up the tunnel
-L set up the forwarding
3306 that first number is the port on your local machine
your.rds.endpoint.amazonaws.com The name of the rds endpoing
3306 the port on the remote computer
sshuser@yourserver.com how you log in to your ec2 instance
2. Use the SSH Tunnel
mysql -u dbuser -p -h 127.0.0.1
This lets you connect to the remote rds instance. Note that you have to use the host here 127.0.0.1 explicitly and that it is not the host you set up earlier. This is because it is now forwarding all of the requests. That’s all.
To be clear on how the ports work, here is another example
ssh -N -L 1234:your.rds.endpoint.rds.amazonaws.com:3306 sshuser@yourserver.com
mysql -u dbuser -p -h 127.0.0.1 -P 1234
This says forward from port 1234 on my computer to port 3306 on the remote instance. I just used 3306 in both as the defaults.
Hey, thanks for this. However I have a quick question. Where do I find my server? (in order to do this -> sshuser@yourserver.com
It would be the same way your normally ssh into your server. For example on this site, I ssh in with
sshusername@thekeesh.com
Similarly, you could do it with an IP address or some other name
sshuser@YOUR IP
or amazon often will give you a longer public dns name.
Thanks for this article, Jeremy. I was having difficulty getting tunneling to work on my own, but switching from localhost to 127.0.0.1, as you suggest in this article, did the trick. I really appreciate that you wrote this out.
Yup, glad I could help!
Hi,
Thank you for this post. It saved me from alot of pain. in case one uses a key then make sure u add the -i option
ssh -N -L 3307:RDS_HOST:3306 USER@AWSHOST -i KEY
and also once connected it will just blink so u need to open a new terminal to query the db
Thanks,
Vishal
Hi Vishal,
“and also once connected it will just blink so u need to open a new terminal to query the db”
My Terminal is just blinking now after doing this:
ssh -N -L 3307:RDS_HOST:3306 USER@AWSHOST -i KEY
How now do I add the second part?
mysql -u dbuser -p -h 127.0.0.1
What are the steps to open a new Terminal to query the db?
Thanks
I’m not sure where the tunneling comes in but I usually just log into my EC2 Instance as you would normally do and then issue a mysql connect to the endpoint.
That has worked for me.
Thank you! This saved me the trouble of re-creating my RDS server, migrating, etc. which would have resulted in 30 minutes of downtime and a late night for me!
Hey guys,
For the people who are using nodejs. Use my working code as an example:
//AWS Configuration file
RDS : {
host : ‘127.0.0.1’,
database : ‘*****_db’,
user : ‘*****’,
password : ‘*****’,
port : ‘1234’
},
RDSWithoutTunneling : {
host : ‘your.rds.endpoint.rds.amazonaws.com’,
database : ‘*****_db’,
user : ‘********’,
password : ‘********’,
port : ‘3306’,
}
//Controller file.
AWSConfig = require (‘../config/aws_config.js’);
function getConnection(){
var connection = mysql.createConnection({
host : AWSConfig.RDS.host,
database : AWSConfig.RDS.database,
port : AWSConfig.RDS.port,
user : AWSConfig.RDS.user,
password : AWSConfig.RDS.password
});
return connection;
}
//Implementation (Steps)
– Using ssh tunneling :
1. In a terminal run: ssh -N -L 1234:your.rds.endpoint.rds.amazonaws.com:3306 -i Keypem.pem ubuntu@xx.xx.xx.xx
2. In another terminal run: mysql -u admin -p -h 127.0.0.1 -P 1234
– Not using ssh tunneling:
1. Add your IP address to a whitelist on RDS Security Group.
2. Make sure when you create your RDS instance that ‘publicly accessible?’ is ‘yes’. If not, you won’t be able to connect even when you have the ports properly set up.
Hope this is useful for you.
thanks man! this really helped, and ran into no issues.
i’m even tunneling through a bastion host and it just works :tm:
Sorry, I don’t get it! SSH usually requires a password or ssh key to be given by the server and installed on the client. There is no mention of passwords or ssh keys in your article. So where do I get mine? I don’t see anywhere on the AWS Console to retrieve it.
Furthermore, “Amazon RDS does not allow direct host access to a DB instance via Telnet or Secure Shell (SSH).” http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.SSL
RDS doesn’t know that the tunneling is taking place. The mysql command line client is connecting directly to RDS via an ssh tunnel. This definitely works. Regarding keys, ssh assumes a default key or you can specify a key with the “-i” flag as described by another poster above.
The ssh key is the key you would normally use to connect to an EC2 server. If you don’t know how to set that up, you will need to turn to AWS documentation. It is simple.
I agree. Something is missing here. Although even some of AWS documentation do not touch on pw or ssh keys either. Its hard to believe that people are connecting to AWS databases with data passing through internet in clear view.
This help shed some light on this tricky topic of secure connection between RDS db and desktop client https://forums.aws.amazon.com/message.jspa?messageID=173501
Many thanks for this article – I went through it, and to my surprise, everything worked right the first time. (Not used to that happening…)
I thought I’d also reply to Josh, maybe this will clarify what’s going on with tunnelling. He’s correct that Amazon does not allow *direct* SSH access to RDS instances. Likewise, Glen is correct that you can login to an EC2 instance that is in the same VPC, and from there, SSH into the RDS instance. But that can be a pain – tunnelling lets you connect from your local laptop directly to RDS. Josh was asking about the SSH keys – those need to be downloaded from AWS (and installed on the EC2 instance at launch). Then you can either configure it in the SSH config file, or use the ssh -i key.pem option. The config file is:
~/.ssh/config
Host east_fubar
Hostname 52.87.123.123 # EC2 instance
User ubuntu # depends on the AMI – might also be ec2-user/cloud-user, etc
IdentityFile /home/fubar/.ssh/ec2_key.pem # This key is from AWS, and is on the EC2 instance
ssh ubuntu@east_fubar # Once you can do this, should be able to tunnel:
This says, “ssh into this EC2 box as this user – then any packets on that port, pass-through to the RDS server”
ssh -N -L 1234:fubar.us-east-1.rds.amazonaws.com:3306 ubuntu@east_fubar
mysql -u db_admin -p -h 127.0.0.1 -P 1234
So you are not *directly* ssh’ing into the RDS box, but instead an EC2 box, which passes on the packets to RDS.
Here’s the string I use. This allows other machines on the same network to use the system running SSH as a proxy. In this example, I have an EC2 instance (-i key.pem user@ec2instance) which has access to the RDS database, and a system on my local lan which has SSH access to said ec2-instance — so, from the local system with ssh access to the ec2 instance:
ssh -q -o BatchMode=yes -fgN -L 3306:rds-writer-dns-name:3306 -i key.pem user@ec2instance
This saved me a lot of time, thank you!
Thank you so much. That was so easy to follow and straight to the point. Got it working in seconds!