I wrote a simple shell script to dump a specific mysql database. problem is it is prompting me for a password even though I provide on. The mysql db is version 5.0.27 if that matters. Here is the specific line I am using.

$MYSQLDUMP -u backup -p$MyPass $DB > $DEST/$FILE

I tried several variations, but to no avail.


I would wager that $MyPass is not being set properly in your script. If any of the mysql command-line tools receive a -p that is not immediately followed by a password, they assume they should prompt for the password. If, on the other hand, you supplied an incorrect password (or the wrong username or similar) it would just fail to connect.

So, I suggest placing an echo command at the start of that line and re-running your script, like so:

echo $MYSQLDUMP -u backup -p$MyPass $DB

Don't forget to remove the output redirection.

| improve this answer | |
  • 1
    doh, it was the stupid variable, had pass in caps when it was set vs lowercase in the above line. thanks guys – aduljr Aug 19 '09 at 20:51

A better solution would be to store the username and password in a configuration file and point to that configuration file. By having it in the command line anyone who can run ps could find the password for your server.

Pass the --defaults-extra-file=/pathto/database.cnf to mysql dump.

The configuration file needs to look like this. Set the filesystem permissions so that only the backup process can open the configuration file.

host     = servername.domain.tld
user     = backup
password = strongpassword

Update (2016-06-29) If you are running mysql 5.6.6 or greater, you should look at the mysql_config_editor tool that allows you to store credentials in an encrypted file. Thanks to Giovanni for mentioning this to me.

| improve this answer | |
  • its a specific user for that database with only enough permission to perform a backup. Only two people have access to this server, my and one other person. But I will make use that that cnf file idea. never knew about that option. thanks – aduljr Aug 19 '09 at 20:49
  • 3
    I know this is an old answer, but still shows up when searching how to securely pass the password and it's linked from other posts too. Using version 5.5, I had to encapsulate the strongpassword in single ticks in the config file, such as if my password is aoeu1234, my config file has password = 'aoeu1234'; otherwise you'll get an unauthorized error. – Damon Jul 23 '14 at 21:19
  • Wondering if there's a solution that does not include a password in cleartext in a .cnf file. – Bob Stein Jan 9 '15 at 15:43
  • @Zoredache, How "anyone who can run ps could find the password for your server"? – Pacerier Jan 15 '15 at 9:37
  • @Damon, And what if your password have these ticks? – Pacerier Jan 15 '15 at 9:38

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.