For automated operations, such as backup recovery, DBAs often need to encapsulate SQL statements into shell scripts. This paper describes several methods of SQL statements for shell scripts in MySQL database under Linux environment, for your reference. For the output of the script to beautify the results, need to further refine and adjust. The following are specific examples and their methods.
1. Embed SQL statements directly into the shell script file
--Demo Environment [[email protected] ~]# more /etc/issuecentos release 5.9 (Final) kernel \r on an \m [email protected][(None)]> show variables like ' version '; +---------------+------------+| variable_name | value |+---------------+------------+| version | 5.6.12-log |+---------------+------------+ [[email protected] ~]# more shell_call_sql1.sh #!/bin/bash# define logtimestamp= ' date +%y%m%d%h%m%s ' LOG=call_sql_${ timestamp}.logecho "start execute sql statement at ' Date '." >>${LOG} # execute sql statmysql -uroot -p123456 -e "Tee /tmp/temp.logdrop database if exists tempdb;create database tempdb;use tempdbcreate table if not existS tb_tmp (Id smallint,val varchar ());insert into tb_tmp values (1, ' Jack '), (2, ' Robin '), (3, ' mark '); select * from tb_tmp;noteequit " echo -e " \ n ">>${log }echo "Below is output result." >>${LOG}cat /tmp/temp.log>>${LOG}echo "Script executed successful." >>${LOG}exit; [[email protected] ~]# ./shell_call_sql1.sh Logging to file '/tmp/temp.log ' +------+-------+| id | val |+------+----- --+| 1 | jack | | 2 | robin | | 3 | mark |+------+-------+outfile disabled.--author : leshami--blog : http://blog.csdn.net/leshami
2, command line calls a separate SQL file
[[email protected] ~]# more Temp.sql tee /tmp/temp.logdrop database if exists tempdb;create database tempdb;use tempdbcreate table if not exists tb_tmp (Id smallint,val varchar ();insert into tb_tmp values (1, ' Jack '), (2, ' Robin '), (3, ' Mark '); select * from tb_tmp;notee [[email protected] ~]# mysql -uroot -p123456 -e "Source /root/temp.sql" logging to file '/tmp/temp.log ' +------+-------+| id | val |+------+-------+| 1 | jack | | 2 | robin | | 3 | mark |+------+-------+outfile disabled.
3, calling SQL file
using pipe breaks
[[email protected] ~]# mysql - uroot -p123456 </root/temp.sqllogging to file '/tmp/temp.log ' id val1 jack2 robin3 markOutfile disabled. #使用管道符调用SQL文件以及输出日志 [[ email protected] ~]# mysql -uroot -p123456 </root/temp.sql >/tmp/ temp.log[[email protected] ~]# more /tmp/temp.loglogging to file '/tmp/ Temp.log ' id val1 jack2 robin3 markoutfile disabled.
4, shell script in MySQL prompt down with SQL
[[email protected] ~]# more Shell_call_sql2.sh#!/bin/bashmysql -uroot -p123456 <<eofsource /root/temp.sql;select current_date ();d elete from tempdb.tb_tmp where id=3;select * from tempdb.tb_tmp where id=2; Eofexit; [[email protected] ~]# ./shell_call_sql2.shlogging to file '/tmp/temp.log ' ID val1 jack2 robin3 markoutfile disabled.current_date () 2014-10-14id val2 robin
5, Shell script variable input and output
[[email protected] ~]# more shell_call_sql3.sh#!/bin/bashcmd=] Select count (*) From tempdb.tb_tmp "cnt=$ (mysql -uroot -p123456 -s -e " ${cmd} ") echo " current &NBSP;COUNT&NBSP;IS&NBSP;:&NBSP;${CNT} "exit [[email protected] ~]# ./shell_call_sql3.sh warning: using a password on the command line interface can be insecure. current count is : 3 [[email protected] ~]# echo "select COUNT (*) from tempdb.tb_tmp "|mysql -uroot -p123456 -s3 [[email protected" ~]# more shell_call_sql4.sh#!/bin/bashid=1cmd= "Select count (*) from tempdb.tb_ Tmp where id=${id} "cnt=$ (mysql -uroot -p123456 -s -e " ${cmd} ") echo " CURRENT&NBSP;COUNT&NBSP;IS&NBSP;:&NBSP;${CNT} "Exit [[email protected] ~]# ./shell_call_sql4.sh current count is : 1 #以上脚本演示中, for the only use, the results of the output is not very regular and friendly, need to further improve and improve.
Execute MySQL statement in shell script