Accessing Your Database Remotely Via SSHTunnel

Accessing Your Database Remotely Via SSHTunnel Sounds good.Now Without wasting your much time lets go.As Your Know Our 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 Query Browser, or SQLyog, you need to create an SSH tunnel to your database host/instance.

For example, you want to run pgAdmin or MySQL Query Browser 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.

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.

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 Query Browser 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,