How to pass password to mysql command line

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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

Method 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
<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="7303120000041c01174e2333464604430117">[email protected]</a>

Make sure that you secure this file.

Then run:

mysql --defaults-extra-file=<path to the new config file> [all my other options]

Method 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>

Method 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]user=xxxnpassword=xxx" | mysql --defaults-file=/dev/stdin -e 'select user()

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

Method 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

Method 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

Method 7

# Install 
sudo apt install mysql-client

# Using
mysql -u my_user -h my_host -P 9130 -D my_database -p

# With password inline
mysql -u my_user -h my_host -P 9130 -D my_database -pmypass

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0