strange authentication trouble about mysqldump: error 1045

[21novembre@gmail.com]

Hi all,

I got a quite strange problem when I tried to setup a database backup
shell. I put it this way:
"bin/mysqldump --opt --user=xxx --password=xxx DB > DB.bak"
However, error 1045 came to me to say "Access denied for user
'xxx'@'localhos t' (using password: YES) when trying to connect".
None the less, I'm absolutely full of confidence on my correct username
and password, simply because if I do it this way:
"bin/mysqldump --opt --user=xxx DB > DB.bak",and input the same
password, everything goes well.
Could you please give me some suggestion concerning the issue? Any help
will be appreciated.

Zh.y

[Bill Karwin]

Re: strange authentication trouble about mysqldump: error 1045

21novembre@gmai l.com wrote:[color=blue]
> if I do it this way:
> "bin/mysqldump --opt --user=xxx DB > DB.bak",and input the same
> password, everything goes well.[/color]

I assume you mean "bin/mysqldump --opt --user=xxx -p DB > DB.bak" and
input the same password. (that is, using the -p option to ask for an
interactive prompt for password) Without using the -p flag, you're
asking to log into that database without using a password. MySQL
permits that mode of access to have different privileges than when you
provide a password for the same user name!

You can test access to a database given a username and password, using
the mysqlaccess command:

mysqlaccess --db=DB --user=xxx --password=yyy
--superuser=root --spassword=rootp assword

Also try the same thing without specifying a password:

mysqlaccess --db=DB --user=xxx
--superuser=root --spassword=rootp assword

Regards,
Bill K.

[21novembre@gmail.com]

Re: strange authentication trouble about mysqldump: error 1045

Hi Bill, I tried what you told me. I have the results posted here
because I don't understand if it's normal or not. I'm totally confused
about what's happening.
--------------------
[root@hostname mysql]# bin/mysqlaccess --db=DB --user=xxx
--password=yyy --superuser=root --spassword=xxxx;
mysqlaccess Version 2.06, 20 Dec 2000
By RUG-AIV, by Yves Carlier (Yves.Carlier@r ug.ac.be)
Changes by Steve Harvey (sgh@vex.net)
This software comes with ABSOLUTELY NO WARRANTY.
Broken pipe
[root@hostname mysql]# bin/mysqlaccess --db=DB --user=xxx
--superuser=root --spassword=xxxx;
mysqlaccess Version 2.06, 20 Dec 2000
By RUG-AIV, by Yves Carlier (Yves.Carlier@r ug.ac.be)
Changes by Steve Harvey (sgh@vex.net)
This software comes with ABSOLUTELY NO WARRANTY.
Broken pipe
--------------------------

Zh.Y

[Bill Karwin]

Re: strange authentication trouble about mysqldump: error 1045

21novembre@gmai l.com wrote:[color=blue]
> Hi Bill, I tried what you told me. I have the results posted here
> because I don't understand if it's normal or not. I'm totally confused
> about what's happening.[/color]

Okay, I've provided a reference below that explains this error. But
mysqlaccess is just a tool to demonstrate what privileges to that
database are configured, depending on whether you provide a password or
not. It's not strictly necessary to use this tool to solve your
original problem.

Your original posting asked why you have trouble with this command:
"bin/mysqldump --opt --user=xxx --password=xxx DB > DB.bak"
while this command works:
"bin/mysqldump --opt --user=xxx DB > DB.bak"

I assume you mean the second command includes a "-p" option, since you
said you were prompted for a password. If you include no option "-p",
mysql tools assume that the user you specified is connecting without a
password. This is supported by MySQL; a user can be granted privileges
on a database in the case when no password is specified. And no
password will be asked for by mysql tools if you don't give "-p" on the
command line.

Therefore, one possibility I think might explain your original problem
is that user xxx has privilege on database DB, but _only_ when
connecting without specifying a password.

Okay, a different possibility, if you meant that you are actually using
the "-p" option, and therefore are giving a password in response to a
prompt. Your password may contain shell metacharacters, such as !#'"`&;
or even whitespace. This could cause problems in the first command
example, even if the password works when you enter it interactively at a
password prompt.

The solution is that you may need to quote the password, and possibly
put backslashes in even if you quote it. For instance, if your password
is "I'm #1", this might work:

bin/mysqldump --opt --user=xxx --password='I\'m #1' DB > DB.bak

Discovering the correct quoting and escaping combinations to prevent the
shell from interpreting the special characters in a given string can be
complex. Perhaps the simplest solution is to use in your passwords no
characters that are special to the shell (if you need to specify the
password on the command-line, which should be infrequent).

You can also use other methods to specify the password on connect, like
the .my.cnf file, or the MYSQL_PWD environment variable.
See http://dev.mysql.com/doc/mysql/en/pa...-security.html
[color=blue]
> [root@hostname mysql]# bin/mysqlaccess --db=DB --user=xxx
> --password=yyy --superuser=root --spassword=xxxx;
> mysqlaccess Version 2.06, 20 Dec 2000
> By RUG-AIV, by Yves Carlier (Yves.Carlier@r ug.ac.be)
> Changes by Steve Harvey (sgh@vex.net)
> This software comes with ABSOLUTELY NO WARRANTY.
> Broken pipe[/color]

I found an explanation of this error in this page from the MySQL
documentation:

http://dev.mysql.com/doc/mysql/en/installing-binary.html

If you would like to use mysqlaccess and have the MySQL distribution in
some non-standard place, you must change the location where mysqlaccess
expects to find the mysql client. Edit the bin/mysqlaccess script at
approximately line 18. Search for a line that looks like this:

$MYSQL = '/usr/local/bin/mysql'; # path to mysql executable

Change the path to reflect the location where mysql actually is stored
on your system. If you do not do this, you get a Broken pipe error when
you run mysqlaccess.

Regards,
Bill K.

[21novembre@gmail.com]

Re: strange authentication trouble about mysqldump: error 1045

Hi Bill, you are absolutely a genius! Yes my password contain "!@#$%^".
I used to think this password will take me good luck. However I never
expect it brings me some trouble before the good luck.
Thank you very much! I got it.