mysql分库备份脚本_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."