At my work I was created a new Staging or Demonstration server. This is a server that I can use to show preview of web applications to clientele. As I develop on a laptop my own system is not always available so a dedicated system for Staging use is beneficial to my clients. My sysadmin provided a new system running Ubuntu 14.04 LTS. And he stated: I have also given you sudo access to update, install and uninstall as well as write to the apache2 directory and php5 directory. Mysql 5.5 is on there and you have an account accessible from localhost with the username dgurba. I don’t like doing work over the command line for database development, I prefer phpMyAdmin which I’ve used for many years. So I wanted to make use of phpMyAdmin locally installed on my laptop, to this remote server over an SSH connection.
Creating an SSH Tunnel and Configuring phpMyAdmin
SSH tunneling is a means to talk to a remote MySQL instance as if it resides on the local computer. A MySQL instance typically listens on port 3306 for payloads (query, authentication, etc). Since I run MySQL locally this is the ssh command I use to setup a tunnel to my remote server:
/usr/bin/ssh -fNL 3307:localhost:3306 email@example.com
As you can see this command breaks down as follows:
- using the -f option requests ssh to go to background just before command execution. This is useful for having the SSH tunnel run in the background. If you are using a password to connect to the remote server, you’ll want to remove this argument.
- using the -N option tells ssh to not execute a remote command. This is useful for just forwarding ports, which is what we want to accomplish.
- the -L option specifies that the given port on the local (client) host is to be forwarded to the given host and port on the remote side.
- a useful way to get further information on what SSH is doing if you encounter problems is to pass the -v option for verbose output.
So the generic form of this command is:
ssh -fNL LOCAL_PORT:localhost:REMOTE_PORT user@REMOTE_IP
After issueing the above SSH command you can see if the tunnel is running using the ps command and looking for ssh applications:
> ps -aux | grep ssh dgurba 2806 0.0 0.0 10616 316 ? Ss 11:24 0:00 ssh-agent -s dgurba 3139 0.0 0.1 418896 21820 ? Sl 11:24 0:00 /usr/bin/python /home/dgurba/apps/sshplus/sshplus.py dgurba 7122 0.0 0.0 48276 912 ? Ss 12:44 0:00 /usr/bin/ssh -fNL 3307:localhost:3306 firstname.lastname@example.org
This verifies our tunnel is up and running (if it’s not, you may want to check your local firewall configuration).
In order for phpMyAdmin to make use of this we must add a server entry to our local systems config.inc.php file. As of this writing the current stable version of phpMyAdmin on Ubuntu 14.04 is: 188.8.131.52deb0.1
$i++; $cfg['Servers'][$i]['auth_type'] = 'cookie'; // phpMyAdmin should communicate over the wired-up // SSH port which should be already connected previously. $cfg['Servers'][$i]['port'] = 3307; $cfg['Servers'][$i]['verbose'] = 'ID Web-Stages [via SSH tunnel]'; // The host is "localhost", and using the above port. // The tunnel does the rest of the work making phpMyAdmin // think that it's a local resource. $cfg['Servers'][$i]['host'] = '127.0.0.1'; $cfg['Servers'][$i]['connect_type'] = 'tcp'; $cfg['Servers'][$i]['compress'] = FALSE; $cfg['Servers'][$i]['extension'] = 'mysqli'; $cfg['Servers'][$i]['AllowNoPassword'] = false;
The most important thing to realize about this $cfg instance (which tripped me up too) is that the host value is 127.0.0.1 or localhost. I had put in the remote IP address here — But we’re using a tunnel!
We can then go to http://localhost/phpmyadmin and select our remote host MySQL instance and attempt to login with our credentials. This all occurs over the SSH tunnel and we can gain access:
As you can see from this screenshot:
- we’re on a localhost url (our development system).
- we are logged in as dgurba@localhost (the remote system, thinks we’re local)
- we are running stable versions of MySQL remotely; and of phpMyAdmin locally.
So it’s all working! GREAT! But if we reboot our development system we lose our tunnel :(, can we have our development system create the tunnel on boot or login? You betcha.
Activating the SSH Tunneling on (Re)boot on your Development System
Now that the whole setup works we want to be hassle free without having to remember if the tunnel exists before trying to use phpMyAdmin.
What we want to do is run our above SSH command to create the tunnel when the system starts. Under Linux you may be familiar with the .profile, .xinit or .bashrc files. Many of these load on user login or GUI subsystem init — but we simply want this tunnel to be made on startup. And for instance the .profile file is also process on FTP and other logins — we don’t want tunnels spawning weirdly all over the place! So, we will add our tunnel creation to the /etc/rc.local file. I tend to place custom commands in a seperate file and call the file from rc.local. In the event of error I can easily CAT the file and not rc.local which may be offlimits of some system configurations.
In $HOME/init_scripts/create_ssh_tunnels.sh I place the following commands:
#!/bin/sh # David Gurba <email@example.com> # # This file should be called by your system's rc.local file to be run at # startup. For Ubuntu 14.04 that file is at /etc/rc.local, depending on your # OS distribution it's path may be different. # # These tunnels are created via rc.local as opposed to .profile, or .xinit as # other init files such as .profile are run on every login session. You do not # want to be creating tunnels all over the place. /usr/bin/ssh -fNL 3307:localhost:3306 firstname.lastname@example.org
I use full system paths in my script(s) because typically scripts when run by the system may be in a different environment than what you use when you login, if you don’t know what $PATH values are set for shortcut commands (like just call ssh arg1 arg2) may fail to be found — so full paths is just a debug/safety precaution that assures the script behaves as expected later (or is being used as strictly as possible).
In /etc/rc.local I then added the following commands:
#!/bin/sh -e # # rc.local # # This script is executed at the end of each multiuser runlevel. # Make sure that the script will "exit 0" on success or any other # value on error. # # In order to enable or disable this script just change the execution # bits. # # By default this script does nothing. exec 2> /tmp/rc.local.log # send stderr from rc.local to a log file exec 1>&2 # send stdout to the same log file set -x # tell sh to display commands before execution /home/dgurba/init_scripts/create_ssh_tunnels.sh exit 0
We can then restart the system and see if our tunnel is made. Unfortunately for me it was not. Does our debug log have any useful information:
+ /home/dgurba/init_scripts/create_ssh_tunnels.sh OpenSSH_6.6.1, OpenSSL 1.0.1f 6 Jan 2014 debug1: Reading configuration data /etc/ssh/ssh_config debug1: /etc/ssh/ssh_config line 19: Applying options for * debug1: Connecting to web-stage01.id.ucsb.edu [184.108.40.206] port 22. debug1: Connection established. debug1: permanently_set_uid: 0/0 debug1: identity file /root/.ssh/id_rsa type -1 debug1: identity file /root/.ssh/id_rsa-cert type -1 debug1: identity file /root/.ssh/id_dsa type -1 debug1: identity file /root/.ssh/id_dsa-cert type -1 debug1: identity file /root/.ssh/id_ecdsa type -1 debug1: identity file /root/.ssh/id_ecdsa-cert type -1 debug1: identity file /root/.ssh/id_ed25519 type -1 debug1: identity file /root/.ssh/id_ed25519-cert type -1 debug1: Enabling compatibility mode for protocol 2.0 debug1: Local version string SSH-2.0-OpenSSH_6.6.1p1 Ubuntu-2ubuntu2 debug1: Remote protocol version 2.0, remote software version OpenSSH_6.6.1p1 Ubuntu-2ubuntu2 debug1: match: OpenSSH_6.6.1p1 Ubuntu-2ubuntu2 pat OpenSSH_6.6.1* compat 0x04000000 debug1: SSH2_MSG_KEXINIT sent debug1: SSH2_MSG_KEXINIT received debug1: kex: server->client aes128-ctr email@example.com none debug1: kex: client->server aes128-ctr firstname.lastname@example.org none debug1: sending SSH2_MSG_KEX_ECDH_INIT debug1: expecting SSH2_MSG_KEX_ECDH_REPLY debug1: Server host key: ECDSA eb:3b:da:de:0f:8d:5e:67:d6:1f:90:32:7f:d0:ff:f1 debug1: read_passphrase: can't open /dev/tty: No such device or address Host key verification failed.
I see 2 items of note here:
- The shell script we wrote to spawn the SSH tunnel is running as the root user, not as dgurba. The remote account expects my dgurba id_rsa credentials — not those of root.
- It complained something about a TTY that did not exist.
These problems exist because we’re not running as the correct user. We could copy the id_rsa file from dgurba to root, but ewww. We could also make the create_ssh_tunnels.sh script owned by root and chmod 700 — but again eww.
I ended up playing with setuid bit on the script to try and make it always run as the dgurba user, but it turns out setuid on shebanged scripts are usually ignored (see http://unix.stackexchange.com/a/2910/86052).
I ended up changing my rc.local file to run the following command:
// We use the "su" command to run the given command as the // desired user. su -lc "/home/dgurba/init_scripts/create_ssh_tunnels.sh" dgurba
Now you can securely connect to and manage your MySQL instance! 🙂 wooo!
Looking for quality web hosting? Look no further than Arvixe Web Hosting!