……
,last_modified_date
from customer
where last_modified_date >=@start_date and last_modified_date <= @end_date;
#!/bin/bash
## script name; mysql_script_runner.sh
## wrapper script to execute mysql script with variables
ARGS=4
if [ $# -ne "$ARGS" ]
then
echo "you passed $# parameters"
echo "Usage: `basename $0` sql_script_file start_date end_date output_file"
exit
fi
sql_script=$1
start_date=$2
end_date=$3
output_file=$4
#run mysql query with paramenters
/usr/bin/mysql –uuser_id -ppassword –h mysql-host -A -e "set @start_date=${start_date}; set @end_date=${end_date}; source ${sql_script};" >${data_file};
exit
# end of script.
To execute the script from command line, I can do something like this:
# mysql_script_runner.sh get_customer_record.sql ‘2011-06-01’ ‘2011-06-12 23:59:59’ cust_rec.dat
I can also set it up to run as a cron job with some small changes.
Posts
3 comments:
Hi Buddy..
May I know how can I use @Var in LOAD DATA LOCAL INFILE command?
my @Var =/home/rc-user/AutoJob/Sample.txt
Sample.txt contains all the data which I want to load.
Please note that (@Var) DOES NOT HAVE (') - Single Quotes at the beginning or end.
when I tried,
LOAD DATA LOCAL INFILE @Var, it gave me below error message:
ERROR 1064 (42000) at line 2 in file: '/home/rc-user/AutoJob/generic_load.sql': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@Var
INTO TABLE demo_auto_load
FIELDS TERMINATED BY ';' LINES TERMINAT' at line 1
very well
one downside thing of this technique that it can not be used with DROP table and DROP user statements.
User variables can appear only where expressions are allowed, not where constants or literal identifiers must be provided.
check: https://www.safaribooksonline.com/library/view/mysql-cookbook-2nd/059652708X/ch01s27.html
was stuck with errors when i tried to pass arguments from bash script to a .sql script to be used in drop user (i get the username to drop from bash script). the only solution i found was to do whatever it is required to be done in bash script then call the .sql script.
I needed this for creating new (readonly)users which has the same limitation unfortunately. I ended up making a shell script which generates .sql scripts by replacing some template variables by using sed.
${MYSQL_ROOT_HOST} is a environment var set by Docker:
#!/bin/bash
declare -a arr=("username1" "username2")
## now loop through the above array
for i in "${arr[@]}"
do
echo " Username: $i"
sed "s/{username}/$i/g; s/{host}/${MYSQL_ROOT_HOST}/g" /tmp/create_users_for_database.sql > /tmp/1.sql
mysql -h "localhost" -u "root" "-ptest" "mysql" < /tmp/1.sql
done
My template create_users_for_database.sql script:
create user '{username}_read'@'{host}' identified by '{username}_read';
GRANT SELECT ON *.* TO '{username}_read'@'{host}';
Post a Comment