Execute SQL statement action in shell script

This article mainly describes the shell script in the execution of SQL statements 5 ways to operate MySQL, this article explains the SQL statement embedded directly into the shell script file, the command line calls a separate SQL file, using the pipe character call SQL file, etc., the need for friends can refer to the following

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 (20));INSERT into tb_tmp values (1, ' Jack '), (2, ' Robin '), (3, ' Mark ');SELECT * from Tb_tmp;NoteeQuitEcho-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.  

2. The command line invokes a separate SQL file

[Email protected] ~]# more Temp.sqlTee/tmp/temp.logDrop database if exists tempdb;Create database tempdb;Use tempdbCREATE table if not exists tb_tmp (ID smallint,val varchar (20));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. Invoking the SQL file using the pipe character

[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.log   Logging to file '/tmp/temp.log '  

4. The MySQL prompt in the shell script is lowered with SQL

[Email protected] ~]# more shell_call_sql2.sh#!/bin/bashmysql-uroot-p123456 <<eofSource/root/temp.sql;Select Current_date ();Delete 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 val 1 jack 2 Robin 3 Mark Ou Tfile disabled. current_date () 2014-10-14 ID val 2 Robin

5. Variable input and output in shell script

[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.shWarning:using a password on the command line interface can is 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/bash id=1 cmd= ' select COUNT (*) from T   empdb.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

Execute SQL statement action in shell script