Tunneling MySQL through SSH

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 [email protected] 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.

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 [email protected]

Tell init to reread the configuration file ` telinit q `