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
remotehost is the client running the queries_
mysqlhost_ is the MySQL server
On remotehost place this code in getquery.sh
ssh -l theuser mysqlhost 'mysql -u mysqluser -pmysqlpasswd databasename < filewithquery.sql' > $THEDATE.csv
- 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
and the result will be in a date.sql file.
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).
First log into the remotehost and run this command.
ssh -N -L 3307:127.0.0.1:3306 firstname.lastname@example.org
This should log into the mysqlhost and set up the port forwarding. Remember you need to have public key authentication set up.
- -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
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@example.com
Tell init to reread the configuration file
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. firstname.lastname@example.org