SOLVED: Error connecting to MySQL: Access denied for user 'nobody'@'localhost' (using password: YES)

[feerdispzoo]

Hello,
I've use CloudLinux, on this I install Mariadb10.5 but my app working with Mariadb104. i see is not possible downgrade, so I delete and then install again correct mariadb104

After this not possible open mysql in dir admin:
Error connecting to MySQL: Access denied for user 'nobody'@'localhost' (using password: YES)

I fallow to:

Troubleshooting | Directadmin Docs

DirectAdmin Knowledge Base

docs.directadmin.com

When I type:
mysql -uda_admin -p

Then ask me for password, but when I click enter without pass then login correct

Whe I tpe:
mysql -uroot -p
Then ask me for password, but when I click enter without pass then login correct



So:
I set first password for root:
mysql mysql -e "UPDATE mysql.user SET authentication_string=PASSWORD('rootpass') WHERE user='root'; FLUSH PRIVILEGES;"

Now when I type:
mysql -uroot -p

and when I enter password working.


ISSUE:
I try in the same way set also password for user da_admin:
ALTER USER 'da_admin'@'localhost' IDENTIFIED BY 'daadminpass';

When I try this then I get issue:
[root@vim735ac ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.4.25-MariaDB-cll-lve MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> ALTER USER 'da_admin'@'localhost' IDENTIFIED BY 'XXXXXXXX';
ERROR 1396 (HY000): Operation ALTER USER failed for 'da_admin'@'localhost'
MariaDB [(none)]> exit
Bye
[root@vim735ac ~]#

 

[Richard G]

MariaDB [(none)]> ALTER USER 'da_admin'@'localhost' IDENTIFIED BY 'XXXXXXXX';

Odd, that should have worked.

Try if this works.

UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'da_admin' AND Host = 'localhost';

 

[feerdispzoo]

Hello Richard,
please see:

1)

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.25-MariaDB-cll-lve MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> ALTER USER 'da_admin'@'localhost' IDENTIFIED BY 'xxxxxxxxx';
ERROR 1396 (HY000): Operation ALTER USER failed for 'da_admin'@'localhost'
MariaDB [(none)]>

2)

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.4.25-MariaDB-cll-lve MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> UPDATE mysql.user SET authentication_string = PASSWORD('xxxxxxxx') WHERE User = 'da_admin' AND Host = 'localhost';
ERROR 1348 (HY000): Column 'authentication_string' is not updatable
MariaDB [(none)]>

and this is strange in DirectAdmin:

Error connecting to MySQL: Access denied for user 'nobody'@'localhost' (using password: YES)

Display "nobody". It not should be root@localhost?

 

[feerdispzoo]

@Richard G,
please see also I try grant privileges and return this:

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO da_admin@localhost WITH GRANT OPTION;
ERROR 1133 (28000): Can't find any matching row in the user table
MariaDB [(none)]>

 

[feerdispzoo]

@update
I do this:

CREATE USER 'da_admin'@'localhost' IDENTIFIED BY 'xxxxx';

then

GRANT ALL PRIVILEGES ON *.* TO da_admin@localhost WITH GRANT OPTION;
FLUSH PRIVILEGES;
quit

Now I check password stored correct in:
/usr/local/directadmin/conf/mysql.conf

I can login correct with password mysql -u root - p and mysql -u da_admin -p

I also restart mariadb, start mariadb, and check status. All is okay.

But still DirectAdmin display this excatly error:

Error connecting to MySQL: Access denied for user 'nobody'@'localhost' (using password: YES)

Im not sure from where came this user "nobody". Im not sure but I think I need to assign again root user to directadmin ?


@update
resolved. After this I found missing "u" in user:
/usr/local/directadmin/conf/mysql.conf