0

I'd like to run multiple MySQL queries and save them to specified files. Queries are saved in a bash script, queries.sh, e.g.:

cat queries.sh
mysql -u <user> -p <DBname> -e "select Col1 from Table1 where Condition;" > /home/<user>/queries/Col1Table1Cond
mysql -u <user> -p <DBname> -e "select Col5 from Table2 where Condition;" > /home/<user>/queries/Col5Table2Cond

Executing the script is not enough, since <user> has to input his password at each query and this is compromising the script flow.

  • So what do you plan to do ? Keep the password in a variable ? Parse commandline switch ?? – Gilles Quenot Sep 25 '16 at 12:36
  • How about storing the password as an environment variable or in another file? – Barun Sep 25 '16 at 12:37
2

Try this ;)

user="jonnDoe"
dbname="customers"
password="VerySecret"

mysql -u $user -D $dbname -p $password -e "select Col1 from Table1 where Condition;" > /home/$user/queries/Col1Table1Cond
mysql -u $user -D $dbname -p $password -e "select Col5 from Table2 where Condition;" > /home/$user/queries/Col5Table2Cond

A better practice is to configure mysql /etc/mysql/my.cnf file with the password inside, this way, no need to enter it each times :

[client]
password    = your_password

In this later case, remove all password related things from previous script

2

There usually are a few different ways to achieve this.

Store it in an external file containing only the password:

$cat password.txt
Password

$cat queries.sh
mysql -u <user> -p$(cat password.txt) <rest of the command>

Store it in another shell script:

$cat settings.sh
PASSWORD=Password

$cat queries.sh
source settings.sh
mysql -u <user> -p"$PASSWORD" <rest of the command>

Store it in an environment variable called PASSWORD:

$cat queries.sh
mysql -u <user> -p"$PASSWORD" <rest of the command>

Also, see How to auto login mysql in shell scripts?

  • 1
    Thanks Barun. I think this solution is more portable if the script needs to be executed by different users with personal password. – dovah Sep 25 '16 at 12:51
  • Avoid using envoronment variables as stated by mysql. IF you use an external file, make sure only you have access to it chmod 0600 password.txt – Alwin Kesler Feb 20 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.