MySQL passwordless access via SSH tunnel - Access denied for user 'root'@'localhost'

Question

I have a new MySQL 8 server installed. Fresh, clean, straight from the package. Now I want to connect to it with DBeaver (a GUI tool). It has a secure feature as SSH tunnel. The tunnel works. I connect over SSH using my private key.

Then I try to connect as root to localhost with DBeaver (over the tunnel), and that gives me "Access denied for user 'root'@'localhost'" error message.

Why is that? When I SSH to the server with putty as root, then type mysql - it just works. I have full access.

BTW, I tried to use "127.0.0.1" instead of "localhost" in DBeaver configuration, but it didn't help.

Here's my mysql.user table:

mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | debian-sys-maint |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+

Any idea how to configure the access? Or maybe I must set a password? But why does it work in terminal?

Answer 1

You cannot use root from outside the server. This is intentional on the part of MySQL because people would (far too often) put up a website with a MySQL backend and bug-riddled front-end, use a simple password, and get pWn3d … often in more ways than one.

A quick way forward is to create another account for all your admin work, then grant all privileges with the grant option. The process would look like this:

CREATE USER 'harry'@'%' IDENTIFIED WITH mysql_native_password BY 'superSecretPassword!123';
GRANT ALL ON *.* TO 'harry'@'%' WITH GRANT OPTION;

Problem solved 👍🏻