Automatically back up SQL scripts

sqlbackup.sh

#!/usr/bin/env bash
# @auther <james@lightbeijing.com>
set -x
shopt -s extglob

BACKUP_HOST=''
BACKUP_USER=''
BACKUP_PASSWORD=''
BACKUP_PORT=3306
MAIL_ADDR=('james@lightbeijing.com')
# Back up the root directory
BACKUP_DIR='/data/mysql_data/master_mysql-3306/data/db'
# Backup the database list, all for the entire library export
BACKUP_DATABASES=('all' 'dev')
# Sql backup level d(every day) w(every week) m(every month)
BACKUP_LEVEL='d'
# Daily days of sql retention days
BACKUP_RESERVED_D=7
# Monthly backup of sql retention days
BACKUP_RESERVED_W=30
# The number of days to keep the sql
BACKUP_RESERVED_M=360
# Backup file prefix
BACKUP_FILE_PREFIX='backupsql_'
# he current year and the next day
CURRENT_TIME=$(date +%Y%m%d-%H%M%S)
# The timestamp of the current date
TIMESTAMP=$(date -d $(date  +"%Y-%m-%d") +%s)

# One day timestamp
let DAY_TIMESTAMP=60*60*24
# One week timestamp
let WEEK_TIMESTAMP=${DAY_TIMESTAMP}*7
# One month timestamp
let MONTH_TIMESTAMP=${WEEK_TIMESTAMP}*4
# Delete the timestamp of the daily backup
let RESERVED_D_TIMESTAMP=${TIMESTAMP}-${BACKUP_RESERVED_D}*${DAY_TIMESTAMP}
# Delete the timestamp of the monthly backup
let RESERVED_W_TIMESTAMP=${TIMESTAMP}-${BACKUP_RESERVED_W}*${DAY_TIMESTAMP}
# Delete the timestamp of the annual backup
let RESERVED_M_TIMESTAMP=${TIMESTAMP}-${BACKUP_RESERVED_M}*${DAY_TIMESTAMP}

# Create the root directory
if [ ! -d $BACKUP_DIR ]
then
	echo "info: mkdir -p $BACKUP_DIR"
        mkdir -p $BACKUP_DIR
fi

# Create a directory based on the root directory based on the parameters
_mkBackupDir () 
{
	BACKUP_MKDIR="${BACKUP_DIR}/$1"
	if [ ! -d  $BACKUP_MKDIR ]
	then
		echo "info: mkdir -p ${BACKUP_MKDIR}"
		mkdir -p $BACKUP_MKDIR
	fi
	return 0
}

# The initial time of the embedded operation
_get_TIMESTAMP_EMBEDDING ()
{
	if [ ! -e ${BACKUP_TMP_DIR}/timestampembedding ]
	then
		echo "info: Timestamp embedding"
		echo $TIMESTAMP > ${BACKUP_TMP_DIR}/timestampembedding
	fi
	TIMESTAMP_EMBEDDING=$(cat ${BACKUP_TMP_DIR}/timestampembedding)
}

# Use mysqldump and gzip to package backup sql files
_dumpsql ()
{
	_mkBackupDir $1/$2 && BACKUP_DATABASE_DIR=$BACKUP_MKDIR

	BACKUP_FILE_SQL=${BACKUP_FILE_PREFIX}$1_$2_${CURRENT_TIME}_${TIMESTAMP}.sql.tar.gz

	if [[ $2 == 'all' ]]
	then
		local DATABASE_NAME='--all-databases'	
	else
		local DATABASE_NAME=$2
	fi
        echo "Package export sql file : ${BACKUP_DATABASE_DIR}/${BACKUP_FILE_SQL}" >> $LOG_FILE
        mysqldump -u${BACKUP_USER} -h${BACKUP_HOST} -P${BACKUP_PORT} -p${BACKUP_PASSWORD} ${DATABASE_NAME} | gzip > ${BACKUP_DATABASE_DIR}/${BACKUP_FILE_SQL}

	return 0
}

# Get the timestamp of the file
_getFileTimestamp ()
{
	local filefead=${1##*_}
	filetimestamp=${filefead%%.*}
}

# Get the maximum timestamp in the file in the directory, and clear the expired file
_getMaxTimestampAndClearOldFile () 
{
	local files=$(ls $1)
	_getFileTimestamp ${files['0']} && maxtimestamp=${filetimestamp}
	case $2 in
	'd')
		local RESERVED_TIMESTAMP=$RESERVED_D_TIMESTAMP
	;;
	'w')
		local RESERVED_TIMESTAMP=$RESERVED_W_TIMESTAMP
	;;
	'm')
		local RESERVED_TIMESTAMP=$RESERVED_M_TIMESTAMP
	;;
	esac

	for file in $files
	do
		_getFileTimestamp $file
		if [[ $filetimestamp > $maxtimestamp ]]
		then
			maxtimestamp=$filetimestamp
		fi
		if [[ $filetimestamp < $RESERVED_TIMESTAMP ]]
		then
			echo "info: rm -rf ${file}"
			rm -rf ${file}
		fi		
	done

	return 0
}

_init () 
{
	for database in ${BACKUP_DATABASES[@]}
	do
		echo " " >> $LOG_FILE
		echo "${database}:" >> $LOG_FILE
		echo "--------------------------------------------------------------" >> $LOG_FILE

		if [[ $BACKUP_LEVEL == 'd' ]]
		then
			echo "Every Day ：" >> $LOG_FILE
			_getMaxTimestampAndClearOldFile ${BACKUP_DAY_DIR}/$database d
			_dumpsql day $database
		fi
		
		let DIFF_TIMESTAMP=${TIMESTAMP}-${TIMESTAMP_EMBEDDING}
		let DIFF_WEEK=${DIFF_TIMESTAMP}/${WEEK_TIMESTAMP}
		let DIFF_MONTH=${DIFF_TIMESTAMP}/${MONTH_TIMESTAMP}
		 
		if [[ $DIFF_TIMESTAMP == 0 ]]
		then
			if [[ ! -d ${BACKUP_WEEK_DIR}/$database ]]
			then
				echo "Every Week ：" >> $LOG_FILE
				_dumpsql week $database
			fi

			if [[ ! -d ${BACKUP_MONTH_DIR}/$database ]]
			then
				echo "Every Month ：" >> $LOG_FILE
				_dumpsql month $database
			fi
		fi
		
		if [[ $DIFF_WEEK != 0 && $BACKUP_LEVEL != 'm' ]]
		then
			_getMaxTimestampAndClearOldFile ${BACKUP_WEEK_DIR}/$database w
			let MAX_DIFF_TIMESTAMP=${maxtimestamp}-${TIMESTAMP_EMBEDDING}
			let MAX_DIFF_WEEK=${MAX_DIFF_TIMESTAMP}/${WEEK_TIMESTAMP}
			if [[ $DIFF_WEEK > $MAX_DIFF_WEEK ]]
			then
				echo "Every Week ：" >> $LOG_FILE
				_dumpsql week $database
			fi
		fi

		if [[ $DIFF_MONTH != 0 ]]
		then
			_getMaxTimestampAndClearOldFile ${BACKUP_MONTH_DIR}/$database m
			let MAX_DIFF_TIMESTAMP=${maxtimestamp}-${TIMESTAMP_EMBEDDING}
			let MAX_DIFF_MONTH=${MAX_DIFF_TIMESTAMP}/${MONTH_TIMESTAMP}
			if [[ $DIFF_MONTH > $MAX_DIFF_MONTH ]]
			then
				echo "Every Month ：" >> $LOG_FILE
				_dumpsql month $database
			fi
		fi
	done

	return 0
}

# Get the tmp path
_mkBackupDir tmp && BACKUP_TMP_DIR=${BACKUP_MKDIR}
# Get the initial buried time point
_get_TIMESTAMP_EMBEDDING

LOG_FILE=${BACKUP_TMP_DIR}/${BACKUP_FILE_PREFIX}_${TIMESTAMP}.log
echo "为IP是${BACKUP_HOST}端口为${BACKUP_PORT}的mysql服务器进行备份" >> $LOG_FILE

_mkBackupDir day && BACKUP_DAY_DIR=${BACKUP_MKDIR}
_mkBackupDir week && BACKUP_WEEK_DIR=${BACKUP_MKDIR}
_mkBackupDir month && BACKUP_MONTH_DIR=${BACKUP_MKDIR}

# Start the backup
_init

for email in $MAIL_ADDR
do
	cat $LOG_FILE | mutt -s 'MySQL Backup Log and SQL Files for '`hostname`" - `date`"  $email
done