Running mysql query from bash; where query is stored in a separate file w/ variables set in bash

Question

I have a bash script that is responsible for querying a database and piping the results to a file:

date=`date +"%Y/%m/%d"`

/usr/bin/mysql -u $db_user -p$db_pass -h $db_name $db_schema << MYSQLEOF > $output_file

  select *
  from table
  where date = $date

MYSQLEOF

Now expanding this project into other areas, for the sake of re-usability/orthogonality I wanted to house the query in it's own file and call with something like:

cmd=`echo $sql_file`

/usr/bin/mysql -u $db_user -p$db_pass -h $db_name $db_schema -e "$cmd" > $output_file

sql file:

  select *
  from table
  where date = $date

I am having trouble finding out how I can (in bash) do this, while still being able to have and adjust variables ($date) inside the sql file. Echoing the .sql file into a bash variable, I haven't been able to get "$date" to not be taken as a literal string.

Is there a solution in bash, or should I look into something like perl to handle this?

Sammitch · Accepted Answer · 2012-11-15 19:25:32Z

1

Use the mySQL CLI's source command.

queryfile=my_query.sql
/usr/bin/mysql -u $db_user -p$db_pass -h $db_name $db_schema -e "source $queryfile" > $output_file

answered Nov 15, 2012 at 19:25

Sammitch

32.5k7 gold badges58 silver badges93 bronze badges

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

1 Comment

zvxr Over a year ago

Thanks; I couldn't get this to work but I believe it was getting mysql to recognize $queryfile as a path. Definitely doing more research into 'source'.

zvxr · Accepted Answer · 2012-11-15 20:50:08Z

1

I found a solution using command-line Perl to search/replace on the fly.

Bash script:

date=`date +"%Y/%m/%d"`
export date

cmd=`perl -lpe 's/DATE_VAR/$ENV{date}/g' "$sql_file"`

/usr/bin/mysql -u $db_user -p$db_pass -h $db_name $db_schema -e "$cmd" > $output_file

and in sql_file:

select *
from table
where date = 'DATE_VAR'

answered Nov 15, 2012 at 20:50

zvxr

1871 gold badge5 silver badges12 bronze badges

Collectives™ on Stack Overflow

Running mysql query from bash; where query is stored in a separate file w/ variables set in bash

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related