Knowledge Base/Engine Yard Cloud Documentation/Manage your Database

Access Your Database Remotely Through an SSH Tunnel

Engine Yard
posted this on February 16, 2012 10:02 AM

Updated: December 9th, 2013

Your database instance is not directly accessible from outside the environment. This is a good thing because a database cannot reliably maintain the security and patch levels like SSH can. However, it means that to access your database from an external database tool, such as pgAdmin, MySQL Workbench, or SQLyog, you need to create an SSH tunnel to your database host/instance.

For example, you want to run pgAdmin or MySQL Workbench on your laptop and you want to use this tool to examine your application database: You create an SSH tunnel from your laptop (the local) to your database instance (the remote) on Engine Yard Cloud.

You can also use SSH tunnels to set up offsite replication.

This page describes:

About SSH tunnels

An SSH tunnel links a port on your local machine to a port on a remote host. When these ports are linked, anything communicated to the local port is passed through SSH to the remote port; likewise, any communication to the remote port is passed back through SSH to the local port. The added benefit of this setup is that the communications between your local machine and the remote host is encrypted by the SSH connection.

Use MySQL Workbench to connect to MySQL Server

As an alternative to setting up an SSH tunnel manually, you can use MySQL Workbench to connect to a MySQL Server using TCP/IP over an SSH connection. See MySQL Workbench for more information.

Set-up SSH Keys

Before you can set up your SSH tunnel, you need to ensure that you have configured and installed SSH keys on your running environment.

Create the SSH tunnel manually

The syntax for creating the SSH tunnel is ssh -L [local port]:[database host]:[remote port] \
[username]@[remote host]

  • [local port] The local port your database tool connects to.
    If you have a MySQL installation on your local machine, it runs on port 3306 by default; therefore, don’t use 3306 for the local port. For example, use 3307 instead.
    If you have a PostgreSQL installation on your local machine, it runs on port 5432 by default; therefore, don’t use 5432 for the local port. For example, use 5433 instead.

  • [database host] The hostname or IP address of the database instance that you are tunneling to. If the [remote host] is the database instance you will want to set this to 127.0.0.1 (so it refers to itself). If you used an application instance as [remote host] then you can use the value of "host:" from your database.yml instead.

  • [remote port] The port that your remote database listens for connections on.
    For MySQL databases, this is 3306 by default.
    For PostgreSQL database, this is 5432 by default.

  • [username] the user for the database instance. The default user for the Engine Yard Cloud database is deploy.

  • [remote host] the remote instance your tunnel will connect to the database through. This can be the database instance itself, or any instance within the database environment.

The tunneling command opens an SSH session with the [remote host] specified. The tunnel will work as long as that SSH session is active. If the session window is consuming valuable workspace we recommend minimizing it as running tunnels in the background can lead to multiple tunnels and port conflicts.

To create and test the SSH tunnel for a MySQL database

  1. In a terminal window on your local machine, type:

    ssh -L 3307:ec2-172-16-139-19.us-west-1.compute.amazonaws.com:3306 \
    deploy@ec2-174-129-17-196.compute-1.amazonaws.com

    where
    3307 is the local port,
    ec2-172-16-139-19.us-west-1.compute.amazonaws.com is the database host,
    3306 is the listening port,
    deploy is the database username, and
    ec2-174-129-17-196.compute-1.amazonaws.com is the remote host.

  2. Before connecting the external database tool such as MySQL Workbench or SQLyog, test the connection with a simple tool such as the database console, mysql.
    (Your tunnel needs to be running for this test.)

    Type

    mysql -udeploy -p -P 3307 -h 127.0.0.1 

    where
    deploy is the database username on the remote host and
    3307 is the local port.

    You are prompted for your database password.

    Note: for the -h argument, it is necessary to use 127.0.0.1 instead of localhost.

To create and test the SSH tunnel for a PostgreSQL database

  1. In a terminal window on your local machine, type

    ssh -L 5433:ec2-172-16-139-19.us-west-1.compute.amazonaws.com:5432 \
    deploy@ec2-174-129-17-196.compute-1.amazonaws.com

    where
    5433 is the local port,
    ec2-172-16-139-19.us-west-1.compute.amazonaws.com is the database host,
    5432 is the listening port,
    deploy is the database username, and
    ec2-174-129-17-196.compute-1.amazonaws.com is the remote host.

  2. Before connecting the external database tool such as pgAdmin, test the connection with a simple tool such as the database console psql.
    (Your tunnel needs to be running for this test.)

    Type

    psql -Udeploy -d myapp -p 5433 -h 127.0.0.1

    where
    deploy is the database username on the remote host and
    5433 is the local port.

    You are prompted for your database password.

Setting up an SSH tunnel using a custom Chef recipe

The procedure above explained how to set up the SSH tunnel manually.

An alternate way to set up an SSH tunnel is through this custom cookbook: //github.com/engineyard/ey-cloud-recipes/tree/master/cookbooks/ssh_tunnel

This recipe installs:

  • An init script that sets up the SSH tunnel from the instance in the background
  • A config file to have monit watch over the tunnel and keep it open

To create an SSH tunnel using the Engine Yard ssh_tunnel custom Chef recipe

More information

For more information about...                                         See...
Finding database hostname and password Find key information about your database.                                     

If you have feedback or questions about this page, add a comment below. If you need help, submit a ticket with Engine Yard Support.

 

Comments

User photo
Jonathan Thomas
Inquisitive Minds, Inc

Your example didn't work for me.

I believe the first host/ip needs to be localhost or 127.0.0.1

e.g.

ssh -L 3307:ec2-174-129-17-196.compute-1.amazonaws.com:3306 deploy@ec2-174-129-17-196.compute-1.amazonaws.com 
=>
ssh -L 3307:127.0.0.1:3306 deploy@ec2-174-129-17-196.compute-1.amazonaws.com 
July 14, 2012 11:58 AM
User photo
Keri Meredith
Engine Yard Inc.

Hi Jonathan, Thank you for your feedback. We updated the [database host] syntax to reflect the situation you encountered. 

We appreciate your taking the time to provide feedback - and to help keep the docs up to date.

thanks again! kjm

July 20, 2012 04:41 PM
User photo
Jonathan Thomas
Inquisitive Minds, Inc

Thanks. Don't you also want to update the mysql (& postgres) examples too (i.e. to match the cut & paste I used in my previous comment)?

July 20, 2012 06:19 PM
User photo
Tyler Poland
Engine Yard Inc.

Hi Jonathan,

The most common use case for these tunnels is that a separate system user (other than `deploy`) ends up getting created on the application instances with limited privileges. As a result most of these tunnels get connected through an application instance instead of directly through the database as in your example. The description of [database host] has been updated to reflect this.

Thank you for your assistance with this,

Tyler

July 24, 2012 08:52 AM
User photo
Kerosene Games
Orchestra-appfog-kerosenegames-com

For anyone looking to set up MySql Workbench specifically, the above commandline tunnel isn't strictly necessary. The steps to follow are as follows:
1. Set up SSH keys as in the "Prerequisites" link above
2. Click "New Connection" in Workbench
3. Specify whatever connection name you like, and set the "Connection Method" to "Standard TCP/IP over SSH"
4. Fill out the details as follows, where DB_PASS, DB_USER, DB_HOST and DB_NAME are the equivalents that would be passed to your application (besides sshing into your instance and getting these values from /data/<appname>/current/config, a php application can get them using phpinfo):

SSH Hostname- DB_HOST:22
SSH Username- DB_USER
SSH Password- DB_PASS
SSH Key File- (browse to the id_rsa file created earlier, typically ~/.ssh/id_rsa)
MySQL Hostname- 127.0.0.1
MySQL Server Port- 3306
Username- DB_USER
Password- DB_PASS
Schema- DB_NAME


5. Click "Test Connection", your connection should succeed. Click "Ok" and you can now use this connection like any other.

April 18, 2013 07:26 AM
User photo
David Rossellat
squar

would it be possible to see an example of the ey cookbook recipe with the same values used in this manual ssh tunnel set-up? been trying to use the ssh_tunnel recipe but I'm not having any luck. please help.

January 07, 2014 06:19 PM
User photo
Tyler Poland
Engine Yard Inc.

Hi David,

While this knowledge base item provides similar functionality to our SSH Tunnel cookbook the use case for each of these is very different. The instructions here are designed to be used for setting up a tunnel from your local system to your Engine Yard database for use with a third party database tool.

The SSH Tunnel cookbook is designed to configure a tunnel that allows your database to access an external system (e.g. for replication), or to allow application resources to access a database that is not contained in its local environment. To run this cookbook it must be added using a `include_recipe "ssh_tunnel"` in main/recipes/default.rb, and the ssh cookbook needs to be configured under ssh_tunnel/recipes/default.rb.

If you are continuing to have trouble with the SSH Tunnel cookbook please open a request with our Support team so we can answer any questions you might have. If you do request additional support for this we may be able to provide some additional documentation around the cookbook that can better assist others.

Thank you,
Tyler

January 08, 2014 12:42 PM
User photo
David Rossellat
squar

Thanks Tyler - i was able to repurpose the ssh tunnel erb templates and I'm now testing it. I'm now running this particular recipe without the ey framework, which seems to require the engineyard gem. 

If I get stuck, I'll submit a support ticket.

Thanks!

January 08, 2014 01:13 PM