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

拼搏现实的明天。 2023-07-07 13:59 53阅读 0赞

## 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"

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

1.  查询MySQL任意表的数据，并将查询到的结果保存到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