web://❤ A web related blog by Christian Engel

Access denied for 'root'@'localhost'

I had the case where I tried to connect to a remote MySQL server through an SSH tunnel with dbeaver.

The tunnel itself worked fine - I was able to create a connection but the server refused connecting and identifying as root user to the database. This happened on an old DigitalOcean Droplet with MySQL 5 installed where I was running a bunch of legacy websites.

I pulled my hair and tried a couple of approaches around the localhost part - because MySQL can be restrictive here - localhost != 127.0.0.1. But it never worked out.

When I created an SSH connection to the server, running the command mysql -uroot worked just fine - I was connected as root user immediately. It didn't even ask for a password. I assumed the authentication happened through an SSH key but I was wrong. The following command shined some light on the issue:

SELECT user, authentication_string, plugin, host FROM mysql.user;

It spits out a list of existing users of your MySQL server, what plugin the user uses for authentication and what hosts are allowed to connect.

```bash +