How to auto login in MySQL from a shell script?

Question

I have a MySQL server with an user with a password. I want to execute some SQL queries in shell scripts without specifying the password like this:

config.sh:

MYSQL_ROOT="root"
MYSQL_PASS="password"

mysql.sh:

source config.sh
mysql -u$MYSQL_ROOT -p$MYSQL_PASS -e "SHOW DATABASES"

How can I simplify the whole process in order to execute SQL queries without specifying the -p and -u argument etc.?

A word of advice: You should get into the habit of setting passwords on your databases. Also, it's probably not a good idea to use your root user to do everyday stuff, either. Create a user with limited privileges for doing queries. — Ian Atkin
– Ian Atkin, Commented Dec 11, 2012 at 5:57
General advice: Don't give passwords as command line arguments. They can be seen by any user logged in on the machine, even if the command only takes a fraction of a second. — quazgar
– quazgar, Commented Sep 26, 2019 at 14:43

Toby Speight · Accepted Answer · 2020-01-14 16:34:39Z

59

Alternative ways to write these options.

You can write

mysql -u "$MYSQL_ROOT" -p"$MYSQL_PASS" -e "SHOW DATABASES"

If [password is] given, there must be no space between --password= or -p and the password following it. If no password option is specified, the default is to send no password.

to pass empty strings as separate arguments. Your comment below indicates that the client will still ask for a password, though. Probably it interprets the empty argument as a database name and not as the password. So you could try the following instead:

mysql --user="$MYSQL_ROOT" --password="$MYSQL_PASS" -e "SHOW DATABASES"

.my.cnf file

But even if there is a way, I'd still suggest you use a ~/.my.cnf file instead. Arguments on the command line are likely included in a process listing generated by ps -A -ocmd, so other users can see them. The .my.cnf file, on the other hand, can (and should) be made readable only by you (using chmod 0600 ~/.my.cnf), and will be used automatically. Have that file include the following lines:

[client]
user=root
password=

Then a simple mysql -e "SHOW DATABASES" will suffice, as the client will obtain its credentials from that file.

See 6.1.2.1. End-User Guidelines for Password Security for the various ways in which you can provide a password, and their respective benefits and drawbacks. See 4.2.3.3. Using Option Files for general information on this .my.cnf file

edited Jan 14, 2020 at 16:34

Toby Speight

32.3k58 gold badges83 silver badges118 bronze badges

answered Dec 11, 2012 at 5:55

MvG

61.8k25 gold badges158 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

KeepZero Over a year ago

Thank you. If I use: mysql -u "$MYSQL_ROOT" -p "$MYSQL_PASS" -e "SHOW DATABASES", bash still ask me to Enter password

beporter Over a year ago

There can't be any space after the -p option. It must look like: -pMyPassHere or -p'My Pass Here'.

user2493235 Over a year ago

Shouldn't this be the accepted answer? Particularly since it offers the highly relevant .my.cnf option?

Beto Aveiga Over a year ago

Using .my.cnf file in home is the best choice for me. And as you mention permissions are important here. Works like a charm. Thanks.

Ken Ingram Over a year ago

This should be the accepted answer. Unless there is a better option as a matter of password security.

marco · Accepted Answer · 2014-10-31 17:35:50Z

16

As MvG suggested (recommended in the MySQL manual 4.2.2 connecting and 6.1.2.1 security guidelines) you should use a file. The password on the command line may be unsafe since ps may show it to other users. The file does not have to be .my.cnf, can be an ad-hoc option file for your script in a temporary file:

OPTFILE="$(mktemp -q --tmpdir "${inname}.XXXXXX")$"
trap 'rm -f "$OPTFILE"' EXIT
chmod 0600 "$OPTFILE"
cat >"$OPTFILE" <<EOF
[client]
password="${MYSQL_PASS}"
EOF
mysql --user="$MYSQL_ROOT" --defaults-extra-file="$OPTFILE" -e "SHOW DATABASES"

The first lines create a safe temp file, then put the options then use it. trap will protect you form OPTFILE lying around in case of interrupts.

answered Oct 31, 2014 at 17:35

marco

5905 silver badges9 bronze badges

2 Comments

marco Over a year ago

@ranind suggested changing the order in the command line to mysql --defaults-extra-file="$OPTFILE" --user="$MYSQL_ROOT" -e "SHOW DATABASES" to avoid "unknown variable" error. Both orders worked for me.

solenoid Over a year ago

This is a great answer: portable, and as secure as this method can be.

binarybelle · Accepted Answer · 2017-06-07 20:41:43Z

15

Try this:

if [ $MYSQL_PASS ]
then
  mysql -u "$MYSQL_ROOT" -p"$MYSQL_PASS" -e "SHOW DATABASES"
else
  mysql -u "$MYSQL_ROOT" -e "SHOW DATABASES"
fi

edited Jun 7, 2017 at 20:41

binarybelle

711 silver badge9 bronze badges

answered Dec 11, 2012 at 6:00

Diego Basch

13.1k2 gold badges31 silver badges24 bronze badges

6 Comments

MvG Over a year ago

I might be wrong, but as far as I recall, an empty password and no password at all are different things for MySQL.

Bill Karwin Over a year ago

@MvG, not correct. An empty password and no password are treated the same. Try it: choose a login with no password, use -p and when it prompts you, hit return.

sensadrome Over a year ago

N.B This answer is wrong, since there should be no space after the -p option. Generally speaking using a defaults file is a better way to go (should be only readable by user, so chmod 600) : mysql -u "$MYSQL_ROOT" --defaults-file=/tmp/my.cnf -e "SHOW DATABASES"

brianlmerritt Over a year ago

passwords should not be inserted into a command line, by bash or by user. It is very insecure. The -p is also wrong as there should not be a space, thankfully, as users should definitely not do this!

mmv-ru Over a year ago

But put password in command line is insecure. Any user can see password by ps

|

Community · Accepted Answer · 2020-06-20 09:12:55Z

11

Here is a little bash script you can use to get a shell really quickly

It opens up a shell so you can manually execute queries. Very convenient.

  #!/bin/bash
  DB_USER='your_db_username'
  DB_PASS='your_password'
  DB='database_name'
  echo 'logging into db $DB as $DB_USER'
  mysql -u "$DB_USER" --password="$DB_PASS" --database="$DB"

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 21, 2014 at 1:10

med116

1,61618 silver badges16 bronze badges

Comments

V. Bozz · Accepted Answer · 2019-09-26 14:31:02Z

To auto login in MySQL using Debian GNU/Linux distributions (like Ubuntu, Xubuntu, Linux Mint, gNewSense, etc. etc.) you can simply use the already existing file /etc/mysql/debian.cnf that contains valid superuser credentials.

So, you can try to auto login with this simple command:

sudo mysql --defaults-file=/etc/mysql/debian.cnf

If it works, to avoid to remember the above command you can also create some Bash aliases. You can do it pasting these commands in your user terminal:

echo ''                                                                       >> ~/.bashrc
echo "# Auto-login in MySQL"                                                  >> ~/.bashrc
echo "# From https://stackoverflow.com/a/33584904/3451846"                    >> ~/.bashrc
echo "alias mysql='sudo mysql --defaults-file=/etc/mysql/debian.cnf'"         >> ~/.bashrc
echo "alias mysqldump='sudo mysqldump --defaults-file=/etc/mysql/debian.cnf'" >> ~/.bashrc
. ~/.bashrc

And then just type this to auto login:

mysql

Collectives™ on Stack Overflow

How to auto login in MySQL from a shell script?

5 Answers 5

Alternative ways to write these options.

.my.cnf file

5 Comments

2 Comments

6 Comments

Here is a little bash script you can use to get a shell really quickly

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Alternative ways to write these options.

.my.cnf file

5 Comments

2 Comments

6 Comments

Here is a little bash script you can use to get a shell really quickly

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related