Using an SSH tunnel, or Putty, to securely access a remote MySQL server

Date: Fri May 27 2016 MySQL »»»» Mac Tutorials
The MySQL clients allow you to access a remote MySQL server pretty easily. You just specify the host name on the command line. But what if you want the data encrypted, for security? Or the data compressed, to improve data transfer speed? Or, more importantly, you want the MySQL server to be "locked down" limiting the range of systems that can access the MySQL server? Enabling the simple access to a remote MySQL server means configuring it to listen for connections from the entire world. Doing so is an obvious security risk.

An example of the default MySQL remote access using the command line tools is done as follows.

mysqldump --host=example.com --user=dbuser -p dbname

However these data transfers are done unencrypted and uncompressed. Perhaps you are security conscious and want your data to be encrypted, or to strictly control which computers can access your database. Perhaps it bothers you to run a database over the Internet, and compressing the data seems attractive.

Securing the connection between MySQL and MySQL Administrator using an SSH tunnel covers this using the Windows PUTTY client. SSH in general is a kind of swiss army knife of patching together data connections over the Internet. What the article does is lead you through using PUTTY to create a tunnel into your remote server.

An SSH Tunnel is a way to make a TCP port be created on your computer which, using SSH, exchanges data to another port on another computer. The SSH tunnel is a purely raw data exchange, so it can carry any protocol, and since it's SSH it can securely encrypt the data or compress it.

The Open SSH documentation also contains documentation on doing this using the command line SSH tool. This tool is included in many operating systems such as Linux, Solaris and Mac OS X. But it's not included with Windows.

Here's how you set up the SSH tunnel to connect to a MySQL server.

 

ssh -f -L 1234:localhost:3306 example.com sleep 60

 

The -f option puts the SSH process into the background, while the sleep 60 makes the SSH process simply exit if no connection is made within 60 seconds. The MySQL server is on example.com and at the remote end of the SSH tunnel a connection is made to localhost port 3306. This port is the default one for MySQL to run on, and this makes the remote end of the SSH tunnel connect to a MySQL instance on example.com. The local end of the connection appears on port 1234.

Then to connect to the server use this command:

mysql --port=1234 --user=dbuser --host=127.0.0.1

If you want to compress the data add a -C option to the SSH command line. MySQL also has a similar -C option.

«««««« Examiner.com writers can save their work using this screen-scraper script written for the purpose Making money through Facebook pages, Facebook advertising, and promoting content through Facebook »»»»»»