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).

-FileZilla Server 0.9. beta

-written by Tim Kosse (tim.kosse@filezilla-project.org)

Please visit https://filezilla-project.org/

Remote system type is UNIX.

Password required for ftpuser

Logged on

CWD successful. "/bak" is current directory.

local: aaa remote: aaa

Entering Passive Mode (,,,,,)

Opening data channel for file upload to server of "/bak/aaa"

Successfully transferred "/bak/aaa"

bytes sent in 9.4e-05 secs (53.19 Kbytes/sec)

Goodbye

[root@localhost shell]# cat auto_backup.sh

#!/bin/bash

#

db_user="dbuser"

db_password=""

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 $

bye

EOF

}

auto_ftp aaa

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

[root@localhost shell]# cat auto_backup.sh

#!/bin/bash

#

# 需要备份的数据库的账号信息

db_user="dbuser"

db_password=""

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 $

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).

-FileZilla Server 0.9. beta

-written by Tim Kosse (tim.kosse@filezilla-project.org)

Please visit https://filezilla-project.org/

Remote system type is UNIX.

Password required for ftpuser

Logged on

CWD successful. "/bak" is current directory.

local: school_score_20190612182616.sql remote: school_score_20190612182616.sql

Entering Passive Mode (,,,,,)

Opening data channel for file upload to server of "/bak/school_score_20190612182616.sql"

Successfully transferred "/bak/school_score_20190612182616.sql"

bytes sent in 7.9e-05 secs (29734.18 Kbytes/sec)

Goodbye

搭建windows的ftp服务