CODEWITHSUNDEEP
mysql
spraff
spraff

Reputation: 33425

Why can't I pipe a password into mysql non-interactively?

I want to run mysql client, giving the password non-interactively.

The standard solution is this

mysql -u root -e "foo" -p < password_file

but my situation is this

produce_password | mysql -u root -p

Here, mysql prompts for a password, even though data is being piped in. (Yes, produce_password is emitting data; echo foo | mysql behaves the same way.)

The Internet seems to think the above should work, but the fact is it doesn't. The workaround would be

produce_password > password_file
mysql -u root -p < password_file
rm password_file

But let's say I don't want to do this (e.g. policy demands that this password never be written to the disk)

How can I make mysql take the password from the input process without prompting, as it would for a file?

Upvotes: 3

Views: 3719

Answers (4)

MagMax
MagMax

Reputation: 2709

The easiest way is to declare the environment variable MYSQL_PWD.

Example:

$ export MYSQL_PWD=$(produce the password if required)
$ mysql -h example.org -u username

Remember you should not use -p in this case.

Upvotes: 0

manoflinux
manoflinux

Reputation: 715

the problem with that is the password shows up in the process list. But you can do this.

mysql --defaults-file=<(echo '[client]'; echo 'user=USERNAME'; echo "password=$mysqlpassword";)

It shows up in the process list like this.

mysql --defaults-file=/dev/fd/63

so the <() creates a file handle to the output of your commands. This works with command line options that are expecting a file.

Upvotes: 1

spraff
spraff

Reputation: 33425

With thanks to fancyPants for explaining the cause, here is a solution which meets my requirements. (The encrypted .mylogin.cnf with mysql_config_editor isn't right for me, but thanks.)

To satisfy the security policy, mount /ramfs as a ramfs temporary file system. Assume file permissions are suitably restrictive.

ramdir="/ramfs"
cnf="$(mktemp "$ramdir/this-script-name-XXXXX")"
pw="$(produce_password)"

cat >"$cnf" <<EOF
[client]
user=root
password="$pw"
EOF

mysql --defaults-extra-file="$cnf" -e 'select 1'

rm "$cnf"

Upvotes: 2

fancyPants
fancyPants

Reputation: 51908

I don't know how to explain this, but when you pipe something, the stdout of the first program is forwarded to the stdin of the second program. You somehow confuse this, with the command line or whatever. You can pipe something to mysql, of course, but whatever you pipe is handled after you've authenticated yourself.

The solution would be 

mysql -u root -p$(produce_password)

This generates your password with whatever program you have there and puts it in the right place on your commandline (my english is bad, can't explain better). When you have it in a file you could do

mysql -u root -p$(cat file)

I don't know, why you want to do it this way anyway, but you might be interested in having an encrypted file with your credentials, that you can use to log in without specifying a password. Read more about it here.

Upvotes: 2

Related Questions