My PHP MySQL SSH tunneling experience

First, little background on why I’m writing this blog post… I have a client that has a “semi-dedicated” hosting account. In most respects, this account is just a fancy shared hosting account, but they say there are less accounts on the server. The client’s website is on that hosting account. The client also has a server at another location. I have root access to that server, and the client wants to be able to have that server do direct MySQL queries on the website’s database.

There are a number of ways that this could be handled, and here are some that I tried:

• MySQL sercure connection (SSL).
• Opening up an SSH tunnel for MySQL using PHP stream sockets.
• Opening up an SSH tunnel by starting SSH via PHP’s shell_exec function.
• Manually creating an SSH tunnel for MySQL via the Linux terminal.
• Automating a permanent SSH tunnel for MySQL by using autossh.

That was the actual order that I attempted to accomplish what we needed done. The MySQL secure connection was not possible because the host couldn’t make it happen. The PHP stream sockets idea was only half working, and I got to a point where it was impossible to debug. Very little information about stream sockets on the internet didn’t help. The shell_exec approach finally worked, but wasn’t ideal, because there was no way to ensure that it stayed up, or knowing when it was up or down. That finally lead to the manual creation of the tunnel via the terminal, and it’s automation via autossh.

So, for what did end up working, I wanted to document the issues I was having, and what it took to fix them.

The User Running The SSH Connection

When attempting to use PHP’s shell_exec, it’s important to know that anything run through shell_exec is run as the www-data user, at least on Ubuntu 16.04. This means www-data needed it’s own id_rsa and id_rsa.pub files, which Ubuntu wanted to put in /var/www/.ssh. That location was perfect, and I created them there, BUT that was the problem. I created them.

Also, when I tried using the SSH command in the terminal, I was fine until I wanted autossh to run it through systemd. In that case root is the default user, but even when I tried to configure SSH to use my id_rsa, it still wouldn’t work. In that case I just created a new user “autossh” and let that user create its own id_rsa and id_rsa.pub files. Once the autossh user created those files and tested the connection, the automated tunnel approach worked.

Let www-data or autossh Create Their Own RSA Key Pairs

Neither www-data or autossh can log in. So it wasn’t like I could just log out, and then log in as one of these users. Well, that’s not entirely true. I can do this in the terminal, just not via the desktop/gui. This is how it’s done:

# In the case of the autossh user, I had to create that user:
sudo useradd -m -s /bin/false autossh

# In the case of the www-data user, I had to temporarily edit
# the /etc/passwd file so that www-data would have shell access.
# I just switched /usr/sbin/nologin to /bin/bash

# Still logged in as me, I get root:
sudo su
[password]

# Now I become the autossh user:
su autossh

# Now generate an SSH key for the user (don't add a password!)
ssh-keygen -t rsa

# add the key to other server
cat /home/autossh/.ssh/id_rsa.pub | ssh -p 2233 me@example.com 'cat >> .ssh/authorized_keys'

# check the connection, verify key ...
ssh me@example.com

# Stop being autossh
exit

# Stop being root
exit

There were a few other gotchas to be aware of:

• File permissions for id_rsa and id_rsa.pub both require 0600. I did see online that some people use 0400. It’s obviously safest to use 0400 if it works for you.
• Directory permissions for .ssh needs to be 0700, or at least this is my experience and what I found when researching online.
• I had to chown the id_rsa and id_rsa.pub files to autossh:autossh because when creating them they were created as root:root.

So, unlike other blog posts you’ll find regarding an SSH tunnel for MySQL, I though this information would be good to put online, because it seemed to be lacking, or spread out all over the internet, and now it’s here in one place.

authssh’s SSH Config

We can clean up our SSH command:

# /home/autossh/.ssh/config w/ chmod 0700 w/ chown autossh:autossh
Host mysql_webserver_tunnel
  HostName example.com
  User me
  Port 2233
  IdentityFile /home/autossh/.ssh/id_rsa
  LocalForward 3307 127.0.0.1:3306
  ServerAliveInterval 30
  ServerAliveCountMax 3
  ExitOnForwardFailure yes

Prepare a systemd Service to Run at Boot

# sudo nano /etc/systemd/system/autossh-mysql-tunnel.service
[Unit]
Description=AutoSSH tunnel service example.com MySQL on local port 3307
Wants=network-online.target
After=network-online.target

[Service]
User=autossh
# -f flag does not work with systemd, so do this:
Environment="AUTOSSH_GATETIME=0"
ExecStart=/usr/bin/autossh -M 0 -T -N -F /home/autossh/.ssh/config mysql_webserver_tunnel

[Install]
WantedBy=multi-user.target

Tell systemd We Added Something

sudo systemctl daemon-reload

Start the Service Manually

sudo systemctl start autossh-mysql-tunnel.service

Enable at Boot

sudo systemctl enable autossh-mysql-tunnel.service

Disable at Boot

sudo systemctl disable autossh-mysql-tunnel.service

Check the Status of the Service

sudo systemctl status autossh-mysql-tunnel.service -l

Kill All autossh Services

sudo pkill -9 autossh