mysqldump 备份数据库脚本

mysqldump 备份数据库脚本

#!/bin/bash
#################################################
# 备份数据库脚本
#
#
# 参数解释：
# d 需要备份的数据库，一个
# u 用户名
# p 密码
# h 数据库地址
# k 保存备份个数，用于清理旧的备份，默认保留600个
# P 端口，默认3306
# D 备份地址 默认脚本同目录
# S 是否清理旧的备份，值为数字1和0，默认为1清理
# M 主从结构是否配置--master-data=2，值为数字1和0，默认为0不启用
# 调用示例./backup.db.script -dmydb -hmyhost -umyuser -pmypassword -D"/backup/database" -k672
#
#################################################

set -e
CURRENT_PATH=$(dirname $(readlink -f "$0") || (cd "$(dirname "$0")";pwd))

# clear
BACKUP_DB=""
BACKUP_USER=""
BACKUP_PASS=""
KEEP_COUNTS=""
BACKUP_HOSTPORT=""
BACKUP_HOSTNAME=""
BACKUP_DIR=""
BACKUP_MASTER_STATUS=0
CLEAR_HISTORY=1
##

while getopts "d:u:p:k:P:h:D:S:M" opt; do  
  case $opt in  
    d)
      echo "database:$OPTARG" 
   		BACKUP_DB=$OPTARG
      ;;  
    u)  
      echo "username:$OPTARG"
		BACKUP_USER=$OPTARG
      ;;  
    p)  
      echo "password:******"
		BACKUP_PASS=$OPTARG
      ;; 
    k)
	  echo "keep:$OPTARG"
		KEEP_COUNTS=$OPTARG
	  ;;
	P)
	  echo "port:$OPTARG"
		BACKUP_HOSTPORT=$OPTARG
	  ;;
	h)
	echo "host:$OPTARG"
		BACKUP_HOSTNAME=$OPTARG
	;;
    D)
	echo "backup dir:$OPTARG"
		BACKUP_DIR=$OPTARG
	;;
	S)
	echo "not clear history"
		CLEAR_HISTORY=0
	;;
	M)
	echo "backup master info"
		BACKUP_MASTER_STATUS=1
	;;
    \?)  
    echo "Example:"
	echo "`basename $0` -d[databasename] -u[username] -p[password] [-k][keepcounts] [-h][host] [-P][port] [-D][backupdir] [-S] [-M]"
	echo "-d database name"
	echo "-u username" 
	echo "-p password" 
	echo "-h host"
	echo "-P port"
	echo "-S not clear history"
	echo "-D backup base dir"
	echo "-M backup master info"
	echo "-k keep database max count,default is 600"
	exit 1
      ;;  
  esac  
done

#保存备份个数，备份600个数据
number=$KEEP_COUNTS
if [[ "x$number" = "x" ]];then
	number=600;
fi
if echo $number | grep -q '[^0-9]';then
	echo 'keep database history max days parameter is invalid,only positive number support'
	exit -1
fi
if [[ $number -eq 0 ]];then
	echo "invalid parameter -k: $number"
	exit -1
fi
#备份保存路径
backup_dir=$BACKUP_DIR
if [[ "x$BACKUP_DIR" = "x" ]];then
	backup_dir=$CURRENT_PATH
fi 
backup_dir=$backup_dir/$BACKUP_DB
#日期
dd=`date +%Y%m%d%H%M%S` 
#用户名
username=$BACKUP_USER
if [[ "x$username" = "x" ]];then
	echo 'Need parameter -u user'
	exit -1;
fi 
#密码
password=$BACKUP_PASS
if [[ "x$password" = "x" ]];then
	echo 'Need parameter -p password'
	exit -1;
fi 
# 地址
host=$BACKUP_HOSTNAME
if [[ "x$host" = "x" ]];then
	host=127.0.0.1
fi 
# 端口
port=$BACKUP_HOSTPORT
if [[ "x$port" = "x" ]];then
	port=3306
fi 
# 判断记录主从日志
if [ "$BACKUP_MASTER_STATUS"x == 1x ];then
      BACKUP_MASTER_STATUS="--master-data=2"
else
      BACKUP_MASTER_STATUS=""
fi
# 将要备份的数据库
database_name=$BACKUP_DB
# 如果文件夹不存在则创建
if [ ! -d $backup_dir ]; then     
    mkdir -p $backup_dir; 
fi 
# 执行备份命令
echo "mysqldump -u$username -p****** -h$host -P$port $BACKUP_MASTER_STATUS --single-transaction --default-character-set=utf8 --routines --flush-logs --databases $database_name> $backup_dir/$database_name-$dd.sql"
mysqldump -u$username -p$password -h$host -P$port $BACKUP_MASTER_STATUS --single-transaction --default-character-set=utf8 --routines --flush-logs --databases $database_name> $backup_dir/$database_name-$dd.sql
#压缩备份文件
gzip $backup_dir/$database_name-$dd.sql;
rm -f $backup_dir/$database_name-$dd.sql;

#写创建备份日志
echo "create $backup_dir/$database_name-$dd.dump" >> $backup_dir/log.txt

if [[ "x$CLEAR_HISTORY" = "x1" ]];then
	echo now clear database backup history.
	num=$(ls $backup_dir -lart|grep "$database_name-[0-9]*.sql"|awk '{print $9}'|grep "^$database_name-[0-9]*.sql.gz$"|wc -l)
	echo total backup number: $num,total keep: $number
	if [ $num -gt $number ];then
		diff=$(( $num - $number ))
		echo diff backup: $diff
		cir=0
		#删除最早生成的备份，只保留number数量的备份
		for i in $(ls $backup_dir -lart|grep "$database_name-[0-9]*.sql"|awk '{print $9}'|grep "^$database_name-[0-9]*.sql.gz$")  
		do   
			if [ $cir -lt $diff ];then
				# 删除
				echo remove backup file $backup_dir/$i
				rm -f $backup_dir/$i
				#写删除文件日志
	  			echo "delete $backup_dir/$i" >> $backup_dir/log.txt
			else
				echo no more backup files to delete
				break;
			fi
			cir=$(( $cir + 1 ))
		done 
	fi
else
	echo not clear database backup history.
fi
##
echo ==============  backup database $BACKUP_DB successfully.