Solved Access denied for user 'root'@'localhost, but ...

[MisterM]

Hello

I am on a case, that I have never seen, surely all the team of DA would answer me.

I am no longer able to make a backup, the task goes into error when it tries to connect with the command "mysqldump".

Error while backing up database xxxx_xxxx
Error while backing up database xxxx_xxxx The sql file is 0 bytes in size:
/home/tmp/......
mysqldump error output: mysqldump: Got error: 1045: "Access denied for user 'root'@'localhost' (using password: NO)" when trying to connect

But on the other hand, I know how to connect as root and the password used is correct from the setup.txt file of the installation...

Mz

 

[MisterM]

Well then, I have relaunched a build all is at the time of the update of DA, it to stop Mysql with a lot of time, but correct, finally I think, is relaunching by restarting normally, I will see tomorrow if I receive or not a message of error of safeguard or not of my sites.

Mz

 

[MisterM]

Hello,

I thought that the build that I made yesterday would have worked, hey no, always the same problem is always the error message and the worst is that I know how to connect in root with this command if:

mysql -uroot -p

By adding the password that I find in the setup.txt file.
What should I do, any idea?
Mz

 

[Ohm J]

you should get user and password from "/usr/local/directadmin/conf/mysql.conf" AND

"/usr/local/directadmin/conf/my.cnf" username and password should match inside "mysql.conf"


also you can use this script to reset root/da_admin password

Version 1.61.4 | Directadmin Docs

DirectAdmin Knowledge Base

www.directadmin.com

 

[MisterM]

New error when I restart the backup :

mysqldump error output: /usr/local/mysql/bin/mysqldump: unknown variable 'passwd=password da_admin'

Mz

 

[Richard G]

unknown variable 'passwd=password da_admin'

Seems something went wrong on filling in the password or the script did something wrong.

 

[MisterM]

nothing at all @Richard G, when I reset is test with the following command:

mysql -uda_admin -p

I can connect with the user ...

Mz

 

[Richard G]

Strange. It gets that variable from somewhere.
Did you replace the new password in the files @jamgames2 mentioned?

 

[Ohm J]

mysql.conf and my.cnf not same template, did you just copy file and place ?

I only told you, Username and Password to match with these files. not whole file.

 

[MisterM]

Question for the team for the people who follow me.

What configuration file works with mysqldump , mysql.conf or my.cnf ?

Mz

 

[Richard G]

Depends on what DA is calling.

Just to be sure, check the layout.
The /usr/local/directadmin/conf/my.cnf it should look like this:

[client]
user=da_admin
password="hereyourpassword"

The mysql.conf in there should just look like this:

user=da_admin
passwd=hereyourpassword

Be sure both passwords are the same, and working with da_admin if you test via commandline.

 

[MisterM]

okay, that's why it puts this error "unknown variable", I test and I come back to you ...

 

[MisterM]

always the same mistake:

mysqldump -u user -pPassword db > base-test.sql
mysqldump: unknown variable 'passwd=password_da_admin'

Mz

PS :

The mysql logs, where is it ? /var/log/mysql ?

 

[Richard G]

Try using p- without the password behind it. And then you have the password right after using the enter.

Double check if you have the [client] in the my.cnf and -not- in the mysql.conf file.
Also mind the apostrophs present in the my.cnf but absent in the mysql.conf file.

I also always use mysqldump like this or with only -p and always works:
mysqldump -uusername -ppassword databasename >/backup/dump.sql

 

[MisterM]

then
cat /usr/local/directadmin/conf/my.cnf

[client]
user=da_admin
passwd=XXXXXXXXXX

cat /usr/local/directadmin/conf/mysql.conf

user=da_admin
passwd=XXXXXXXXXX

mysqldump -uusername -ppassword database > base-test.sql
mysqldump: unknown variable 'passwd=XXXXXXXXXX'

Mz

 

[Richard G]

Change:

[client]
user=da_admin
passwd=XXXXXXXXXX

to

[client]
user=da_admin
passwd="XXXXXXXXXX"

so with the apostrophes like in my example, and try again.

If that does not work, search your system for maybe a mysqldump.sh or something else.
There should be only 1 mysqldump binarie and if you use console manual input, it should never respond with "unknown variable 'passwd=XXXXX" unless there is some mysqldump script somewhere or a second binarie or if you give in passwd=XXXX instead of only XXXX.

 

[MisterM]

Hello
The error came from the file that was in

/root/.my.cnf

I just deleted it, is this correct?
Now it displays this when I run the command, mysql

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

However, when I connect to this :

mysql -uroot -p

I have access to root using the password in the setup.txt file which is different than the password in the da_admin file in my.cnf.
Good news, I just restarted a backup of a site and it works without bug.

User XXXX has been backed up. <7:14:19>

The User backups have been created in /home/XXXXX/user_backups <7:14:19>

Mz

 

[Zhenyapan]

@MisterM
mysqldump -u username -p dbname > backupname.sql
then it asks password, put it an all will work

 

[MisterM]

@MisterM
mysqldump -u username -p dbname > backupname.sql
then it asks password, put it an all will work

@Zhenyapan It works now
I recreate a task concerning my backup and it works during 3 days I will close the subject.
Mz

 

[MisterM]

Hello

I'm looking to add that this is "solved"

Thanks to @Richard G , @Zhenyapan , @jamgames2

Mz