Secure Shell (ssh)

Secure Shell (ssh) is a means of establishing a secure connection with a remote system over a network. It is most commonly used for interactive shell sessions, but can also be used for encrypted tunnels and more.

Through the use of public key infrastructure, you can establish secure passwordless connections. This can be especially useful for automated scripting.

ssh tunnels

Accessing a database directly over the Internet is not a good idea, since the data (including the username and password used for authentication) is being transferred in the clear. A good workaround is to use an ssh tunnel to encrypt the traffic.

Example 1: database on target system

In this example we will connect to a MySQL database running on a machine that you have direct ssh access to.

ssh -L3307:localhost:3306 user@remotehost.example.com

This will allow you to connect to remote-host.example.com on port 3306 via your localhost on port 3307.

Then you can connect to the remote database over the tunnel using a standard MySQL client:

mysql -uroot -p -h127.0.0.1 -P3307

If you need to connect with JDBC, you can use a URI like this:

jdbc:mysql://localhost:3307/database-name

Example 2: database on a different system

If the database is on a system that you don't have direct access to, you may need to use another system as a proxy. This can happen, for example, if the database is behind a firewall, or if the grants don't permit you to connect directly.

The method is very similar to the first example. To create the tunnel:

ssh -L3307:remote-db-host:3306 user@remote-host.example.com

This creates an ssh session to remote-host.example.com, with a tunnel running from localhost:3307 to the database server on remote-db-host:3306. In other words, connections to the database are routed through remote-host.example.com to allow you to connect.

Then you can connect as shown in the first example.

Resources

• MySQL SSH Tunnel
• MySQL ssh tunnel Quickstart
• Connecting to MySQL via an SSH Tunnel
• Tunnelling with SSH
• Tunnelling Database Traffic with OpenSSH and Linux
• autossh - Automatically restart SSH sessions and tunnels

Resources

• 3 Steps to Perform SSH Login Without Password Using ssh-keygen & ssh-copy-id
• SSH Speed Tricks
• ssh-copy-id, sshfs, speed!
• OpenSSH key management: Part 1 | Part 2 | Part 3