You are here

Using SSH or Putty to remotely access a MySQL server

The MySQL clients allow you to access a remote MySQL server pretty easily. For example, a 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. 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.

Short URL