shell编程系列25--shell操作数据库实战之备份MySQL数据，并通过FTP将其传输到远端主机

shell编程系列25--shell操作数据库实战之备份MySQL数据，并通过FTP将其传输到远端主机


备份mysql中的库或者表

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

需求：将school中的score表备份，并且将备份数据通过ftp传输到 10.11.0.148(windows) 的ftp的 bak 目录下


FTP常用指令：
    open    与FTP服务器建立连接，例子：open 10.11.0.148
    user    有权限登录FTP服务器的用户名和密码，例子：user ftpuser ftpuser


# 备份school库
[root@localhost shell]# mysqldump -udbuser -p123456 -h 10.11.0.215 school >school.sql
# 备份school库下的score表
[root@localhost shell]# mysqldump -udbuser -p123456 -h 10.11.0.215 school score >score.sql
# -A备份所有数据库，所有有权限的数据库
# mysqldump -udbuser -p123456 -h 10.11.0.215 -A >score.sql


[root@localhost shell]# sh auto_backup.sh aaa
Connected to 10.11.0.148 (10.11.0.148).
220-FileZilla Server 0.9.60 beta
220-written by Tim Kosse (tim.kosse@filezilla-project.org)
220 Please visit https://filezilla-project.org/
Remote system type is UNIX.
331 Password required for ftpuser
230 Logged on
250 CWD successful. "/bak" is current directory.
local: aaa remote: aaa
227 Entering Passive Mode (10,11,0,148,220,39)
150 Opening data channel for file upload to server of "/bak/aaa"
226 Successfully transferred "/bak/aaa"
5 bytes sent in 9.4e-05 secs (53.19 Kbytes/sec)
221 Goodbye
[root@localhost shell]# cat auto_backup.sh 
#!/bin/bash
#

db_user="dbuser"
db_password="123456"
db_host="10.11.0.215"

ftp_user="ftpuser"
ftp_password="ftpuser"
ftp_host="10.11.0.148"

src_dir="/data01/bak"
dst_dir="/bak"

function auto_ftp
{
    ftp -niv << EOF
        open $ftp_host
        user $ftp_user $ftp_password

        cd $dst_dir
        put $1
        bye
EOF
}

auto_ftp aaa


# 自动备份数据库并传输备份文件到ftp的脚本

[root@localhost shell]# cat auto_backup.sh 
#!/bin/bash
#

# 需要备份的数据库的账号信息
db_user="dbuser"
db_password="123456"
db_host="10.11.0.215"

# 远端ftp服务器的信息
ftp_user="ftpuser"
ftp_password="ftpuser"
ftp_host="10.11.0.148"

# 源目录，目标目录
src_dir="/data01/bak"
dst_dir="/bak/"
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
}
#chown -R ftpuser.ftpuser /data01
#auto_ftp "/data01/bak/school_score_20190612181401.sql"
# 备份数据
mysqldump -u"$db_user" -p"$db_password" -h"$db_host" school score > $file_name && auto_ftp $file_name

# 执行脚本
[root@localhost shell]# sh auto_backup.sh 
Connected to 10.11.0.148 (10.11.0.148).
220-FileZilla Server 0.9.60 beta
220-written by Tim Kosse (tim.kosse@filezilla-project.org)
220 Please visit https://filezilla-project.org/
Remote system type is UNIX.
331 Password required for ftpuser
230 Logged on
250 CWD successful. "/bak" is current directory.
local: school_score_20190612182616.sql remote: school_score_20190612182616.sql
227 Entering Passive Mode (10,11,0,148,197,15)
150 Opening data channel for file upload to server of "/bak/school_score_20190612182616.sql"
226 Successfully transferred "/bak/school_score_20190612182616.sql"
2349 bytes sent in 7.9e-05 secs (29734.18 Kbytes/sec)
221 Goodbye

搭建windows的ftp服务