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

#!/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

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‘