John Zwinck John Zwinck - 2 years ago 70
SQL Question

Does mysqldump --password really do what it says?

I'm trying to use

to dump a schema, and it mostly works but I ran into one curiosity: the
option seems like it is doing something other than setting the password (as the
page and
output say it should).

Specifically, it looks like it's doing what is indicated here: - that is, setting the database to dump.

To support my somewhat outlandish claim, I can tell you that if I do not specify the
) 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
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

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download