（转）第六课 Shell脚本编程-Shell脚本操作数据库实战

原文：https://blog.csdn.net/aa18855953229/article/details/127380172

第一节 数据导入和授权
测试数据 school.sql
--建表
--学生表
CREATE TABLE `student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
insert into student values('1001' , 'zhaolei' , '1990-1001-1001' , 'male');
insert into student values('1002' , 'lihang' , '1990-12-21' , 'male');
insert into student values('1003' , 'yanwen' , '1990-1005-20' , 'male');
insert into student values('1004' , 'hongfei' , '1990-1008-1006' , 'male');
insert into student values('1005' , 'ligang' , '1991-12-1001' , 'female');
insert into student values('1006' , 'zhousheng' , '1992-1003-1001' , 'female');
insert into student values('1007' , 'wangjun' , '1989-1007-1001' , 'female');
insert into student values('1008' , 'zhoufei' , '1990-1001-20' , 'female');
--课程表测试数据
insert into course values('1001' , 'chinese' , '1002');
insert into course values('1002' , 'math' , '1001');
insert into course values('1003' , 'english' , '1003');

--教师表测试数据
insert into teacher values('1001' , 'aidisheng');
insert into teacher values('1002' , 'aiyinsitan');
insert into teacher values('1003' , 'qiansanqiang');

--成绩表测试数据
insert into score values('1001' , '1001' , 80);
insert into score values('1001' , '1002' , 90);
insert into score values('1001' , '1003' , 99);
insert into score values('1002' , '1001' , 70);
insert into score values('1002' , '1002' , 60);
insert into score values('1002' , '1003' , 80);
insert into score values('1003' , '1001' , 80);
insert into score values('1003' , '1002' , 80);
insert into score values('1003' , '1003' , 80);
insert into score values('1004' , '1001' , 50);
insert into score values('1004' , '1002' , 30);
insert into score values('1004' , '1003' , 20);
insert into score values('1005' , '1001' , 76);
insert into score values('1005' , '1002' , 87);
insert into score values('1006' , '1001' , 31);
insert into score values('1006' , '1003' , 34);
insert into score values('1007' , '1002' , 89);
insert into score values('1007' , '1003' , 98);
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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
创建数据库
create database school default character set utf8;
1
载入数据
# mysql -u 用户名 -p 数据库名 < 数据库文件
mysql -u root -p school < / root/ school.sql
# 登陆查看是否载入成功
mysql -u root -p
use school;
show tables;
# 查看数据库中的表，数据已经载入
select * from student;
# 赋予用户数据库权限
# 赋予任意主机普通数据用户dbuser，查询、插入、更新、删除 数据库中school任意表的权利。
grant all on school.* to dbuser@'%' identified by '123456';
grant all on school.* to dbuser@localhost identified by '123456';
grant select on school.* to dbuser@'%' identified by '123456' ;
grant insert,on school.* to dbuser@'%' identified by '123456' ;
grant update on school.* to dbuser@'%' identified by '123456';
grant delete on school.* to dbuser@'%' identified by '123456';
# 操作权限后
use mysql;
show tables;
desc user; # 查看user表结构
select User,Host,Password FROM user; # 可以看到我们的dbuser
mysql -udbuser -p123456 -h localhost school < /root/school.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
第二节 shell和数据库交互
mysql命令参数详解：
-u 用户名
-p 用户密码
-h 服务器IP地址
-D 连接的数据库
-N 不输出列信息
-B 使用tab键代替默认交互分隔符
-e 执行SQL语句
其他选项
-E 垂直输出
-H 以HTML格式输出
-X 以XML格式输出
# 不进入交互模式 -e
mysql -u dbuser -p123456 -Dschool -e "select * from student "
# -B 使用tab键代替默认交互分隔符
mysql -u dbuser -p123456 -Dschool -B -e "select * from student "
# -N 不输出列信息
mysql -u dbuser -p123456 -Dschool -N -e "select * from student "

# —E 垂直输出
mysql -u dbuser -p123456 -D school -N -E -e "select * from student "

# -H 以HTML格式输出
mysql -u dbuser -p123456 -D school -N -H -B -E -e "select * from student
1
2
3
4
5
6
7
8
9
10
11
12
写一个脚本，该脚本可以接收两个参数，参数为数据库名称和需要执行的SQL语句
#!/bin/bash

user="dbuser"
password="123456"
db_name="$1"
SQL="$2"
mysql -u"$user" -p"$password" -D "$db_name" -e "$SQL"

# 执行
./test.sh school "select * from course"
1
2
3
4
5
6
7
8
9
10
第三节 利用Shell脚本将文本数据导入到MySQL中
文本一：处理文本中的数据，将文本中的数据插入Mysql中
1010 jerry 1990-1001-1001 male
1011 mike 1990-12-21 female
1012 tracy 1990-1005-20 male
1013 kobe 1990-1008-1006 male
1014 allen 1989-1007-1001 female
1015 curry 1991-12-1001 female
1016 ztom 1992-1003-1001 female
1
2
3
4
5
6
7
脚本test.sh
#!/bin/bash

user="dbuser"
password="123456"

mysql_conn="mysql -u"$user" -p"$password" "

cat data.txt | while read id name birth sex
do
$mysql_conn -e "insert into school.student values('$id','$name','$birth','$sex')"
done
1
2
3
4
5
6
7
8
9
10
11
# 插入前数据
select* from student;
# 执行脚本
./test1.sh
# 查看结果
select* from student;
1
2
3
4
5
6
文本二：处理文本中的数据，将文本中的数据插入Mysql中
2021|hao|1989-12-21|male
2022|zhang|1989-12-21|male
2023|ouyang|11989-12-21|male
2024|li|11989-12-21|female
1
2
3
4
脚本test.sh
#!/bin/bash

user="dbuser"
password="123456"

IFS='|' # 设置系统默认的分割符

cat data.txt | while read id name birth sex
do
mysql -u"$user" -p"$password" -e "insert into school.student values('$id','$name','$birth','$sex')"
done
1
2
3
4
5
6
7
8
9
10
11
12
第四节 备份MySQL数据
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yFo6IPEO-1666024558252)(.\printscreen\6.1.png)]

# mysqldump数据量大不是很合适
mysqldump -udbuser -p123456 school > school.sql
cat school.sql
1
2
3
FTP常用指令：
open 与FTP服务器建立连接
user 有权限登录FTP服务器的用户名和密码
需求：将school中的score表备份，并且将备份数据通过FTP传输到192.168.2.100的/data/backup目录下
#!/bin/bash

db_user="dbuser"
db_password="123456"

ftp_user="ftp_user"
ftp_password="redhat"
ftp_host="192.168.2.100"

dst_dir="/data/backup"
time_date="`date+%Y%m%d%H%M%S`"
file_name="school_score_${time_date}.sql"

# ftp -i关闭交互 -n自动登录 -v显示传输日志
# 通过<< EOF 给ftp发送指令
function auto_ftp
{
ftp -inv << EOF
open $ftp_host
user $ftp_user $ftp_password

cd $dst_dir
put $1
bye
EOF
}

mysqldump -u"$db_user" -p"$db_password" -h"$db_host" school score > ./$file_name && auto_ftp ./$file_name
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
每隔一分钟执行一次
# crontab是用来定期执行程序的命令。
# -e : 执行文字编辑器来设定时程表，内定的文字编辑器是 VI，如果你想用别的文字编辑器，则请先设定 VISUAL 环境变数来指定使用那个文字编辑器(比如说 setenv VISUAL joe)
# -r : 删除目前的时程表
# -l : 列出目前的时程表

* * * * *
- - - - -
| | | | |
| | | | +----- 星期中星期几 (0 - 7) (星期天 为0)
| | | +---------- 月份 (1 - 12)
| | +--------------- 一个月中的第几天 (1 - 31)
| +-------------------- 小时 (0 - 23)
+------------------------- 分钟 (0 - 59)

# 每一分钟执行一次 /bin/ls：
* * * * * /bin/ls
# 在12月内, 每天的早上 6 点到 12 点，每隔2个小时0分钟执行一次 /usr/bin/backup：
0 6-12/2 * 12 * /usr/bin/backup
# 每月每天的午夜0点20分, 2点30分, 4点20分....执行 echo "haha"：
20 0-23/2 * * * echo "haha"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
crontab -e
# 日志不输出
*/1 * * * * sh /root/test.sh &> /dev/null
————————————————