1

I am trying to execute a shell script to login to mysql as root and execute some commands, and in order to avoid putting the root's password in the command line, I am using heredoc format as shown below. However, mysql prompts me for the password despite the fact that I am giving it the right password.

Exactly the same syntax works perfect on some other hosts but not here.

Why?

mysql@myhost:MySQL> mysql -uroot -p -s -P3306 -e 'SELECT NOW();'  <<EOF
> MyPassword
> EOF
Enter password:    <---- why does it require manual password entry in here?
2014-12-23 14:57:25
RGO
  • 4,586
  • 3
  • 26
  • 40
  • 1
    MySQL always displays the prompt before reading the password. It doesn't care whether the input is coming from a terminal or a here-doc. – Barmar Dec 23 '14 at 04:36
  • @Barmar Yes, that is correct. I think I need to clarify the problem as this: in other hosts, mysql prompts with "Enter password:" but it doesn't stop; it just accepts the already givenpassword. However, in this case, it stops there and waits for manual password entry. – RGO Dec 23 '14 at 05:26
  • You're right, I see that on my Linux system. It must be reading the password from `/dev/tty` rather than `stdin`. – Barmar Dec 23 '14 at 05:33
  • Have you considered using a `.my.cnf` file instead of putting the password in the script? – Barmar Dec 23 '14 at 05:34
  • @Barmar No! but I am trying to avoid it as I am not supposed to do any changes on the host. Btw... I have tried to make the example more clear. Thanks for your useful comment. – RGO Dec 23 '14 at 05:37
  • Remove the space between -p and the password, it's a mysql parsing thing – Wadih M. Sep 09 '19 at 18:09

1 Answers1

0

Assuming you use a BASH or KSH command line interface:

read -s pwd;mysql -uroot --password=${pwd} -s -P3306 -e 'SELECT NOW();'

For the read command, you should use the -s option because, as it happens with using heredoc, this solution would also display the password as it is being typed, if you wouldn't use said option.

This solution works because the mysql client obfuscates the password so it does not show in the output from process-listing commands, like ps. Run this:

read -s pwd;mysql -uroot --password=${pwd} -s -P3306

Then, if you open an additional command line interface and execute ps, you will see that it does not show the password, but and obfuscated version of it, in the process list.

This was a famous bug of the mysql client, back in the day, but was fixed in 2002.

Using read -s to put the password in a variable has the added benefit of not showing the password in the command-line interface's history.

Carlos Man
  • 348
  • 3
  • 10
  • 1
    He said he doesn't want to put the password on the command line. Presumably because it shows up in `ps` output. – Barmar Dec 23 '14 at 05:31
  • That is absolutely NOT TRUE! The "mysql" client specifically obfuscates the password text provided in the command line, so it shows like this in the output from "ps": mysql -uroot --password=x xxxxxxxxxxxxxx -s. I don't deserve that -1. Please, whoever gave me that -1 (I am new at this), check your facts. Do a "ps" on a separate console and see for yourself. Additionally, using this method, the password does not show in the output from the "history" command. – Carlos Man Dec 23 '14 at 15:49
  • You need to edit the answer so SO will let me remove my downvote. – Barmar Dec 23 '14 at 16:26
  • Edited the answer as requested. Thanks in advance. – Carlos Man Dec 23 '14 at 16:41