Mysqldump password issue

[Fred Condo]

On Oct 2, 2008, at 12:00 PM, Matthew Seaman wrote:

> Andrei Brezan wrote:
>> Hello list,
>> I wanna do a
>> mysqldump -u user -ppasswd --all-databases > backup.sql
>> and all I get is
>> mysqldump: No match.
>> This happens either i put --all-databases or I specify any of the
>> databases. I want to do a backup as user root, that's why I use
>> all-databases opt.
>> If I use the command:
>> mysqldump -u root -p --all-databases >backup.sql
>> I get the password prompt, I type the passwd and everythig works  
>> great.
>> It seems that there is a problem with -p, i've tried --password with
>> same result.
>> If anyone has any ideea please let me know about it.
>> I mention that i use Freebsd 7_0 and mysql 5.0.67
>
> My guess is that the password (which you've obviously elided) contains
> characters of syntactic significance to the shell.  Any of the  
> following
> will lead to wailing and gnashing of teeth:
>
> * ? [ < > & ; ! | $
>
> Probably others as well.  The general way to get round this is to
> put 'quote' marks around your password -- but this will only work if
> the password is a separate word on the command line -- ie. whitespace
> between it and any other tokens.  I believe that the '-p' flag to  
> MySQL
> is a bit painful in that regard as it doesn't allow whitespace between
> itself and the password.  Hmmm... untested, but it should work if you
> just quote around the -p like so: '-ppassword'.
>
> Alternatively, just change the password to one containing less
> troublesome characters: a-zA-Z0-9:@#~+=-_^%., I recommend use of
> 'apg' to generate randomised but strangely memorable passwords.  Oh,
> and simply making the password longer makes it much more secure even
> if you're limited to a relatively small alphabet.

If consistent with your security policies, you can store the password  
in your ~/.my.cnf file:

[client]
user=db_user
password=funny&password