Shell脚本中执行sql语句操作mysql的5种方法
对于自动化运维,诸如备份恢复之类的,DBA经常需要将SQL语句封装到shell脚本。本文描述了在Linux环境下mysql数据库中,shell脚本下调用sql语句的几种方法,供大家参考。对于脚本输出的结果美化,需要进一步完善和调整。以下为具体的示例及其方法。
1、将SQL语句直接嵌入到shell脚本文件中
--演示环境 [root@SZDB~]#more/etc/issue CentOSrelease5.9(Final) Kernel\ronan\m root@localhost[(none)]>showvariableslike'version'; +---------------+------------+ |Variable_name|Value | +---------------+------------+ |version |5.6.12-log| +---------------+------------+ [root@SZDB~]#moreshell_call_sql1.sh #!/bin/bash #Definelog TIMESTAMP=`date+%Y%m%d%H%M%S` LOG=call_sql_${TIMESTAMP}.log echo"Startexecutesqlstatementat`date`.">>${LOG} #executesqlstat mysql-uroot-p123456-e" tee/tmp/temp.log dropdatabaseifexiststempdb; createdatabasetempdb; usetempdb createtableifnotexiststb_tmp(idsmallint,valvarchar(20)); insertintotb_tmpvalues(1,'jack'),(2,'robin'),(3,'mark'); select*fromtb_tmp; notee quit" echo-e"\n">>${LOG} echo"belowisoutputresult.">>${LOG} cat/tmp/temp.log>>${LOG} echo"scriptexecutedsuccessful.">>${LOG} exit; [root@SZDB~]#./shell_call_sql1.sh Loggingtofile'/tmp/temp.log' +------+-------+ |id |val | +------+-------+ | 1|jack | | 2|robin| | 3|mark | +------+-------+ Outfiledisabled.
2、命令行调用单独的SQL文件
[root@SZDB~]#moretemp.sql tee/tmp/temp.log dropdatabaseifexiststempdb; createdatabasetempdb; usetempdb createtableifnotexiststb_tmp(idsmallint,valvarchar(20)); insertintotb_tmpvalues(1,'jack'),(2,'robin'),(3,'mark'); select*fromtb_tmp; notee [root@SZDB~]#mysql-uroot-p123456-e"source/root/temp.sql" Loggingtofile'/tmp/temp.log' +------+-------+ |id |val | +------+-------+ | 1|jack | | 2|robin| | 3|mark | +------+-------+ Outfiledisabled.
3、使用管道符调用SQL文件
[root@SZDB~]#mysql-uroot-p123456</root/temp.sql Loggingtofile'/tmp/temp.log' id val 1 jack 2 robin 3 mark Outfiledisabled. #使用管道符调用SQL文件以及输出日志 [root@SZDB~]#mysql-uroot-p123456</root/temp.sql>/tmp/temp.log [root@SZDB~]#more/tmp/temp.log Loggingtofile'/tmp/temp.log' id val 1 jack 2 robin 3 mark Outfiledisabled.
4、shell脚本中MySQL提示符下调用SQL
[root@SZDB~]#moreshell_call_sql2.sh #!/bin/bash mysql-uroot-p123456<<EOF source/root/temp.sql; selectcurrent_date(); deletefromtempdb.tb_tmpwhereid=3; select*fromtempdb.tb_tmpwhereid=2; EOF exit; [root@SZDB~]#./shell_call_sql2.sh Loggingtofile'/tmp/temp.log' id val 1 jack 2 robin 3 mark Outfiledisabled. current_date() 2014-10-14 id val 2 robin
5、shell脚本中变量输入与输出
[root@SZDB~]#moreshell_call_sql3.sh #!/bin/bash cmd="selectcount(*)fromtempdb.tb_tmp" cnt=$(mysql-uroot-p123456-s-e"${cmd}") echo"Currentcountis:${cnt}" exit [root@SZDB~]#./shell_call_sql3.sh Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure. Currentcountis:3 [root@SZDB~]#echo"selectcount(*)fromtempdb.tb_tmp"|mysql-uroot-p123456-s 3 [root@SZDB~]#moreshell_call_sql4.sh #!/bin/bash id=1 cmd="selectcount(*)fromtempdb.tb_tmpwhereid=${id}" cnt=$(mysql-uroot-p123456-s-e"${cmd}") echo"Currentcountis:${cnt}" exit [root@SZDB~]#./shell_call_sql4.sh Currentcountis:1 #以上脚本演示中,作抛砖引玉只用,对于输出的结果不是很规整友好,需要进一步改善和提高。