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 [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 `