2

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.

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

Edit:

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

Don't I need any unix_socket in there? But how?

Edit 2:

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

The question is just: how should it look right?

I looked on another server with Debian 11 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 myself 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? Thank you!

Wahyu Kristianto
  • 8,719
  • 6
  • 43
  • 68
afriend
  • 421
  • 1
  • 4
  • 6
  • 2
    *ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: **NO**)* - this means that you do NOT provide the information that the password is needed. This is true - in your command line `mysql -u root` there is no `-p` or `--password=` key. So adjust your command line. – Akina Mar 21 '23 at 09:38
  • As mentioned, I can log in with the "-p" parameter. But the problem is that I can't reload mariadb anymore by "/etc/init.d/mariadb reload" (also other operations like that. I guess also restart/start would not work), as the login without "-p" doesn't work, brings the "access denied" error. I guess I need to allow any socket connection for root@localhost. But I have not idea how. Any ideas? – afriend Mar 21 '23 at 09:45
  • The password is NOT empty. So the option `-p` or `--password=` MUST present in either command line or (preferred) in the options file (my.ini). – Akina Mar 21 '23 at 10:02
  • on a fresh install of debian 11, I can login to mariadb just with "mysql -u root" without the "-p" parameter. this also worked on this server in the past. for any reason it doesn't work anymore. this is why e.g. "/etc/init.d/mariadb reload" and also other mysql operations, done by debian, don't work anymore. they bring the error "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)". As it is debian, there is no mysql.ini, but a myql.cnf inside /etc/mysql/. But the configurations there look fine. I guess it is a problem with the privilegs of root@localhost ? – afriend Mar 21 '23 at 10:14
  • I think I have to bring in "unix_socket" in any way, so that debian has access without password. just for having access at "/etc/init.d/mariadb reload" and other system side calls to mysql. but I don't know how? – afriend Mar 21 '23 at 10:19
  • `ALTER USER \`root\`@\`localhost\` IDENTIFIED VIA unix_socket` – danblack Mar 22 '23 at 04:52

1 Answers1

0

I got it solved!

As no one confirmed that it would work as mentioned in "Edit 2", 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!

afriend
  • 421
  • 1
  • 4
  • 6