3

I want to create a script to run the following BASH command:

mysqldump -u [username] -p [db_name] > [path to backup file]

Which results in a backup file. When running this in BASH, it prompts for a password before continuing.

How do I craft this in a BASH script so that the password is automatically entered?

Can this be done securely?

13

The best kind of approach here is to do something like:

mysqldump --defaults-extra-file=/path/to/auth.cnf ...

Where auth.cnf looks like:

[client]
user=the-user
password=the-password

Then make sure the file is only readable by whomever is meant to run that script. The script itself can be world readable.

3

Leaving a plain text password in any file is always a bad idea in case your system is ever compromised. Sometimes it is unavoidable. To make this "secure" you should limit this activity to a very limited user and also leave these sensitive options in a defaults file.

To solve your issue specifically, from the mysql man page:

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, 
mysql prompts for one.

in order to fix this you would need to run:

mysqldump -u [username] -p[password] [db_name] > [path to backup file]

or

mysqldump -u [username] -password=[password] [db_name] > [path to backup file]
  • 5
    Having the password on the mysqldump command line means it will show up in the output of ps. – Stéphane Chazelas Aug 3 '15 at 21:20
  • And the command history (assuming no/default HISTIGNORE) and audit.log (if configured to log executed commands). – Alaa Ali Aug 3 '15 at 22:50
  • @AlaaAli, yes, though if in the case of a script, that won't go in a shell history file. – Stéphane Chazelas Aug 4 '15 at 8:20
  • 2
    This is a correct answer but a bad idea. – dr01 Aug 4 '15 at 11:35
0

If the options are the same as I remember them you should be able to do the following:

mysqldump -opt [db_name] -u [username] -p[password] > [path to backup file]

Don't put a space between the -p and the actual password.

  • The original question also asked on how to do it securely. If you pass it on the command line, the password will show up in ps output as mentioned in some of the other comments. – tangrs Aug 4 '15 at 10:23
0

This is really just a supplementary answer to the others. I'd add it as a comment if I was allowed.

It's also not a very good practice because it puts the password in plaintext which you should never do if you can avoid it. A mysql config file is at least more easily protected than a script.

The answer that I'm avoiding is: Expect scripting. Using Expect script, your bash script runs within Expect which waits for (Expected) input matching it's rule and then responds with the (desired) output.

It's really just Expecting 'password:' and then responding with "myplaintextpassword\r". It was designed to allow scripts on one server to perform interactive ssh sessions on a different server.

Here's a question that covers the topic: Expect Scripting.

Expect is easily installed on any Linux distribution.

0

Stephane's answer is the best way how to do it for mysql case. And in most cases using the program's "own" way is the best.

However, to answer the Bash part of your question:

mysqldump -u joe -p <<<"joe's pasword" > /path/to/backup/file

or (less insecure)

mysqldump -u joe -p </path/to/file/with/joes/password > /path/to/backup/file

This approach will simply pass the string to the mysqldump process. It will work, provided that:

  • the password is only thing it asks,

    ie. there's no other prompt (most commands are quite predictable);

  • it will accept password from STDIN even if it's not interactive,

    ie. the program can deliberately verify if the file on input is an interactive terminal and abort.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.