Does mysqldump --password really do what it says?

Question

I'm trying to use mysqldump to dump a schema, and it mostly works but I ran into one curiosity: the -p or --password option seems like it is doing something other than setting the password (as the man page and --help output say it should).

Specifically, it looks like it's doing what is indicated here: http://snippets.dzone.com/posts/show/360 - that is, setting the database to dump.

To support my somewhat outlandish claim, I can tell you that if I do not specify the --password (or -p) option, the command prints the usage statement and exits with an error. If I do specify it, I am immediately prompted to enter a password (!), and then the database specified in the --password option is dumped (or an error is given in the usual case that a password not matching any database name was specified).

Here's a transcript:

    $ mysqldump -u test -h myhost --no-data --tables --password lose
    Enter password: 
    -- MySQL dump 10.10
    mysqldump: Got error: 1044: Access denied for user 'test'@'%' to
    database 'lose' when selecting the database

So, what gives? Is this the way this is supposed to work? It surely does not appear to make sense nor does it match the official documentation. And finally, if this just the way it works, how am I meant to specify the password to be used in an automated job? Using expect???

I'm using mysqldump Ver 10.10 Distrib 5.0.22, for pc-linux-gnu (i486).

Answer 1

From man mysqldump:

--password[=password], -p[password]

The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, you are prompted for one.
Specifying a password on the command line should be considered insecure. See Section 6.6, "Keeping Your Password Secure".

Syntactically, you are not using the --password switch correctly. As such, the command line parser is seeing your use of "lose" as a stand-alone argument which mysqldump interprets as the database name as it would if you were to attempt a simpler command like mysqldump lose

To correct this, try using --password=lose or -plose or simply use -p or --password and type the password when prompted.

Answer 2

Another option is to create the file ~/.my.cnf (permissions need to be 600).

Add this to the .my.cnf file

[client]
password=lose

This lets you connect as a MySQL user who requires a password without having to actually enter the password. You don't even need the -p or --password.

Very handy for scripting mysql & mysqldump commands.

Answer 3

I found that this happens if your password has special characters in it. The mysql password here has a ! in it, so I have to do ==password='xxx!xxxx' for it to work corrrectly. Note the ' marks.