Run MySQL Queries from Bash Script

bashMySQLscripting

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.

Best Answer

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

Related Question