§ Home > Index > Troubleshooting

MySQL Access denied for user over SSH Tunnel

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

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)