mysql 分庫備份腳本

#!/bin/bash
#Author:heyic.m@gmail.com
#按數據庫分庫備份，可排除指定庫
#2016-06-08 create.
#2017-02-21 add expire_days.
#2017-02-27 redifine backupPath's name style.

#備份用戶與密碼
mysql_user="root"
mysql_pwd="pass"
mysql_host="localhost"
cmd_mysql="/data/apps/mysql/bin/mysql"
cmd_mysqldump="/data/apps/mysql/bin/mysqldump"
#備份目錄
#backuppath="/data/backup/mysql/`date +%F`/"
backup_basedir="/data/backup/mysql"
day=`date +%F`
#備份保存時間
backuppath="${backup_basedir}/${day}"
expire_days=30


if [ ! -d $backuppath ];then
	mkdir $backuppath -p
fi

bk_time=`date +%y%m%d%H%M%S`
#排除不備份的數據庫，多個庫用'|'隔開
exclude_db="mysql|test|information_schema|performance_schema"

#備份功能
if [ -z $mysql_pwd ];then
	if [ -z $exclude_db ];then
		backup_db=`$cmd_mysql -u$mysql_user  -h$mysql_host -e "show databases;"|grep -vi database` 
	else
		backup_db=`$cmd_mysql -u$mysql_user  -h$mysql_host -e "show databases;"|grep -vi database |grep -Ev $exclude_db`
	fi
else
	if [ -z $exclude_db ];then
		backup_db=`$cmd_mysql -u$mysql_user -p$mysql_pwd -h$mysql_host -e "show databases;"|grep -vi database` 
	else
		backup_db=`$cmd_mysql -u$mysql_user -p$mysql_pwd -h$mysql_host -e "show databases;"|grep -vi database |grep -Ev $exclude_db`
	fi
fi

for db in $backup_db;do
	if [ -z $mysql_pwd ];then
		echo "`date` Is backuping $db,please wait..."
		$cmd_mysqldump -u$mysql_user -h$mysql_host \
					--single-transaction \
					--hex-blob \
					--events \
					--routines \
					--triggers \
					--master-data=2 \
					--set-gtid-purged=off \
					--databases \
					$db | gzip -9 > $backuppath/$db.$bk_time.sql.gz
	else
		echo "`date` Is backuping $db,please wait..."
		$cmd_mysqldump -u$mysql_user -p$mysql_pwd -h$mysql_host \
											--single-transaction \
											--hex-blob \
											--events \
											--routines \
											--triggers \
											--master-data=2 \
											--set-gtid-purged=off \
					--databases \
					$db | gzip -9 > $backuppath/$db.$bk_time.sql.gz
	fi
done

echo "`date` The backup is completed."

#清除過期備份
#獲取備份目錄路徑
#截取長度(res_len)=總長度(total_len)-12(/2017-02-21/) 
#${backuppath:0:$res_len}
#total_len=${#backuppath}
#res_len=$[ $total_len - 12 ]
#clean_path=${backuppath:0:$res_len}
clean_path=${backup_basedir}
#echo $clean_path
#清除
echo "start to remove backupfiles of more than $expire_days days."
find $clean_path -mtime +$expire_days | xargs rm -rfv
echo "done."