MySQL over Secure SSH Tunnel Notes

This article contains instructions and troubleshooting hints for connecting to MySQL over an SSH tunnel. Some recommended software is suggested for securing your MySQL connections. If you receive an Access denied for user or Can't connect to MySQL server on 'localhost' error when connecting via your tunnel, some tips are provided for how to solve the errors.

If you receive a MySQL error "Access denied for user" when connecting over an SSH tunnel, then try setting the server hostname to '127.0.0.1' instead of 'localhost'. When connecting over an SSH tunnel, the MySQL client does not correctly handle 'localhost'.

Setting up an AutoSSH Tunnel for MySQL on Linux/FreeBSD

AutoSSH will prevent the tunnel from dropping due to network failures, etc. This is useful for a secure replication master-slave setup between two servers, where setting up SSL is too cumbersome. This assumes you have set up public-private key authentication for the SSH command already.

# Add the following lines to contab
# When server is rebooted, start up the SSH tunnel
@reboot /usr/bin/autossh -M 5122 -N -L 3307:localhost:3306 username@hostname.com &
# Explanation of autossh/ssh options:
# -N - Do not execute a remote command
# -L - The tunnel will forward port 3307 on the local server to port 3306
#    on the remote server.  
# -M 5122 - Port used by autossh to check connectivity.

Use the command below to test the MySQL connection over the SSH tunnel.

mysql --host=127.0.0.1 --port=3307

MySQL command to connect via SSH tunnel

The commands below assume you have added the appropriate user@host entry to the mysql.user table in the destination server. This command will succeed because hostname is set to 127.0.0.1.

mysql -h127.0.0.1 -P 3307 -uUSERNAME -pPASSWORD
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19315
Server version: 5.1.41-3ubuntu12.6-log (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

This command will fail because hostname is set to localhost.

mysql -hlocalhost -P 3307 -uUSERNAME -pPASSWORD
ERROR 1045 (28000): Access denied for user 'USERNAME'@'localhost' (using password: YES)

Mysql over SSH tunnel on Windows

If you want to connect securely to MySQL from a Windows XP/7/2003/2008 machine, you have several options.

HeidiSQL SSH Tunnel - Best Option

HeidiSQL is a free, full-featured MySQL GUI application for Windows. It lets you view and edit data, tables, and other database objects. Its main advantage is that it is lightweight and has very fast startup.

HeidiSQL has a TCP/IP over tunnel option when setting up a database session. It uses the freeware plink.exe (part of putty) SSH utility to set up a an SSH tunnel that runs transparently in the background.

MySQL Workbench SSH Tunnel

MySQL Workbench is the official GUI application for MySQL on Windows. In addition to database design and SQL development, it allows you to administer MySQL servers. The disadvantage for day-to-day use is that it is a large, heavy application with a high start-up time. It is also missing some convenient import/export features that HeidiSQL has.

MySQL Workbench also allows you to set up secure MySQL connections over an SSH tunnel. You just define the tunnel parameters when you define the session, and the SSH tunnel is created in the background.

MySQL SSH Tunnel with Bitvise Tunnelier

For non-interactive applications where you need to connect to MySQL securely, the above options can be used, but you have to manually start the MySQL session and lookup the SSH port for the tunnel. Instead, you can simply use the freeware Bitvise Tunnelier Windows SSH tunnel software.

This application lets you configure a persistent SSH tunnel that runs in the background from a tray icon. The major advantages of this option is that the tunnel runs in the background on a pre-defined port, it reconnects itself on network failures, and it can be configured to run on startup.

HeidiSQL - SQL Error (2003): Can't connect to MySQL server on 'localhost' (10061)

You may receive the HeidiSQL error, SQL Error (2003): Can't connect to MySQL server on 'localhost' (10061) when connecting to a MySQL server over an SSH tunnel. The solution to this error is to run Putty.exe manually and connect one-time to the SSH host server.

By default, Putty (and plink) do not trust servers, and they require you to manually accept and save the server fingerprint before allowing the connection to go through. Until that fingerprint is manually accepted, you will get the unintuitive SQL Error (2003): Can't connect to MySQL server on 'localhost' (10061) error when connecting to MySQL with HeidiSQL.

1. Run putty.exe
2. Connect to your SSH host. You only need to provide the host name (or IP address) and click Open in the Putty session dialog. You do not need to provide user name, password, security key, or any other settings.
3. You will see a dialog box with the title PuTTY Security Alert. The message will read as follows:

   The server's host key is not cached in the registry. You have no guarantee that the server is the computer you think it is. The server's rsa2 key fingerprint is: ssh-rsa 2048 00:00:00:00:00:00:00:00:00:00:00:00:00:00:00:00 If you trust this host, hit Yes to add the key to PuTTY's cache and carry on connecting. If you want to carry on connecting just once, without adding the key to the cache, hit No. If you do not trust this host, hit Cancel to abandon the connection.

4. Click the Yes button.
5. Putty will save the server's key fingerprint to the following registry location: HKEY_CURRENT_USER\Software\SimonTatham\PuTTY
6. Now you may see a prompt to login or other message. Just exit PuTTY at this point.
7. You only need to do this one time.
8. Open the HeidiSQL session again, and the SQL Error (2003): Can't connect to MySQL server on 'localhost' (10061) error should go away.