这是我最近维护的线上数据库的备份脚本，断断续续code了不少。
就目前来看效果很不错，当然主要目的是抛(rang)砖(wo)引(zhuang)玉(bi)。

backup.sh
点击下载
backup.sh 会调用对应的配置文件mysql-cluster-list，public.conf，backuptime文件，调度多个备份脚本执行，并统计结果

#!/bin/bash
#Author		weskiller	2016-04-08	
#Email:weskiller@vip.qq.com

#定义环境变量
export PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
export LANG="en_US.UTF-8"

#定义路径
basedir=/root/scripts/backup
log=$basedir/backup.log
date=`date +%F`
starttime_conf=$basedir/backuptime
mysql_cluster_conf=$basedir/mysql-cluster-list

#superadmin邮件地址,当脚本文件缺失的时候,会发送邮件
Semail='weskiller@vip.qq.com'

#下标变量
declare -i sub=0

#设置循环检查的间隔时间
check_sleep=60

#设置备份超时时间(每天下午6点,用户高峰前期)
deadtime=`date --date "18:00" +%s`

#获取mysql-cluster的列表
mysql_cluster_list=(192.168.1.1)

#或者通过远程的web页面获取列表
#cluster_curl=http://192.168.1.1/mysql_cluster.txt
#for list in `curl "$cluster_curl"`;do
#	mysql_cluster_list[$sub]=$list;
#done

#标记日志备份开始
echo "" >> $log
echo "$date backup.sh start" >> $log

#加载公有配置
if [ -f "$basedir/config/public.conf" ];then
	. $basedir/config/public.conf
else
	/bin/mail -r "backup.sh" -s "backup.sh scripts is interrupt" "$Semail" <<< "`date +%F\ %T` - backup scripts is not performing! the public.conf is miss."
	echo "`date +%T` - backup scripts is not performing! the public.conf is miss." >> $log
	exit 1
fi

#检查开始备份时间配置文件
if [ ! -f "$starttime_conf" ];then
	sendemail "backup.sh scripts is interrupt" "`date +%F\ %T` - backup scripts is not performing! can't find configuration with ${starttime_conf}."
	echo "`date +%T` - backup scripts is not performing! can't find configuration with ${starttime_conf}." >> $log
	exit 1
fi

#加载配置文件mysql_cluster_conf,如果存在的话
if [ -f "$mysql_cluster_conf" ];then
	mysql_cluster_list=(`cat "$mysql_cluster_conf"`)
fi

#检测后端mysql_cluster
declare -i change=0
for i in `seq 0 $((${#mysql_cluster_list[@]}-1))`;do
	nc -z ${mysql_cluster_list[$i]} 3306 >/dev/null 2>&1
	if [ $? -eq 0 ];then
		continue
	else
		echo "detected ${mysql_cluster_list[$i]} is down. will be remove from mysql cluster." >> $log
		unset mysql_cluster_list[$i]
		change=1
	fi 
done

#重新定义有效的mysql_cluster_list
if [ $change -eq 1 ];then
	mysql_cluster_list=(${mysql_cluster_list[@]})
fi

#极端情况下后端集群全部宕机
if [ ${#mysql_cluster_list[@]} -eq 0 ];then
	sendemail "backup.sh scripts is interrupt" "`date +%F\ %T` - backup scripts is not performing! without living mysql cluster."
	echo "`date +%T` - backup scripts is not performing! without living mysql cluster." >> $log
	exit 1
fi

#手动备份
if [ "x$1" == "x" ];then
	manual=0
elif [ "$1" == "manual" ];then
	manual=1
	echo "backup.sh is start with manual." >> $log
fi

#重定向所有标准错误信息
#exec 2>$basedir/backup.error

#备份循环
sub=0
for project in $basedir/config/*-*;do
	script=`basename $project`
	project_name=${script%-*}
#默认模式下,根据设定的时间备份
	if [ "$manual" -eq 0 ];then
#获取配置时间
		project_start_time="`awk 'BEGIN{FS="="}/'$script'/{print $2}' $starttime_conf`" 
		if [ $? -eq 0 ];then
			project_timestamp=`date --date "$project_start_time" +%s`
			if [ "$project_timestamp" -le "`date +%s`" ];then
				echo "`date +%F\ %T` - $script backuptime is error" >> $log
				continue
			fi
		else
			echo "`date +%F\ %T` - $script backuptime is not find " >> $log
			continue
		fi
		waittime="$((project_timestamp-`date +%s`))"
#手动备份,根据设定的时间延迟
	elif [ "$manual" -eq 1 ];then
		first=`awk 'BEGIN{FS="="}{print $2}' backuptime|grep -P "^[^ ]" |sort -n|head -1`
		lag=$((`date --date '1 minute' +%s`-`date --date "$first" +%s`))
		project_start_time="`awk 'BEGIN{FS="="}/'$script'/{print $2}' $starttime_conf`" 
                if [ $? -eq 0 ];then
                        project_timestamp=$((`date --date "$project_start_time" +%s`+$lag))
                else
                        echo "`date +%F\ %T` - $script backuptime is not find " >> $log
                        continue
                fi
                waittime="$((project_timestamp-`date +%s`))"
	fi
	host=${mysql_cluster_list[$((sub%${#mysql_cluster_list[@]}))]}
#使用screen,定时放入后台
	screen -S $project_name -d -m /bin/bash "$project" "$host" "$waittime"
#放入项目数组
	project_list[$sub]="$script"
	((sub++))
done

#监控今天的备份日志,当所有备份完成后发送邮件通知
line=`grep -Pn "$date backup.sh start" $log|sed -n '$p'|awk 'BEGIN{FS=":"}{print $1}'`
while sleep $check_sleep && [ ${#project_list[@]} -gt 0 ];do
	sub=0
	for i in ${project_list[@]};do
		if sed -n "$line,\$p" $log |grep -Pq "$i backup (success|failed)" ;then
			 unset project_list[$sub]
		fi  
		((sub++))
	done
	project_list=(${project_list[@]})
	if [ "`date +%s`" -ge "$deadtime" ];then
		sendemail "backup is overtime" "`date +%F\ %T` -- project:${project_list[*]} already overtime"
	fi
done

#判断是否有失败的项目
if sed -n "$line,\$p" $log |grep -Pq "backup failed";then
	backup_failed=1
else 
	backup_failed=0
fi

#标记日志备份结束
echo "$date backup.sh finish" >> $log

#统计失败项目邮件
if [ $backup_failed -eq 1 ];then
	sendemail "some project backup failed" "`date +%F\ %T` -- backup failed project:`sed -n $line,'$p' $log|grep -Po "\S*(?= backup failed)"|tr '\n' ' '`"
fi

#生成WEB网页
sed -n -e "$line,\$p" -e '1a<pre>' -e '$a</pre>' $log > /backup/backup.html

#日志整理
if [ `date --date '1 day' +%d` -eq 1 ];then
        .  $basedir/logrotate.sh
fi

#优雅的退出
exit 0

scripts
点击下载
scripts 是真正的备份执行脚本，加载public.conf获取公共配置信息，针对不同的线上业务和数据库可以自定义操作，或者根据需求修改，这里只是提供模板。

#!/bin/bash
#author weskiller 2016-04-08
#description:project backup scripts unit

#2015-05-16:新增隔天自动备份功能

#定义环境变量
export PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
export LANG="en_US.UTF-8"
#获取脚本包含的信息
dir=/root/scripts/backup
scripts=`basename $0`
backup_host=192.168.1.${scripts#*-}
game_label=${scripts%-*}

#定义各种需要的时间戳
month=`date +%m`
last_month=`date --date "1 months ago" +%m`
day=`date +%d`
yesterday=`date --date "1 day ago" +%d`

#定义数据库信息
game_database="p${game_label}_logs1"
game_backup_database="p${game_label}_logs_backup1"
today="$(date --date @$(($(date --date "`date +%F`" +%s)-3600*8)) +%F\ %T)"
delete_gamelog_time="$(date --date @$((`date --date "$today" +%s`+7*3600)) +%F\ %T)"
three_months="`date --date '4 month ago' +%m`"

#加载公共配置
if [ -f "$dir/config/public.conf" ];then
	. $dir/config/public.conf
else
	echo "`date +%F\ %T`:not find public.conf" > $dir/config/$scripts.error 
	exit 1; 
fi

#获取backup.sh传递的参数
if [ "x$1" == "x" ];then
	echo "`date +%F\ %T`:not received parameter:mysql backup host " >> $dir/log/$scripts.log
	exit 1
else
	db_host="$1"
fi
if [ "x$2" == "x$2" ];then
	sleep $2
	if [ $? -ne 0 ];then
	echo "`date +%F\ %T`:received sleep time parameter,but sleep error " >> $dir/log/$scripts.log
	exit 1
	fi
else
	echo "`date +%F\ %T`:not received parameter:sleep time(s) " >> $dir/log/$scripts.log
	exit 1
fi

#开始时间戳
start_time="`date +%s`"

#创建文件夹，如果不存在的话
/usr/bin/ssh root@$backup_host "mkdir -p $backupdir/$game_label/months/$month"

#创建xtrabakup命令
if [ "$day" -eq 1 ];then
	innobackup_command="/usr/bin/innobackupex --no-timestamp $backupdir/$game_label/months/$month/$day"
	/usr/bin/ssh root@$backup_host "rm -f $backupdir/$game_label/days"
	/usr/bin/ssh root@$backup_host "ln -sf $backupdir/$game_label/months/$month $backupdir/$game_label/days"
#删除三个月之前的备份
	delete_three_backup="rm -rf $backupdir/$game_label/months/$three_months"
else
#获取days下的目录,确定日期
	uday=$(ssh root@$backup_host "basename \`ls -d $backupdir/$game_label/days/*|grep -Pv "tar.bz2$"\` 2>/dev/null")
	if [ ${uday:-0} -eq $yesterday ];then
	innobackup_command="/usr/bin/innobackupex --incremental --incremental-basedir $backupdir/$game_label/days/$yesterday --no-timestamp  $backupdir/$game_label/days/$day ; result=\$? ; if [ \$result -ne 0 -a ! -e $backupdir/$game_label/days/$day/xtrabackup_info ];then rm -rf $backupdir/$game_label/days/$day;fi ;exit \$result"
	else
	innobackup_command="/usr/bin/innobackupex --incremental --incremental-basedir $backupdir/$game_label/days/$uday --no-timestamp  $backupdir/$game_label/days/$day ; result=\$? ; if [ \$result -ne 0 -a ! -e $backupdir/$game_label/days/$day/xtrabackup_info ];then rm -rf $backupdir/$game_label/days/$day;fi ;exit \$result"
	jump=1	
	fi
fi

#创建mysqldump命令
mysqldump_command="mysqldump --single-transaction --skip-add-locks --skip-lock-tables  --no-create-info --replace --where \"create_time < '$today'\" $game_database logs  |mysql -u$db_user -h$db_host -p$db_pass $game_backup_database"

#创建清理日志命令
mysql_command="mysql -e \"delete from $game_database.logs where create_time < '$delete_gamelog_time';\""

#压缩前一天的备份文件
if  [ "$day" -eq 1 ];then
	tar_command="tar -jcf $backupdir/$game_label/months/$last_month/$yesterday.tar.bz2 -C $backupdir/$game_label/months/$last_month $yesterday --remove-files"
else
	if [ ${uday:-0} -eq $yesterday ];then
		tar_command="tar -jcf $backupdir/$game_label/days/$yesterday.tar.bz2 -C $backupdir/$game_label/days $yesterday --remove-files"
	else
		tar_command="tar -jcf $backupdir/$game_label/days/${uday}.tar.bz2 -C $backupdir/$game_label/days $uday --remove-files"
	fi
fi
#日志标记开始
echo "" >> $dir/log/$scripts.log
echo "`date +%F\ %T` $scripts start" >> $dir/log/$scripts.log
#在远程备份客服端上执行命令
for command in "$innobackup_command" "$mysqldump_command" "$mysql_command" "$tar_command" "$delete_three_backup" ;do
#先判断变量内容,确保ssh命令正常执行
if [ -z "$command " ] ||  echo "$command"|grep -Pq "^\s*$" ;then
	continue;
fi
ssh root@$backup_host "$command" 2>$dir/log/$scripts.error 1>/dev/null
if [ $? -eq 0 ];then
	echo "`date +%F\ %T` -- \"$command\" performing success." >> $dir/log/$scripts.log
else
	echo "`date +%F\ %T` -- \"$command\" performing failed." >> $dir/log/$scripts.log
#	sendemail "$scripts" "`cat $dir/log/$scripts.error`"
	log "failed" "$scripts"
	exit 1
fi
done
#删除错误的输出
rm -rf $dir/log/$scripts.error
#标记日志结束
echo "`date +%F` $scripts finish" >> $dir/log/$scripts.log
#输出backup.log日志
if  [ ${jump:-0} -eq 1 ];then
	log "success" "$scripts" "$((`date +%s`-start_time))" "$uday $day"
else
	log "success" "$scripts" "$((`date +%s`-start_time))"
fi
#优雅的退出
exit 0

public.conf
点击下载
public.conf 公共配置文件，包含了日志输出和维护邮件组，mysql验证

#this is public config that all scripts need to be loaded
#定位绝对路径
backupdir=/backup
#备份验证
db_user=
db_pass=

#负责人E-Mail地址
adminmail=(weskiller@vip.qq.com admin@xx.com)
sendemail () {
	for mail in ${adminmail[@]};do
	/bin/mail -r "backup.sh" -s "$1" "$mail" <<< "$2"
	done
}

#输出日志函数
log () {
case $1 in
	success)
	if [ $3 -lt 60 ];then
		consume="${3}s"
	elif [ $3 -lt 3600 ];then
		consume="$(($3/60))m$(($3%60))s"
	else
		consume="$(($3/(60*60)))h$((($3%(60*60))/60))m$(($3%60))s"
	fi
	if [ "x$4" == "x" ];then
		echo "`date +%F\ %T` - $2 backup $1. used ${consume}." >> /root/scripts/backup/backup.log
	else
		echo "`date +%F\ %T` - $2 backup $1. used ${consume}. ${4% *} => ${4#* } " >> /root/scripts/backup/backup.log
	fi
	;;
	failed)
	echo "`date +%F\ %T` - $2 backup $1" >> /root/scripts/backup/backup.log
	;;
esac
}

backuptime
点击下载
backuptime 主要用于设定备份数据库的时间

#测试数据库
test-1=01:00
test-2=00:00

#线上数据库
produce-101=03:00
produce-102=03:30