Connecting to MySQL through SSH tunnel

In this post we see how to connect to a MySQL server using SSH tunnel and local forwarding.

This command will create a tunnel in the background:

ssh -p 32642 -L 3308:example:3306 -N -i ~/.ssh/abdel -f
mysql -h -P 3308 -u user -p db

This is what each parameter means:

-p port
Port to connect to on the remote host. This can be specified on
a per-host basis in the configuration file.

-L [bind_address:]port:host:hostport
Specifies that the given port on the local (client) host is to be
forwarded to the given host and port on the remote side. This
works by allocating a socket to listen to port on the local side,
optionally bound to the specified bind_address. Whenever a con-
nection is made to this port, the connection is forwarded over
the secure channel, and a connection is made to host port
hostport from the remote machine. Port forwardings can also be
specified in the configuration file. IPv6 addresses can be spec-
ified with an alternative syntax:
[bind_address/]port/host/hostport or by enclosing the address in
square brackets. Only the superuser can forward privileged
ports. By default, the local port is bound in accordance with
the GatewayPorts setting. However, an explicit bind_address may
be used to bind the connection to a specific address. The
bind_address of “localhost” indicates that the listening port be
bound for local use only, while an empty address or ‘*’ indicates
that the port should be available from all interfaces.

-N Do not execute a remote command. This is useful for just for-
warding ports (protocol version 2 only).

-f Requests ssh to go to background just before command execution.
This is useful if ssh is going to ask for passwords or passphrases,
but the user wants it in the background. This implies -n.
The recommended way to start X11 programs at a remote site
is with something like ssh -f host xterm.

-i identity_file
Selects a file from which the identity (private key) for public
key authentication is read. The default is ~/.ssh/identity
for protocol version 1, and ~/.ssh/id_dsa, ~/.ssh/id_ecdsa,
~/.ssh/id_ed25519 and ~/.ssh/id_rsa for protocol version 2.
Identity files may also be specified on a per-host basis in the
configuration file. It is possible to have multiple -i options
(and multiple identities specified in configuration files).
ssh will also try to load certificate information from the
filename obtained by appending to identity filenames.


To check if the command was successful run: sudo netstat -tulpn | grep "3308"

You should see something like:

$ sudo netstat -tulpn | grep "3308"
tcp 0 0* LISTEN 14634/ssh
tcp6 0 0 ::1:3308 :::* LISTEN 14634/ssh

Using an Ansible role

To list jobs interacting with ssh: initctl list | grep ssh

To stop the service:
stop autossh-tunnel-client


My own Ansible role:

- name: Install package
name: ssh
state: present
become: yes

- name: Copy key file(s)
src: "{{ item.src }}"
dest: "{{ item.dest | default(item.src | basename) }}"
owner: "{{ item.owner | default('root') }}"
group: "{{ | default(item.owner) | default('root') }}"
mode: "{{ item.mode | default('0600') }}"
validate: 'echo %s'
with_items: "{{ params['ssh_tunnel'].keys_map }}"
become: yes

- name: Run SSH tunnel on background
command: ssh -f "{{ params['ssh_tunnel'].user }}"@"{{ params['ssh_tunnel'].host }}" -p "{{ params['ssh_tunnel'].port }}" -L "{{ params['ssh_tunnel'].forward }}" -N -i "{{ item.dest | default(item.src | basename) }}"
with_items: "{{ params['ssh_tunnel'].key_map }}"
become: yes


- src: '../../../private/id_rsa'
dest: ~/.ssh/id_rsa
host: ''
forward: '3308:some:3306'
port: 32642
user: tunnel


Check as well: Using Putty to connect to an SHH tunnel