How to pass password to mysql command line

Question

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?

Answer 1

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

Answer 2

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]

Answer 3

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>

Answer 4

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

Answer 5

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="" 

Answer 6

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