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).

-- 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.

Explanation

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

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 

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

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