Accessing MySQL through an SSH tunnel
Wednesday, June 4th, 2008I have been experimenting with SSH the secure shell lately and wanted to write a little about it. When I needed to access a remote computer I used to use SSH as a simple replacement for telnet providing an encrypted channel and supporting public-key based authentication instead of plain clear-text passwords. For example:
ssh gerrit@example.com
ssh example.com -l gerrit
I also used it to copy files through SSH with scp:
scp helloworld.txt gerrit@example.com:~/test/newhello.txt
scp gerrit@example.com:/test/newhello.txt .
Or to transfer files via sftp:
sftp gerrit@example.com
!ls
ls
mget test1.txt
mput test2.txt
But there is so much more you can do with SSH. You can also forward network traffic through SSH as a tunnel, and the possibilities really seem endless. The following command copies a directory tree through a gzipped SSH tunnel:
tar cf - test | ssh -C gerrit@example.com "cd ~ && tar xpf -"
This first creates a tar-file (“cf”) of test to stdout (“-”). Using SSH this stream is then gzipped (“-C”) and moved to example.com. And on the receiving end, the tar-file from stdin (“-”) is extracted with permissions to our home directory (“xpf”).
Local Port Forwarding
You can also forward traffic on single ports through an SSH tunnel. For example:
ssh -L 6080:example.com:80 gerrit@basement.lan
The lan-domains are just for demonstration purposes…the hosts can be located anywhere and don’t really have to be in the LAN. With netstat -ant you will find that localhost or gerrit.lan is now listening on port 6080. If you load http://localhost:6080 in your web-browser it will display example.com. Traffic will flow through localhost’s port 6080 through the secure tunnel (marked in red) to basement.lan. It is basement.lan who accesses example.com on a regular, insecure channel and returns traffic back through the tunnel to localhost. This is not limited to port 80, it can also be used for IMAP, POP3, SMTP, VNC sessions or other protocols.
If ssh-daemon and example.com are on the same machine, you can build a tunnel as follows:
ssh -L 6080:localhost:80 example.com
In this example, localhost:80 refers to example.com’s localhost, not the localhost ssh has been launched from.
SSH and the client-application (lynx in the example above) don’t have to be on the same machine. SSH’s command line option -g can be used to give other computers access to gerrit.lan:6080.
ssh -g -L 6080:example.com:80 gerrit@basement.lan
Alice.lan will now be able to access example.com through gerrit.lan and basement.lan.
Remote Port Forwarding
ssh -R 6080:localhost:80 gerrit@basement.lan
Remote port forwarding is similar to local port forwarding, but works in the opposite direction. Instead of securing the channel between web-client and ssh-daemon, you are securing the channel between web-server and ssh-daemon here. All traffic between web-server on gerrit.lan and the ssh-daemon on basement.lan is secure, the web-client accesses port 6080 on basement.lan, through the regular insecure channel. This can be used to connect a remote intranet-server with the local network. If you can’t connect, you might have to add GatewayPorts yes to /etc/ssh/sshd_config on basement.lan.
MySQL
All these tunneling and forwarding methods can be chained together and combined in a countless number of ways across a countless number of hosts that would really exceed the scope of this document.
Webhosting providers generally allow access to MySQL databases from localhost only. Your webpages are allowed to access them, and you can use tools like PHPMyAdmin to do maintenance, but for security reasons they can’t be accessed directly from external hosts. Sometimes you may need direct access to a database, for example if you want to run reports from another application, data-mining scripts, statistical analysis with SPSS, use the MySQL Query Browser or create backups with mysqldump.
That’s a really nice, practical real-world example for SSH tunnels. You can work around these provider limitations by building a tunnel between your local machine and the remote database host. The following solution is based on an interesting article in issue 06/2008 of the iX magazine. It uses ssh, netcat (nc) and xinetd to establish an ssh-channel automatically whenever the local client attempts to connect to the remote MySQL database. I made a few changes to the script because the database-host may not be on the same machine as the ssh-daemon [in my case it wasn’t].
ssh -l gerrit -N -L [bind_address:]12345:dbhost.lan:3306 basement.lan
mysql --port=12345 -h 127.0.0.1 --user=dbgerrit --password=dbpasswd mydatabase
-
-lis the username on remoteServer -
-Ninhibits the login-shell -
-Lforwards the local port (see above). dbhost.lan:3306 is forwarded through basement.lan to localhost:12345. The local port can optionally be linked to an ip-address (bind_address). - you can now access the remote database via localhost:12345
-
-h 127.0.0.1forces a connection through the given port. Normally, MySQL would use a local socket and ignore the port parameter.
Using a keypair to simplify authentication
First, create a keypair and merge them to a single keyring:
ssh-keygen -t rsa
ssh-keygen -t dsa
cd ~/.ssh
cat id_rsa.pub > public_keys
cat id_dsa.pub >> public_keys
Transfer the keyring and append it to the list of authorized keys. Make sure the directory .ssh exists on the destination host and that you have permissions to access it.
scp public_keys gerrit@basement.lan:.ssh
ssh gerrit@basement.lan
cd ~/.ssh
cat public_keys >> authorized_keys
rm public_keys
Make sure the permissions are set properly [via]:
chmod go-w ~
chmod 700 ~/.ssh
chmod 600 ~/.ssh/authorized_keys
exit
Now you should be able to log in without having to enter a password, try:
ssh gerrit@basement.lan
ssh -l gerrit basement.lan hostname
Automatic tunnels with xinetd and netcat
With xinetd and netcat you can automate the creation of the SSH-tunnel and simply provide a local port for tools like MySQL Query Browser or mysqldump. Netcat (nc), the tcp/ip swiss army knife, should already be available, if not you can install it using
apt-get install netcat xinetd
To test xinetd, you can use the echo service which reads incoming data and returns them without a change. To enable the echo service
nano /etc/xinet.d/echo
In the tcp section, set disable = no.
/etc/init.d/xinetd reload
cat /etc/services | grep echo
should reveal that echo is assigned to port 7.
With
netstat -an | grep :7
you can make sure that xinetd provides the echo-service and that it’s actually listening on port 7. If that’s not the case, check the log files in /var/log.
nc basement.lan echo
allows you to check if the echo service returns what you provide through the standard input.
With xinetd, netcat and ssh-tunnel we have everything we need to setup a proxy server. Each remote database will get a representative port on the proxy-server which is watched by xinetd. If a local database client tries to connect to this port, the daemon will start a shell script that opens the SSH-tunnel and starts netcat to copy data back & forth between the database server and local client.
In /etc/xinetd.d create a service configuration for our proxy
nano /etc/xinetd.d/mysql-proxy-3310
# default: on
service mysql-proxy-3310
{
port = 3310
type = UNLISTED
socket_type = stream
wait = no
user = gerrit
server = /opt/portproxy.sh
server_args = dbhost.lan 3306 basement.lan gerrit
log_on_success += USERID PID HOST EXIT DURATION
log_on_failure += USERID HOST ATTEMPT
disable = no
log_type = FILE /tmp/portproxy.xinetd.log
}
For more details, see also man xinetd.conf.
Next, create the proxy script:
nano /opt/portproxy.sh
#!/bin/bash
# Usage: $0 dbServerHost dbServerPort remoteHost remoteUser
# prepare logging
LOGFILE=/tmp/`basename $0 .sh`.log
function log() {
echo $$: $@ >>$LOGFILE
}
log start $0 at `date` with arguments -"$@"-
# find a free port, assign free port to $port
function getFreePort() {
while true
do
port=$RANDOM
[ $port -lt 1025 ] && continue
netstat -an | fgrep -q ':'$port || break
done
}
# the function does the work (see comments inside), arguments:
# $1 the remote database host
# $2 the remote database port
# $3 the remote host for tunnel
# $4 the remote user for tunnel
function doWork() {
# get free local port for the ssh tunnel
getFreePort
log free port is $port
# build tunnel to remote host
log start tunnel to $4@$3 and database $1:$2
ssh -l $4 -C -L $port:$1:$2 -N $3 >> $LOGFILE 2>&1 &
# wait max up to 20 seconds for the tunnel to start
s=1
while ! netstat -an | grep -q ':'$port
do
log waiting for ssh tunnel -$s-
sleep 1
s=$(($s + 1))
if [ $s -gt 20 ]
then
log unable to start ssh tunnel
exit 1
fi
done
log tunnel built
# start I/O copying
log start nc copy program for stdin/stdout and localhost:$port
nc localhost $port 2>>$LOGFILE
# kill background tunnel
log killing ssh tunnel
kill %1
}
# test arguments: $0 dbServerHost dbServerPort remoteHost remoteUser
if [ $# -ne 4 ]
then
log `basename $0` was called with an illegal argument list: "$@"
exit 1
fi
# do the work
doWork $1 $2 $3 $4
log end of $0 at `date`
exit 0
As mentioned before, this is based on the iX magazine, issue 6/2008. I modified the script to introduce a new parameter remoteHost. The original script assumed that the database host was on localhost. As this may not always be the case, I build a tunnel to remoteUser@remoteHost, and then forward dbServerHost:dbServerPort through the tunnel.
Make sure the script is executable:
chmod 755 /opt/portproxy.sh
Restart xinetd:
/etc/init.d/xinetd reload
Now, if you connect to the MySQL database on port 3310, it will automatically establish a connection to the remote server. Try
mysql --port=3310 -h 127.0.0.1 --user=dbgerrit --password=mydbpasswd mydatabase
mysqldump --port=3310 -h 127.0.0.1 --user=dbgerrit --password=mydbpasswd mydatabase > mydatabase-backup.sql
I find this solution brilliant, and I’m glad I could follow and understand how it works. 
With little adjustments this could be used for a lot of other applications as well.
I hope I got everything right. Please let me know if I made a mistake somewhere. Although I mostly wrote it down to support my memory, I hope any of this was useful, helpful or interesting to you, too.