Shell脚本中执行mysql语句
来源:互联网 发布:太早遇到对的人 知乎 编辑:程序博客网 时间:2024/06/05 14:39
http://www.2cto.com/database/201410/343557.html
对于自动化运维,诸如备份恢复之类的,DBA经常需要将SQL语句封装到shell脚本。本文描述了在Linux环境下mysql数据库中,shell脚本下调用sql语句的几种方法,供大家参考。对于脚本输出的结果美化,需要进一步完善和调整。以下为具体的示例及其方法。
1、将SQL语句直接嵌入到shell脚本文件中
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
--演示环境
[root@SZDB ~]# more /etc/issue
CentOS release 5.9 (Final)
Kernel \r
on
an \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.
--Author : Leshami
--Blog : http://blog.csdn.net/leshami
2、命令行调用单独的SQL文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[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、使用管道符调用SQL文件<br ><="" p="">
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[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、shell脚本中MySQL提示符下调用SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[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、shell脚本中变量输入与输出
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[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 is : ${cnt}"
exit
[root@SZDB ~]# ./shell_call_sql3.sh
Warning: Using a
password
on
the command line interface can be 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 is : ${cnt}"
exit
[root@SZDB ~]# ./shell_call_sql4.sh
Current
count
is
: 1
#以上脚本演示中,作抛砖引玉只用,对于输出的结果不是很规整友好,需要进一步改善和提高
0 0
- shell脚本中执行mysql语句
- Shell 脚本中执行mysql语句
- Shell脚本中执行mysql语句
- shell脚本执行mysql语句
- shell脚本中导入mysql数据&&执行mysql语句
- shell脚本中导入mysql数据&&执行mysql语句
- shell脚本中导入mysql数据&&执行mysql语句
- Shell脚本中执行sql语句操作mysql
- Shell脚本中执行sql语句,操作mysql数据库
- Shell脚本中执行sql语句操作mysql
- 用shell脚本执行mysql语句
- Shell脚本中执行sql语句操作mysql的5种方法
- Shell脚本中执行sql语句操作mysql的5种方法
- 在SHELL中执行MYSQL语句
- 在SHELL中执行MYSQL语句
- shell中执行mysql语句的方法
- shell脚本执行mysql语句, 该怎么玩?
- shell 执行mysql语句
- 利用Qt来实现一个时钟小程序
- 深入探讨在集群环境中使用 EhCache 缓存系统
- 解决编译libiconv时报错:./stdio.h:1010:1: error: 'gets' undeclared here (not in a function)
- 非常详细GC学习笔记
- springMVC学习笔记---修改配置文件路径和传递数据
- Shell脚本中执行mysql语句
- 如何快速的将pdf转换成txt格式
- ext的相关
- JAVAweb 学习总结
- java中二叉排序树模型的建立
- mysql数据库数据迁移方法
- PAT 1001. A+B Format (20) JAVA实现
- JavaScript 字符串转换数字
- android压力测试工具monkey