mysql远程备份脚本_rsnapshot远程备份mysql数据的shell脚本

rsnapshot是一个开源的备份和恢复工具。

它可以创建每天，每周，每小时和每月备份文件系统。

它也可以创建本地和远程的MySQL服务器的备份。

如何使用这个脚本工具呢？

我们需要做好如下的配置：

复制代码 代码示例:

backup.example.com ==> rsnapshot server with RAID protected disks.

mysql.example.com ==> Remote mysql server

webserver.example.com ==> Remote Apache webserver

Configuration on remote mysql.example.com server

接下来就是，下载该脚本工具，并放置在远程mysql服务器中的/root/rsnapshot.mysql中。

然后，设置好可执行权限即可。

操作步骤：

复制代码 代码示例:

# cd /root

# wget http://bash.cyberciti.biz/dl/408.sh.zip

# unzip 408.sh.zip

# mv 408.sh rsnapshot.mysql

# rm 408.sh.zip

# chmod +x rsnapshot.mysql

下面开始定义脚本相关的配置信息。

配置rsnapshot服务器。

找到snapshot的配置文件，查看snapshot_root：

复制代码 代码示例:

# grep snapshot_root /etc/rsnapshot.conf

输出：

复制代码 代码示例:

snapshot_root /.raiddisk/rsnapshots/

rsnapshot will store mysql backup at /.raiddisk/rsnapshots/tmp/ before moving to /.raiddisk/rsnapshots/hourly.0/mysql/ directory. Open your rsnapshot.conf file and add the following configuration:

### start db backup for remote server called mysql.example.com ###

# dump all databases at remote server itself

backup_script /usr/bin/ssh root@mysql.example.com "/root/rsnapshot.mysql" unused1/

# Copy all databases from remote server to local server and rsnapshot will move it to /.raiddisk/rsnapshots/$level/mysql/ directory (where, $level can be hourly, monthly etc).

backup_script /usr/bin/scp -r root@mysql.example.com:/tmp/rsnapshot/mysql/ /.raiddisk/rsnapshots/tmp/ mysql/

Here is sample configuration for both web and mysql server:

#

# Backup mysql.example.com

#

backup_script /usr/bin/ssh root@mysql.example.com "/root/rsnapshot.mysql" unused1/

backup_script /usr/bin/scp -r root@mysql.example.com:/tmp/rsnapshot/mysql/ /.raiddisk/rsnapshots/tmp/ mysql/

#

# Backup webserver.example.com

#

backup root@webserver.example.com:/etc/ webserver.example.com/

backup root@webserver.example.com:/var/www/ webserver.example.com/

backup root@webserver.example.com:/root/ webserver.example.com/

backup root@webserver.example.com:/var/spool/ webserver.example.com/

保存并关闭文件，然后测试以上的配置：

复制代码 代码示例:

# rsnapshot configtest

附上shell 脚本代码，如下：

复制代码 代码示例:

#!/bin/bash

# by www.jquerycn.cn

### SETUP MYSQL LOGIN ###

MUSER='YOUR-MySQL_USERNAME'

MPASS='YOUR-MySQL_PASSWORD'

MHOST="127.0.0.1"

### Set to 1 if you need to see progress while dumping dbs ###

VERBOSE=0

### Set bins path ###

GZIP=/bin/gzip

MYSQL=/usr/bin/mysql

MYSQLDUMP=/usr/bin/mysqldump

RM=/bin/rm

MKDIR=/bin/mkdir

MYSQLADMIN=/usr/bin/mysqladmin

GREP=/bin/grep

### Setup dump directory ###

BAKRSNROOT=/tmp/rsnapshot/mysql

#####################################

### ----[ No Editing below ]------###

#####################################

### Default time format ###

TIME_FORMAT='%H_%M_%S%P'

### Make a backup ###

backup_mysql_rsnapshot(){

local DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"

local db="";

[ ! -d $BAKRSNROOT ] && ${MKDIR} -p $BAKRSNROOT

${RM} -f $BAKRSNROOT/* >/dev/null 2>&1

[ $VERBOSE -eq 1 ] && echo "*** Dumping MySQL Database ***"

[ $VERBOSE -eq 1 ] && echo -n "Database> "

for db in $DBS

do

local tTime=$(date +"${TIME_FORMAT}")

local FILE="${BAKRSNROOT}/${db}.${tTime}.gz"

[ $VERBOSE -eq 1 ] && echo -n "$db.."

${MYSQLDUMP} -u ${MUSER} -h ${MHOST} -p${MPASS} $db | ${GZIP} -9 > $FILE

done

[ $VERBOSE -eq 1 ] && echo ""

[ $VERBOSE -eq 1 ] && echo "*** Backup done [ files wrote to $BAKRSNROOT] ***"

}

### Die on demand with message ###

die(){

echo "$@"

exit 999

}

### Make sure bins exists.. else die

verify_bins(){

[ ! -x $GZIP ] && die "File $GZIP does not exists. Make sure correct path is set in $0."

[ ! -x $MYSQL ] && die "File $MYSQL does not exists. Make sure correct path is set in $0."

[ ! -x $MYSQLDUMP ] && die "File $MYSQLDUMP does not exists. Make sure correct path is set in $0."

[ ! -x $RM ] && die "File $RM does not exists. Make sure correct path is set in $0."

[ ! -x $MKDIR ] && die "File $MKDIR does not exists. Make sure correct path is set in $0."

[ ! -x $MYSQLADMIN ] && die "File $MYSQLADMIN does not exists. Make sure correct path is set in $0."

[ ! -x $GREP ] && die "File $GREP does not exists. Make sure correct path is set in $0."

}

### Make sure we can connect to server ... else die

verify_mysql_connection(){

$MYSQLADMIN  -u $MUSER -h $MHOST -p$MPASS ping | $GREP 'alive'>/dev/null

[ $? -eq 0 ] || die "Error: Cannot connect to MySQL Server. Make sure username and password are set correctly in $0"

}

### main ####

verify_bins

verify_mysql_connection

backup_mysql_rsnapshot