For automated operational dimensions, such as backup recovery, DBAs often need to encapsulate SQL statements into shell scripts. This paper describes several methods of using SQL statements to reduce the shell script in the MySQL database under Linux environment for reference. For the results of the script output beautification, need further refinement and adjustment. The following are concrete examples and their methods.
1. Embed SQL statements directly into the shell script file
Copy Code code as follows:
--Demo environment
[Root@szdb ~]# More/etc/issue
CentOS release 5.9 (Final)
Kernel \ r \m
root@localhost[(none)]> show variables like ' version ';
+---------------+------------+
| variable_name | Value |
+---------------+------------+
| Version | 5.6.12-log |
+---------------+------------+
[Root@szdb ~]# more shell_call_sql1.sh
#!/bin/bash
# Define Log
timestamp= ' Date +%y%m%d%h%m%s '
Log=call_sql_${timestamp}.log
echo "Start Execute SQL statement at ' date '." >>${log}
# Execute SQL Stat
Mysql-uroot-p123456-e "
Tee/tmp/temp.log
Drop database if exists tempdb;
Create database tempdb;
Use tempdb
CREATE 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
Quit
Echo-e "\ n" >>${log}
echo "Below is output result." >>${log}
Cat/tmp/temp.log>>${log}
echo "script executed successful." >>${log}
Exit
[Root@szdb ~]#./shell_call_sql1.sh
Logging to file '/tmp/temp.log '
+------+-------+
| ID | Val |
+------+-------+
| 1 | Jack |
| 2 | Robin |
| 3 | Mark |
+------+-------+
outfile disabled.
2, the command line to invoke a separate SQL file
Copy Code code as follows:
[Root@szdb ~]# more Temp.sql
Tee/tmp/temp.log
Drop database if exists tempdb;
Create database tempdb;
Use tempdb
CREATE 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
[Root@szdb ~]# 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. Call SQL file with pipe character
Copy Code code as follows:
[Root@szdb ~]# mysql-uroot-p123456 </root/temp.sql
Logging to file '/tmp/temp.log '
ID val
1, Jack.
2 Robin
3 mark.
outfile disabled.
#使用管道符调用SQL文件以及输出日志
[Root@szdb ~]# mysql-uroot-p123456 </root/temp.sql >/tmp/temp.log
[Root@szdb ~]# More/tmp/temp.log
Logging to file '/tmp/temp.log '
ID val
1, Jack.
2 Robin
3 mark.
outfile disabled.
4, the shell script in the MySQL prompt down with SQL
Copy Code code as follows:
[Root@szdb ~]# more shell_call_sql2.sh
#!/bin/bash
mysql-uroot-p123456 <<eof
Source/root/temp.sql;
Select Current_date ();
Delete from tempdb.tb_tmp where id=3;
SELECT * from tempdb.tb_tmp where id=2;
Eof
Exit
[Root@szdb ~]#./shell_call_sql2.sh
Logging to file '/tmp/temp.log '
ID val
1, Jack.
2 Robin
3 mark.
outfile disabled.
Current_date ()
2014-10-14
ID val
2 Robin
5. Variable input and output in shell script
Copy Code code as follows:
[Root@szdb ~]# more shell_call_sql3.sh
#!/bin/bash
Cmd= "SELECT COUNT (*) from tempdb.tb_tmp"
cnt=$ (Mysql-uroot-p123456-s-E "${cmd}")
echo "Current count are: ${cnt}"
Exit
[Root@szdb ~]#./shell_call_sql3.sh
Warning:using a password on the command line interface can is insecure.
Current Count Is:3
[Root@szdb ~]# Echo Select COUNT (*) from tempdb.tb_tmp "|mysql-uroot-p123456-s
3
[Root@szdb ~]# more shell_call_sql4.sh
#!/bin/bash
Id=1
Cmd= "SELECT COUNT (*) from tempdb.tb_tmp where Id=${id}"
cnt=$ (Mysql-uroot-p123456-s-E "${cmd}")
echo "Current count are: ${cnt}"
Exit
[Root@szdb ~]#./shell_call_sql4.sh
Current Count Is:1
#以上脚本演示中, for the output of the results are not very structured friendly, need further improvement and improvement.
