Connecting to MySQL through SSH tunnel

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

 

check port with:
sudo netstat -tulpn | grep "3308"

-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 -cert.pub to identity filenames.

 

Using an Ansible role

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

To stop the service:
stop autossh-tunnel-client

 

My own Ansible role:

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

- name: Copy key file(s)
copy:
src: "{{ item.src }}"
dest: "{{ item.dest | default(item.src | basename) }}"
owner: "{{ item.owner | default('root') }}"
group: "{{ item.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

Parameters:

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

Leave a Comment

Your email address will not be published. Required fields are marked *