【Shell脚本编程（七）】Shell脚本操作数据库实战

1. 安装MySQL数据库

yum list all | grep mariadb

#在线安装
yum install mariadb mariadb-server mariadb-libs -y

#查看状态
systemctl status mariadb

#启动mariadb
systemctl start mariadb

#查看端口是否已经监听
netstat -tnlp | grep :3306

#登录mysql,root用户下
mysql

#查看版本
select version();

#显示当前已经有的数据库
show databases;

#创建school数据库并设置字符集为utf8
create database school default character set utf8;

#将school.sql脚本加载到school数据库中(shell命令)
mysql school < school.sql

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','aiyisitan');
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',90);
insert into score values('1002','1001',70);
insert into score values('1002','1001',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);

2. Shell脚本与MySQL数据库交互（增删改查）

2.1. mysql命令参数详解

• -u 用户名
• -p 用户密码
• -h 服务器IP地址
• -D 连接的数据库
• -N 不输出列信息
• -B 使用tab键代替默认交互分隔符
• -e 执行SQL语句

其它选项：

• -E 垂直输出
• -H 以HTML格式输出
• -X 以XML格式输出

1. 写一个脚本，该脚本可以接收两个参数，参数为数据库和需要执行的SQL语句；

vim operate_mysql.sh

#!/bin/bash
#

user="dbuser"
password="123456"
host="192.168.184.132"
db_name="$1"
SQL="$2"

mysql -u"$user" -p"$password" -h"$host" -D"$db_name" -B -e "$SQL"

#脚本文件执行命令
sh operate_mysql.sh school "SELECT * FROM student"

2. 查询MySQL任意表的数据，并将查询到的结果保存到HTML文件中；

vim operate_mysql.sh

#!/bin/bash
#

user="dbuser"
password="123456"
host="192.168.184.132"
db_name="$1"
SQL="$2"

mysql -u"$user" -p"$password" -h"$host" -D"$db_name" -B -e "$SQL"

#脚本文件执行命令
sh operate_mysql.sh school "SELECT * FROM student" > result.HTML

3. 查询MySQL任意表的数据，并将查询到的结果保存到XML文件中；

vim operate_mysql.sh

#!/bin/bash
#

user="dbuser"
password="123456"
host="192.168.184.132"
db_name="$1"
SQL="$2"

mysql -u"$user" -p"$password" -h"$host" -D"$db_name" -B -e "$SQL"

#脚本文件执行命令
sh operate_mysql.sh school "SELECT * FROM student" > result.XML

3. 利用Shell脚本将文本数据导入到MySQL中

如何将文本中格式化的数据导入到MySQL数据库中？

• 需求1：处理文本中的数据，将文本中的数据插入MySQL中;

data.txt

1010    jerry   1991-12-13  male
1011    mike    1991-12-13  female
1012    tracy   1991-12-13  male
1013    kobe    1991-12-13  male
1014    allen   1991-12-13  female
1015    curry   1991-12-13  male
1016    tom     1991-12-13  female

vim import_mysql.sh

#!/bin/bash
#

user="dbuser"
password="123456"
host="192.168.184.132"

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

cat data.txt | while read id name birth sex
do 
    if [$id -gt 1014];then
        $mysql_conn -e "INSERT INTO school.student values('$id','$name','$birth','$sex')"
    fi
done

• 需求2：

data2.txt

2021|hao|1989-12-21|male
2022|zhang|1989-12-21|male
2023|ouyang|1989-12-21|male
2024|li|1989-12-21|female

vim import_mysql-1.sh

#!/bin/bash
#

user="dbuser"
password="123456"
host="192.168.184.132"

IFS="|"

cat data2.txt | while read id name birth sex
do 
    mysql -u"$user" -p"$password" -h"$host" -e "INSERT INTO school.student values('$id','$name','$birth','$sex')"
done

4. 备份MySQL数据，并通过FTP将其传输到远程主机

备份MySQL中的库或表

mysqldump常用参数详解：

• -u 用户名
• -p 密码
• -h 服务器IP地址
• -d 等价于–no-data 只导出表结构
• -t 等价于–no-create-info 只导出数据，不导出建表语句
• -A 等价于–all-databases
• -B 等价于–databases 导出一个或多个数据库

需求：将school中的score表备份，并且将备份数据通过FTP传输到192.168.184.3的/data/backup目录下

#备份数据库命令
mysqldump -udbuser -p123456 -h192.168.184.132 school > school.sql

FTP常用指令：

• open 与FTP服务器建立连接，例子：open 192.168.184.3
• user 有权限登录FTP服务器的用户名和密码，例子：user ftp_user redhat

#测试文件：1.txt

test ftp
test。。。

vim ftp.sh

#!/bin/bash
#

ftp -inv << EOF
    open 192.168.184.3
    user ftp_user redhat
    
    cd /tmp
    put 1.txt
    bye
EOF

vim auto_backup.sh

#!/bin/bash
#

dbuser="dbuser"
db_password="123456"
db_host="192.168.184.132"

ftp_user="ftp_user"
ftp_password="redhat"
ftp_host="192.168.184.3"

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

function auto_ftp
{
    ftp -niv << 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 > $src_dir/$file_name && auto_ftp $src_dir/$file_name