98

I have MySQL password saved on a file foo.php, for example P455w0rd, when I try to use it:

$ cat foo.php | grep '$dbpwd=' | cut -d '"' -f 2 | mysql -U root -p mydb -h friendserver
Enter password: (holds)

$ echo P455w0rd | mysql -u root -p mydb -h friendserver
Enter password: (holds)

Both option still ask for password, what's the correct way to send password from stdin?

Improve this question
3
  • 6
    must be no blank between -p and your password.
    – Fırat Küçük
    Commented May 23, 2015 at 7:10
  • mysql doesn't read the password from stdin, I wasn't able to figure it what it does read it from.
    – Omn
    Commented Mar 19, 2018 at 0:20
  • 2
    The proper answer is don't put your passwords on the command line where anyone with access to /proc can trivially read them as long as the program is running. That's what a ~/.my.cnf is for, properly chmod'ed to 0600
    – Shadur-don't-feed-the-AI
    Commented Jun 16, 2019 at 21:29

6 Answers 6

Reset to default
104

The mysql client utility can take a password on the command line with either the -p or --password= options.

If you use -p, there must not be any blank space after the option letter:

$ mysql -pmypassword

I prefer the long options in scripts as they are self-documenting:

mysql --password=mypassword --user=me --host=etc
Improve this answer
5
  • 23
    This is insecure because any user could view the password - either directly via /proc/$pid/cmdline or via the ps command. It's true that mysql overwrites the password in argv during startup but there is always a time window where another user could observe the password. Also, on some systems the argv overwriting might not work.
    – maxschlepzig
    Commented Jul 14, 2019 at 14:49
  • Thanks for the hint that there must not be any blank space after the option letter. Saved my day.
    – Tobias Marschall
    Commented Oct 7, 2020 at 8:11
  • 11
    Of course this is insecure, but most people are doing something like this in a build process that will be deleted shortly afterwards...
    – Adam Fowler
    Commented Jun 29, 2021 at 22:41
  • 1
    List of running processes might be captured by machine monitoring tools and appear then in log files. A short running process is a bad excuse.
    – dolmen
    Commented Sep 23, 2022 at 8:55
  • 1
    Just be a 'real person' and enter the password when prompted rather than sharing it with anyone, including your mother.
    – Wilson Hauck
    Commented Mar 9 at 17:09
72

You have to be very careful how you pass passwords to command lines as, if you're not careful, you'll end up leaving it open to sniffing using tools such as ps.

The safest way to do this would be to create a new config file and pass it to mysql using either the --defaults-file= or --defaults-extra-file= command line option.

The difference between the two is that the latter is read in addition to the default config files whereas with the former, only the one file passed as the argument is used.

Your additional configuration file should contain something similar to:

[client]
user=foo
password=P@55w0rd

Make sure that you secure this file.

Then run:

mysql --defaults-extra-file=<path to the new config file> [all my other options]
Improve this answer
8
  • 2
    MySQL will modify its argv to overwrite the parameters given to the -p flag. At least that's what's concluded here, together with other relevant info: unix.stackexchange.com/questions/78757/…
    – Kusalananda ♦
    Commented Jan 23, 2017 at 13:40
  • 2
    This is the best solution, as it provides a measure of security. You need to chmod go-rwx, and make sure this argument preceeds all other arguments.
    – ChuckCottrill
    Commented Feb 1, 2017 at 0:52
  • 3
    @Kusalananda, yes, but as per comments on unix.stackexchange.com/q/385339/135943, that does not mean it's safe!
    – Wildcard
    Commented Oct 19, 2017 at 5:01
  • 28
    Set MYSQL_PWD in the environment (export MYSQL_PWD=muhpassword) and execute your command without the -p. See MySQL Program Environment Variables. In spite of the manual's dire warnings, this is rather safe. Unless you start weird warez in the same shell later. So we run: MYSQL_PWD=$(cat foo.php etc) mysql -u foouser -h barhost
    – David Tonhofer
    Commented Mar 1, 2018 at 18:02
  • 1
    The --defaults-extra-file must be the first option when executing the mentioned command. For me, it didn't work otherwise.
    – vinkomlacic
    Commented Jun 16, 2019 at 21:05
13

create a file ~/.my.cnf, make it only accessible by yourself, permission 600.

   [client]
   user=myuser
   password=mypassword

Then you don't need type password any more.

   bash$ mysql -u myuser mydatabase
   mysql>
Improve this answer
1
9

You can use a nifty Linux trick...

/dev/stdin can be used as a file resource for --defaults-file like so:

echo -e "[client]\nuser=xxx\npassword=xxx" | mysql --defaults-file=/dev/stdin -e 'select user()' <database_name>

You can usually use - instead of /dev/stdin, but it's a convention that a lot of programs (including mysql) do not honour.

A little more about the /dev/stdin file resource:

$ ls -la /dev/stdin
lrwxrwxrwx 1 root root 15 Jun 19 08:35 /dev/stdin -> /proc/self/fd/0

This way you can also check empty password situations

Improve this answer
4
  • 2
    you should use password instead of pass to not see the following error: Info: Using unique option prefix 'pass' is error-prone and can break in the future. Please use the full name 'password' instead.
    – Bash Stack
    Commented Aug 30, 2020 at 10:45
  • What's with the 'select user() bit? I couldn't get this to work. It just outputs user().
    – geoidesic
    Commented Jan 24, 2022 at 17:56
  • This is probably the safest answer, as you won't need to write the credentials to file. You could probably also use process substitution instead of stdin, but the idea is the same. Not sure if that makes a difference to whether a different process can read from the file descriptor, but perhaps someone can enlighten us on that.
    – Steen Schütt
    Commented Sep 21, 2022 at 10:57
  • this one is geeky ❤️, you deserve a +1
    – Sylvain
    Commented Jan 22 at 16:27
7

As @maxschlepzig said in a comment,

Note that there is the MYSQL_PWD environment variable which is read by mysql if you don't specify -p.

So in bash:

read -s -p "Enter the mysql password for $DBUSER @ $DBNAME: " DBPASS 
export MYSQL_PWD="$DBPASS" 
mysqldump -u $DBUSER $DBNAME > dump.sql

# once you are done
export MYSQL_PWD=""
Improve this answer
1
  • 4
    From the MySQL 8.0 documentation (below the table): "Use of MYSQL_PWD to specify a MySQL password must be considered extremely insecure and should not be used. [...] MYSQL_PWD is deprecated as of MySQL 8.0; expect it to be removed in a future version of MySQL."
    – Steen Schütt
    Commented Sep 21, 2022 at 9:26
-2

If you want to start mysql with a password provided you have to fetch the password in a variable first:

MYSQLPASS=`cat foo.php | grep '$dbpwd=' | cut -d '"' -f 2`

Then you can start your mysql command with:

mysql -U root -p ${MYSQLPASS} mydb -h friendserver
Improve this answer
2
  • 11
    This also jeopardizes your password security. Note that there is the MYSQL_PWD environment variable which is read by mysql if you don't specify -p. And under Linux this is a secure method because the environment of a user cannot be read by other unprivilleged users - in contrast to the argument vector.
    – maxschlepzig
    Commented Jul 14, 2019 at 14:55
  • You also can't trust environment variables to be secure, but it's better than providing it as an argument. Moreover, use of MYSQL_PWD is deprecated from MySQL 8.0 forward.
    – Steen Schütt
    Commented Sep 21, 2022 at 10:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .