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

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!

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!