Execute MySQL statement in shell script

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  COUNT IS : ${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 COUNT IS : ${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