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.