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

Did you try --password=whatever-password-is ?

Perhaps I'm missing the question, but that is what I do to run the tool.

Answer 2

I am not sure if it works for the --password version, but if you use -p you can specify the password immediately afterwards (the key is not to include a space):

mysqldump -pmypassword ...

Answer 3

The -p option does not require an argument. You just put -p or --password to indicate that you're going to use a password to access the database. The reason it's dumping the database named whatever you put after -p is that the last argument for mysqldump should be the name of the database you want to dump (or --all-databases if you want them all).

@Nathan's answer is also true. You can specify the password immediately following the -p switch (useful in scripts and such where you can't enter it by hand after executing the command).

Answer 4

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 5

Try placing a '=' in between --password lose like:

--password=lose

If you use -p, then there can be no space between the -p and the password, i.e. '-plose'.

Answer 6

--password[=password]

Here is the documentation

Answer 7

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 8

If you use the -p or --password without an argument, you will get a prompt, asking to insert a password.

If you want to indicate a password on the command line, you must use -pYOURPASSWORD or --password=YOURPASSWORD. Notice that there is no space after -p, and there is an "=" sign after --password.

In your example, mysqldump asks for a password, and then treats "lose" as the database name. If that was your password, you should have included a "="

Answer 9

Maybe your user "test" doesn't have the permission to access your "lose" database?

Answer 10

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.