MySQL自动化备份脚本_升级版

经过一段时间的测试,在上次脚本的基础上做了大量的修改优化,使之更为实用,欢迎大家测试,提出宝贵意见.

脚本仅供参考,不可直接粘贴使用,重要的还是分享思路.

脚本说明请看上一篇

http://blog.chinaunix.net/space.php?uid=16844903&do=blog&id=2010853

主要说明一下变更内容:

1,增加年月目录下面的日期目录,比如在201110目录下,会按日期生成22日的目录,该目录下,为当天的备份

2,增加f参数,可以自己定保存的天数,保留7天,则设置-f 7

3,删除了恢复数据的函数,恢复数据比较重要,需要单独编写脚本.

4,脚本文件与变量文件分开,便于脚本更新.

5,脚本名改为backup_mysql.sh

使用说明:

-a: backup all database 备份全库

-e: backup each database 备份所有分库

-d: backup single/multi database eg. -d 'mysql test' 备份单库,或者多个库

-t: backup single/multi table of single database eg. -t 'mysql user' 备份单库下面的表

-b: backup binlog 备份binlog

-f: delete backup 7 days ago! eg. -f 7 删除7天之前的备份,天数可自定义

-p: create connect mysql password 创建密码文件

-c: sync to backup center 传送到异机

脚本文件组成:

backup_mysql.pass传送用的密码文件

backup_mysql.sh主脚本

backup_mysql.var定义的变量

脚本内容:

#!/bin/bash

# script name: backup_mysql.sh
# version: 2011XX

set -e
set -u

#导入backup_mysql.var中的变量.
function prepare()
{
if [ -r 'backup_mysql.var' ]; then
. backup_mysql.var
echo ". backup_mysql.var is success!"
else
echo ". backup_mysql.var is fail!"
exit 0
fi

#检查and创建目录
if [ ! -d $DIR_BACKUP/$TIME_YM/$TIME_DAY ]; then
mkdir -p $DIR_BACKUP/$TIME_YM/$TIME_DAY
fi
cd $DIR_BACKUP/$TIME_YM/$TIME_DAY
}

#结果状态函数
function result_status()
{
echo "[`date +%Y%m%d%H%M%S`] SUCCESS!
$COMMAND"|tee -a log.$TIME_YM
}

#使用帮助and错误提示
function usage_error()
{
echo "Usage: $0 RUN ERROR"
echo "
-a: backup all database
-e: backup each database
-d: backup single/multi database eg. -d 'mysql test'
-t: backup single/multi table of single database eg. -t 'mysql user'
-b: backup binlog
-f: delete backup 7 days ago! eg. -f 7
-p: create connect mysql password
-c: sync to backup center
"
exit 0
}

#读取密码文件
function read_pwd()
{
read USER PASSWD < $FILE_PASSWD
}

#同步
#设置局部变量 FILE_NAME
function rsync_cmd()
{
if [ -z "$POS" ]; then
typeset FILE_NAME="$HOSTNAME.*.$TIME.sql.gz"
else
typeset FILE_NAME="$HOSTNAME.$POS.$TIME.tgz"
fi
cd $DIR_BACKUP
rsync -crptR --password-file=$RSYNC_PASSWD $TIME_YM/$TIME_DAY/$FILE_NAME backupdbuser@$BACKUPHOST/$HOSTNAME/
typeset COMMAND="rsync -crpt --password-file=$RSYNC_PASSWD $TIME_YM/$TIME_DAY/$FILE_NAME backupdbuser@$BACKUPHOST/$HOSTNAME/"
result_status
cd -
}

#基础备份函数
function backup()
{
read_pwd
LOGBIN_STATUS=`$CMD_MYSQL -u$USER -p$PASSWD -N -s -e "SHOW VARIABLES LIKE 'log_bin'" | gawk '{print $2}'`
if [ $LOGBIN_STATUS = "ON" ]; then
MASTER='--master-data=2'
else
MASTER=' '
fi

#mysqldump部分命令+参数
CMD_PART="$CMD_MYSQLDUMP -u$USER -p$PASSWD -x -R $MASTER --socket=$SOCKET --default-character-set=utf8"
}


#调用backup函数,备份
function backup_all()
{
backup
$CMD_PART -A --add-drop-database |gzip >$HOSTNAME.all.$TIME.sql.gz
}

function backup_each()
{
backup
for db in $($CMD_MYSQL -u$USER -p$PASSWD -N -s -e "SHOW DATABASES"|egrep -v $LIST_EXCLUDE_DB)
do
$CMD_PART $db --databases |gzip >$HOSTNAME.$db.$TIME.sql.gz
#  sleep 20
done
}

function backup_db()
{
backup
$CMD_PART --databases $OPTARG | gzip > "$HOSTNAME.`echo "$OPTARG"|gawk '{print $1$2}'`.$TIME.sql.gz"
}

function backup_dt()
{
backup
$CMD_PART $OPTARG | gzip > "$HOSTNAME.`echo "$OPTARG"|gawk '{print $1$2}'`.$TIME.sql.gz"
}

function backup_binlog()
{
if [ -s $DIR_BACKUP/mysql-bin.queue ]; then
read POS < $DIR_BACKUP/mysql-bin.queue
cd $DIR_DATA
tar -zcvf $DIR_BACKUP/$TIME_YM/$TIME_DAY/$HOSTNAME.$POS.$TIME.tgz `gawk -F'/' '{print $NF}' $BINLOG_NAME.index |sed -n "/$POS/,//p"`
cd -
fi
# write last pos
gawk -F'/' '{print $NF}' $DIR_DATA/$BINLOG_NAME.index | tail -n 1 >$DIR_BACKUP/mysql-bin.queue
}

#删除7天之前的备份,如果目录为空,则会删除目录
function backup_delete()
{
cd $DIR_BACKUP
find -mtime +$OPTARG -name "$HOSTNAME.*gz"|xargs -i rm {} -f
typeset TIME_YM=`date -d '2 month ago' +%Y%m`
if [ -d $TIME_YM ]; then
rm $TIME_YM -rf
fi
cd -
}

function passwd_create()
{
if [ ! -e "$DIR_PASSWD" ]; then
mkdir -p $DIR_PASSWD
fi
echo -n "Please enter MySQL(user=root)'s password:"
read -s MYSQL_FASSWD
cat >$FILE_PASSWD <<+
root $MYSQL_FASSWD
+
chmod 600 $FILE_PASSWD
}

#main
if [ $# -eq 0 ]; then
usage_error
else
prepare
while getopts :aed:t:bf:pc varname
do
case $varname in
a)
backup_all
;;
e)
backup_each
;;
d)
backup_db
;;
t)
backup_dt
;;
b)
backup_binlog
;;
f)
backup_delete
;;
p)
passwd_create
;;
c)
rsync_cmd
;;
:)
echo "$varname: 缺少参数"
usage_error
;;
\?)
echo "$varname: 非法选项"
usage_error
;;
esac
done
fi

变量(backup_mysql.var)格是如下:

TIME=`date +%Y%m%d%H%M%S`
TIME_7=`date -d '7 days ago' +%Y%m%d%H%M%S`
TIME_YM=`date +%Y%m`
TIME_DAY=`date +%d`
DIR_MYSQL='/usr/local/mysql'
DIR_BACKUP="/backupdb"
DIR_DATA="$DIR_MYSQL/data"
DIR_PASSWD="$DIR_MYSQL/etc"
FILE_PASSWD="$DIR_PASSWD/passwordfile"
BINLOG_NAME='mysql-bin'
CMD_MYSQLDUMP="$DIR_MYSQL/bin/mysqldump"
CMD_MYSQL="$DIR_MYSQL/bin/mysql"
LIST_EXCLUDE_DB='(test|information_schema|performance_schema)'
SOCKET='/usr/local/mysql/data/mysql.sock'
BACKUPHOST='192.168.250.251::DUMPDB'
RSYNC_PASSWD='backup_mysql.pass'
POS=''