shell 备份mysql库到远程_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服务器建立连接，例子：open10.11.0.148user 有权限登录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.shaaa

Connected to10.11.0.148 (10.11.0.148).220-FileZilla Server 0.9.60beta220-written by Tim Kosse (tim.kosse@filezilla-project.org)220 Please visit https://filezilla-project.org/

Remote system type is UNIX.331 Password required forftpuser230Logged on250 CWD successful. "/bak"is current directory.

local: aaa remote: aaa227 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)221Goodbye

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

functionauto_ftp

{ftp -niv <

open $ftp_host

user $ftp_user $ftp_password

cd $dst_dir

put $1bye

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"

functionauto_ftp

{ftp -niv <

open $ftp_host

user $ftp_user $ftp_password

cd $dst_dir

put $1bye

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.shConnected to10.11.0.148 (10.11.0.148).220-FileZilla Server 0.9.60beta220-written by Tim Kosse (tim.kosse@filezilla-project.org)220 Please visit https://filezilla-project.org/

Remote system type is UNIX.331 Password required forftpuser230Logged on250 CWD successful. "/bak"is current directory.

local: school_score_20190612182616.sql remote: school_score_20190612182616.sql227 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服务