Connect to MySQL ElastiCache through EC2
You can quickly and efficiently use SSH to safely access data in your remote MySQL RDS data stores with just a single command.
This tip not only enables you to access your remote server as if it was local and take advantage of local tools, but also modify data remotely, download MySQL backups, and do it all securely, through an SSH encrypted connection.
You can even use desktop GUI tools (such as MySQL Workbench) and CLI desktop tools to easily modify the remote MySQL database remotely, as well as dump database backups, execute remote queries, view stats (including in a local dashboard app!), or optimize queries. This technique can also be used from an on-site server to remotely monitor your MySQL RDS service in the cloud.
This works by creating an SSH tunnel into your EC2 instance that forwards an port on your local host to the remote MySQL instance.
For example, on Linux or OSX (Putty on Linux and Windows examples follow):
ssh -L 3306:myinstance.123456789012.us-east-1.rds.amazonaws.com:3306 126.96.36.199
This will forward port
3306 from your local desktop to the remote MySQL rds server through your EC2 instance at 188.8.131.52.
You can easily set up this tunnel every time you log into your remote EC2 instance and log into it with whatever name you prefer:
Add this to .ssh/config:
Host my_ec2_instance Hostname 184.108.40.206 Localforward 3306 myinstance.123456789012.us-east-1.rds.amazonaws.com:3306
And you can then access your remote MySQL server as if it was running locally:
mysql --host=localhost --port=3306
Note: if you are already running a local MySQL server, the port forward will not work. In that case, change the first
3306 above to a different unused port on your local machine, like
3406, and then pass that port to your client when accessing, such as:
client = Client(("localhost", 3406)) # note the new port number
You can actually use this to set up a whole fleet of remote MySQL servers, just by specifying different ports for each tunnel. (You can do this as many times as you like.)
On Putty on Windows or Linux, simply add the following settings to a new or existing session. Find your session and select Tunnels from under the Connection/SSH menu:
3306 under source port and
myinstance.123456789012.us-east-1.rds.amazonaws.com:3306 under Destination and click Add:
When you log into the remote EC2 instance with this local forward, you'll be able to access MySQL as if it was locally available on port
3306. (Again, if you are already running a MySQL server on port
3306, change the Source port to a different port number.)
If you'd like to do this through a jump box (see our jump box HOWTO), you should also add a similar
Localforward to the .ssh/config on the jumpbox as well so that the forward first goes through the jump box intance and then on to the EC2 instance.
Keep the tunnel running
Keep the tunnel up and running by using a tool like autossh, which essentially turns the tunnel into a virtual private network connection, but faster and leaner, with a tiny degree of exposure instead of exposing one network to another.
NEW! Check out our AutoSSH howto.
As always, use Userify to distribute your SSH keys and save time and effort!