SSH tunneling for server-side applications

Port forwarding, often referred to as SSH tunneling, is defined by Wikipedia as:

...a name given to the combined technique of
1. translating the address or port number of a packet to a new destination
2. possibly accepting such packet(s) in a packet filter (firewall)
3. forwarding the packet according to the routing table

SSH tunneling creates an encrypted connection between two servers. As an example, you can use an SSH tunnel to connect a local database application to a remote MySQL instance via forwarded ports. The following instructions provide a supplemental tunneling alternative to the article Connect MySQL Workbench to an Acquia Cloud database.

Note

You can not SSH directly into database servers (fsdb and fsdbmesh machines), but you can SSH into web servers.

If you want an application to communicate with an Acquia Cloud server, execute the following at the command line:

ssh -i /path/to/id_rsa -f -L 1111:127.0.0.1:3306 [email protected] -N

A different approach would be to add your SSH private key first, and then open the tunnel afterwards:

ssh-add /path/to/id_rsa ssh -f -L 1111:127.0.0.1:3306 [email protected] -N

The -i /path/to/id_rsa option specifies the location of a private SSH key; this should pair with a public key on the Acquia server. The -N option instructs SSH not to run a command on the remote server, just to open the tunnel. The -L option instructs SSH to listen on port 1111 on the local server and tunnel everything on 127.0.0.1:1111 to acquia-server:3306 (that is, your remote server). You may wish to add the -f flag so that the process can run in the background, but this is not strictly necessary. Note that you can type ssh man at the command line to view additional options with full definitions. The & will background the process, and give you a pid (Process ID) that you can later use to end the process.

Once the SSH tunnel is in place, you can configure a local application (such as phpMyAdmin) to communicate over 127.0.0.1:1111 to use the tunnel. The host:port is 127.0.0.1:1111, as per the preceding command. The SSH tunnel is running on the local server on port 1111, which is forwarding to the Acquia Cloud database server.

Once you have completed the work that requires a tunnel, you can use the command kill -9 pid where pid is the process id from earlier.

MySQL

There are various scenarios where port forwarding is useful for MySQL, especially in the Acquia Cloud environment.

Single Tier

In the following example, we'll connect Sequel Pro to the Acquia database using the SSH tunnel. Sequel Pro is a third party tool for remote database applications.

• Open Sequel Pro.
• Use the Standard connection option.
• Enter 127.0.0.1 (localhost) as the MySQL Host.
• Obtain the Username and Password from the Acquia Insight page under Cloud > Databases.
• Optionally, enter a database name.
• Enter 1111 as the Port.

Some database drivers on some operating systems may produce a MySQL socket error when they are using localhost instead of an IP. You can try using your actual local IP address if you receive such an error.

Tunneling from the command line

You can also tunnel to the remote MySQL database from the command line.

In the $HOME/.ssh/config file, underneath the directive for the host in question, add the forwarding line:

Host myserver
HostName staging-12345.prod.acquia-sites.com
[ some lines removed ]
[ add this next line to forward local port 56789 to the remote 3306 port ]
LocalForward 56789 staging-12345.prod.acquia-sites.com:3306

Or, open an SSH connection just for tunneling:

ssh -f -L 56789:localhost:3306 user@host -N

Multi-tier

Customers on multi-tier do not get SSH access to their database servers, so SSH tunneling plays an important part to allow a connection through the web server to the fsdb. You can tunnel to a server you cannot otherwise access via SSH, assuming that server allows forwarded connections. This can be useful for instances where the database is on a different server than the web processes. First, run the following command:

ssh -f -L 56789:database-server.prod.hosting.acquia.com:3306 [email protected] -N

• 56789 is the port on the user's local machine
• -f allows MySQL to prompt for password
• -L local forward
• -N puts the connection to the background, and doesn't hold the terminal open
• 3306 is the port on the database server to connect to

Next, use the following command to open a shell connection to the DB (your MySQL credentials should replace <username> and <password> in this command):

mysql -u <username> -p <password> -h127.0.0.1 -P56789

The username and password can be found under the Details tab on the Database page. To avoid having the password visible in your terminal, omit the [password] (but keep the '-p') and you will be prompted to enter the password secretly.

Xdebug

XDebug allows for servers to send debug data to remote users, provided they have authenticated against the server. This data may be used by IDEs to track down bugs and issues with code on remote servers. Once remote enable has been set up on the server, the user (and support staff) should be able to connect to the server in order to gain access to Xdebug output.

This can be done in one of two ways:

On the command line during SSH connection:

ssh -R 9000:localhost:9000 user@host

Or in the $HOME/.ssh/config file, underneath the directive for the host in question, add the forwarding line:

Host myserver
HostName staging-12345.prod.hosting.acquia.com
[ some lines removed ]
[ add this next line to forward remote port 9000 to the local 9000 port ]
RemoteForward 9000 localhost:9000