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. The command line invokes a separate SQL file

[Email protected] ~]# more Temp.sql tee/tmp/temp.logdrop database if exists tempdb;create database Tempdb;use Tempdbcrea Te table if not exists tb_tmp (ID smallint,val varchar); INSERT into tb_tmp values (1, ' Jack '), (2, ' Robin '), (3, ' mark '); SE Lect * 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.loglogging to file '/tmp/temp.log ' ID      val1       Jack2       robin3       markoutfile disabled.

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 ();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. 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.sh warning:using A P Assword 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 prot Ected] ~]# 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 was: ${cnt}" exit [[email protected] ~]#./shell_call_sql4.sh current Cou NT is:1# above script demonstration, make a use of, for the output result is not very regular friendly, need to further improve and improve.

Execute MySQL statement in Shell script