Tunneling MySQL through SSH

Hold on Cowboy

This blog post is pretty old. Be careful with the information you find in here. It's likely dead, dying, or wildly inaccurate.

By tunneling MySQL through SSH you can do MySQL queries from a remote host without having MySQL listen on IP interface. There are two ways to do this depending on the need. The first way is just a connection through SSH that runs a specific query. The second way is to use SSH port forwarding to set up the encrypted tunnel.

These require that SSH use public keys to do an automatic login

1. Tunneling MySQL For a Single Predictable Query 

remotehost is the client running the queries_

mysqlhost_ is the MySQL server

On remotehost place this code in getquery.sh

#!/bin/bash THEDATE=$(date +%Y%m%d) ssh -l theuser mysqlhost 'mysql -u mysqluser -pmysqlpasswd databasename < filewithquery.sql' > $THEDATE.csv

Explanation

  • THEDATE is just that, today’s date
  • theuser is the user account on the mysqlhost server
  • mysqluser is the username for the mysql db
  • mysqlpasswd is the password for the mysqluser (note there is no space between ‘-p-’ and ‘mysqlpasswd’
  • datbasename is the database name you connecting to
  • filewithquery.sql is the file that holds the SQL query (see next) Your query results will now be stored in today’sdate.sql (i.e. 20070104.sql).

On mysqlhost place this in theuser home directory as filewithquery.sql

-- Your SQL query for example SELECT * FROM users WHERE list=2

Now on the remotehost you can run sh getquery.sh and the result will be in a date.sql file.

2. Tunneling the MySQL connection with port forwarding 

This will allow a connection to a MySQL server over a secure SSH connection and without having to have MySQL listen on the network (except localhost).

Testing the connection 

First log into the remotehost and run this command. ssh -N -L 3307:127.0.0.1:3306 theuser@mysqlhost.com This should log into the mysqlhost and set up the port forwarding. Remember you need to have public key authentication set up.

Explaination

  • -N tells ssh not to execute a remote command.

    Second try making a connection to the remote mysql server via the tunnel

mysql -u mysqluser -pmysqlpasswd -P 3307 database localhost

This should allow you to connect to the mysqlhost and run sql commands

Persistent Connection via Init 

Now you want to add the following line to /etc/inittab

sm:345:respawn:/usr/bin/ssh -N -L 3307:127.0.0.1:3306 theuser@mysqlhost.com

Tell init to reread the configuration file telinit q

Did this help you out? It took me a few days to piece together all this information together, I hope this saves you some time (who knows, maybe the future me will be thankful I wrote this down). Let me know your thoughts. shanestillwell@gmail.com