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
#以上脚本演示中，作抛砖引玉只用，对于输出的结果不是很规整友好，需要进一步改善和提高。