Bash script with multiple queries and redirection in the same mysql session

Question

My bash script queries a mysql database 3 times and redirects the standard out of each query to a file (3 different files in total with different columns structure ).

I want it to ask for the mysql password as it's important for me not to have the password in the script or on disk.

How can I include all queries and stdout redirection in the same mysql session in order to avoid asking for the password 3 times?

This is what I have now:

#!/bin/bash 
mysql -h database.com -u user -p -e "USE database; mysql query1"  > file1
mysql -h database.com -u user -p -e "USE database; mysql query2"  > file2
mysql -h database.com -u user -p -e "USE database; mysql query3"  > file3

codeforester · Accepted Answer · 2018-05-25 19:06:01Z

0

You could use tee and notee commands and write a single query file, say queries.sql and invoke it in a single shot:

use database
tee file1
query1
notee
tee file2
query2
notee
tee file3
query3
notee

Then invoke it:

mysql -h database.com -u user -p -e "source queries.sql" > /dev/null

answered May 25, 2018 at 18:51

codeforester

43.8k21 gold badges122 silver badges159 bronze badges

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

Comments

landru27 · Accepted Answer · 2018-05-25 19:24:45Z

0

You could use bash to prompt for the password, and then supply it to each of the mysql commands:

#!/bin/bash

echo "enter the password for MySQL:"
read -s PASSWD

mysql -h database.com -u user -p$PASSWD -e "USE database; mysql query1"  > file1
mysql -h database.com -u user -p$PASSWD -e "USE database; mysql query2"  > file2
mysql -h database.com -u user -p$PASSWD -e "USE database; mysql query3"  > file3

Here is a POSIX-compliant version of silent prompting for non-bash shells:

stty -echo
printf "enter the password for MySQL:"
read PASSWD
stty echo
printf "\n"

edited May 25, 2018 at 19:24

answered May 25, 2018 at 19:16

landru27

1,71214 silver badges20 bronze badges

Collectives™ on Stack Overflow

Bash script with multiple queries and redirection in the same mysql session

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related