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."