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
jay says:
Hi , I have been trying to set up a ssh connection with public private key from an ec2 server to my mysql database server and connect to my database. I have been successful to connect to my db and access data only from shell but not from a php script. The ssh connection does not go through. I am thinking it fails because the apache user is not able to read the public or private key since it was created by the shell user(ec2 user). I am going try following your example of creating an autossh user and try that way. will update once i try it.