#!/bin/bash
# script name: backup_mysql.sh
# version: 2011XX
# www.jb200.com
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'" | g
linuxjishu/13830.html target=_blank class=infotextkey>awk '{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
