MySql: ERROR 1045 (28000): Access denied for user 'root'@'localhost' although password is okay

Question

I run Debian 11 with mariadb and everything worked fine. Just yesterday I saw that different commands, such as "/etc/init.d/mariadb reload" or "mysql -u root" bring the error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

But I can log in with "mysql -u root -p xxx".

The password seems to be okay:

SELECT User, Host, authentication_string,plugin FROM mysql.user WHERE User='root';

brings this output:

| root | localhost | *A1500BDAC90A8E0183B2803086BD6D6E0999EC60 | mysql_native_password | | root | ::1 | *A1500BDAC90A8E0183B2803086BD6D6E0999EC60 | mysql_native_password | | root | myserver | *A1500BDAC90A8E0183B2803086BD6D6E0999EC60 | mysql_native_password | | root | 127.0.0.1 | *A1500BDAC90A8E0183B2803086BD6D6E0999EC60 | mysql_native_password |

What I don't understand: with the plugin "mysql_native_password" and the set password I should be able to login with "mysql -u root", for instance. bind-address is set to 0.0.0.0.

SHOW GRANTS FOR 'root'@'localhost';

brings these 3 rows:

Row 1: GRANT ALL PRIVILEGES ON . TO root@localhost IDENTIFIED BY PASSWORD '*A1500BDAC90A8E0183B2803086BD6D6E0999EC60' WITH GRANT OPTION

Row 2: GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
Row 3: GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION

MySQL (mariadb) runs on the server, all the websites can access it without any errors. Just debian can't access. Any ideas?

Edit :

I think I know the problem: The upper mentioned privilegs for root@localhost look like a syntax of an old mysql version. I use mariadb von Debian 11. I guess, I mistakly overtook the export of the privileges of root 1 by 1 from the old system to the new one.

The question is just: how should it look right? I looked on another server with Debian 11 now where it runs. There the privileges are like that:

GRANT ALL PRIVILEGES ON . TO root@localhost IDENTIFIED VIA mysql_native_password USING '*84A00BCFE90A8E0183B2803086BD6D6E0999EC12' OR unix_socket WITH GRANT OPTION

Can I try that without risking locking me out totally? Again: the webserver with all the mysql databases runs fine. Should I try to run that line, just with the correct password hash, of course? Could anyone confirm, please. Thank you!

Answer 1

When you try to use no password (e.g. no -p) no password is sent, and it doesn't matter what password hashing scheme is used in MariaDB.

What's used on modern system to authenticate users socket authentication. This looks up the uid (and username) of the unix user connecting to the socket, and if it matches the transmitted MariaDB username it allows login.

This can be configured using the ... IDENTIFIED VIA unix_socket ... or even (with >11.6) to authenticate a different MariaDB and Unix-user via the ... IDENTIFIED VIA unix_socket 'bob' ... where bob is the OS user name.

Answer 2

I got it solved!

As no one confirmed that it would work as mentioned in my "Edit", I risked it now and gave it a try. By running the mysql command:

GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING '*84A00BCFE90A8E0183B2803086BD6D6E0999EC12' OR unix_socket WITH GRANT OPTION 

... I got it to run again. The debian root has passwordless access to mysql root now again.

I hope that helps anyone out there who faces the same problem. Just get sure to replace the hash '*84A00BCFE90A8E0183B2803086BD6D6E0999EC12' by your own one, depending on your password!