9

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.

aduljr
  • 500
  • 2
  • 5
  • 16

2 Answers2

17

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.

Community
  • 1
Zoredache
  • 130,897
  • 41
  • 276
  • 420
  • 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 09 '15 at 15:43
  • @Zoredache, **How** "anyone who can run ps could find the password for your server"? – Pacerier Jan 15 '15 at 09:37
  • @Damon, And what if your password have these ticks? – Pacerier Jan 15 '15 at 09:38
  • @Pacerier you can escape it with a whack, so `'yourpwd\'withatick'`. *See [this answer](http://stackoverflow.com/a/881208/734790) for more character escapes.* You can test it by using plain mysql to connect, such as `mysql --defaults-extra-file=db.cnf` but you'll need db.cnf to be mode 700 *(rwx --- ---)* or stronger on Linux, or 400 *(r-- --- ---)* if stored on NTFS but ran from Linux, otherwise you'll get an error that it's "World-writeable". *NTFS on Windows may need restricted ownership as well, I'm not sure.* – Damon Jan 15 '15 at 19:14
  • @Pacerier So if you pass the password via the command line `mysql ... --password hunter2` and someone else on the system can run `ps axfww` then they will see the password since that ps command will print the full command line. – Zoredache Jan 15 '15 at 19:20
  • @Zoredache, I mean when you type `--password` only, and it would *prompt* you for the password. Then you type the password in. In this way how could `ps axfww` work? – Pacerier Jan 16 '15 at 07:07
  • @Pacerier the context of this question is about supplying **backup script** with the password via the command line arguments. So I am/was ignoring the interactive options. If you are interactively running the client then the above doesn't apply to you. – Zoredache Jan 16 '15 at 07:41
4

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.

Insyte
  • 9,394
  • 3
  • 28
  • 45
  • 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