mysqldump prompting for password in shellscript

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.

Solution 1:

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.

Solution 2:

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.

[client]
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.