备份mysq binlog shell

#!/bin/bash


#dateDIR=$(date -d "yesterday" +"%Y%m%d")
dateDIR=$(date +"%Y%m%d")
cd /home/database_backup/
#mkdir -p $dateDIR/YourDB/binlog


##刷新缓存中的日志
mysqladmin -uUSERNAME -pPASSWORD flush-logs


#7天前的时间
TIME=$(date "-d 7 day ago" +"%Y-%m-%d %H:%M:%S")


#24小时前的时间
StartTime=$(date "-d 1 day ago" +"%Y-%m-%d %H:%M:%S")


#删除7天前的2进制文件
mysql -uUSERNAME -pPASSWORD  -e "purge master logs before '${TIME}';"


#针对每个数据库
for db in $(mysql -uUSERNAME -pPASSWORD  -e "show databases" | grep -ve "Database" -ve "mysql" -ve "test" -ve "information_schema")
#针对特定数据库
#for db in $(mysql -uUSERNAME -pPASSWORD  -e "show databases" | grep -ve "Database" -ve "mysql" -ve "test" -ve "information_schema"|grep YourDB)
do
#针对所有的日志文件
for log in $(cat /var/lib/mysql/mysql_3306_bin.index)
do
#备份每个数据库24小时之前到现在时间的所有日志，写入到相应文件中
mkdir -p $dateDIR/${db}/binlog
mysqlbinlog -uUSERNAME -pPASSWORD  -d $db --start-datetime="$StartTime" ${log}>>$dateDIR/${db}/binlog/${dateDIR}.binlog
done
#tar jcvf /data/backup/mysql/binlog/$dateDIR/${db}_${dateDIR}.tar.bz2 /data/backup/mysql/binlog/$dateDIR/${db}_${dateDIR} 2&>/dev/null
#rm -rf /data/backup/mysql/binlog/$dateDIR/${db}_${dateDIR}
done
#删除7天以前的增量备份文件和目录
find /home/database_backup/* -mtime +7 -type d -exec rm -rf {} \;