#!/bin/bash #Date:2017/5/2 #Author:wangpengtai #Blog:http://wangpengtai.blog.51cto.com #At Sunday, we will backup the completed databases and the incresed binary log during Saturday to Sunday. #In other weekdays, we only backup the increaing binary log at that day! ################################ #the globle variables for MySQL# ################################ DB_USER='root' DB_PASSWORD='123456' DB_PORT='3306' BACKUPDIR='/tmp/mysqlbakup' BACKUPDIR_OLDER='/tmp/mysqlbakup_older' DB_PID='/data/mysql/log/mysqld.pid' DB_SOCK='/data/mysql/log/mysql.sock' LOG_DIR='/data/mysql/log' BACKUP_LOG='/tmp/mysqlbakup/backup.log' DB_BIN='/usr/local/mysql/bin' #time variables for completed backup FULL_BAKDAY='Sunday' TODAY=`date +%A` DATE=`date +%Y%m%d` ########################### #time variables for binlog# ########################### #liftcycle for saving binlog DELETE_OLDLOG_TIME=$(date "-d 14 day ago" +%Y%m%d%H%M%S) #The start time point to backup binlog, the usage of mysqlbinlog is --start-datetime, --stop-datetime, time format is %Y%m%d%H%M%S, eg:20170502171054, time zones is [start-datetime, stop-datetime) #The date to start backup binlog is yesterday at this very moment! START_BACKUPBINLOG_TIMEPOINT=$(date "-d 1 day ago" +"%Y-%m-%d %H:%M:%S")
#注意在my.cnf中配置binlog文件位置时需要使用绝对路径，一定想成好习惯，不要给别人挖坑！！ #####################举例######################## #[mysqld] #log_bin = /var/lib/mysql/mysql-bin #####################举例######################## BINLOG_INDEX='/data/mysql/log/mysql-bin.index' ############################################## #Judge the mysql process is running or not. # #mysql stop return 1, mysql running return 0.# ############################################## function DB_RUN(){ if test -a $DB_PID && test -a $DB_SOCK;then return 0
function OLDER_BACKDIR_EXSIT(){ if test -d $BACKUPDIR_OLDER;then # echo "$BACKUPDIR_OLDER was exist." return 0 else echo "$BACKUPDIR_OLDER is not exist, now create it." mkdir -pv $BACKUPDIR_OLDER # return 1 fi } function BAKUP_CLEANER(){ #move the backuped file that created time out of 7 days to the BACKUPDIR_OLDER directory returnkey=`find $BACKUPDIR -name "*.sql.gz" -mtime +7 -exec ls -lh {} /;` returnkey_old=`find $BACKUPDIR_OLDER -name "*.sql.gz" -mtime +14 -exec ls -lh {} /;` if [[ $returnkey != '' ]];then
echo "----------------------" echo "Moving the older backuped file out of 7 days to $BACKUPDIR_OLDER." echo "The moved file list is:" find $BACKUPDIR -name "*.sql.gz" -mtime +7 -exec mv {} $BACKUPDIR_OLDER /; echo "-----------------------" elif [[ $returnkey_old != '' ]];then #delete the backuped file that created time out of 14 days from BACKUPDIR_OLDER directory. echo "Delete the older backuped file out of 14 days from $BACKUPDIR_OLDER." echo "The deleted files list is:" find $BACKUPDIR_OLDER -name "*.sql.gz" -mtime +14 -exec rm -fr {} /;
三、测试方法：
使用了一个测试脚本，修改日期，达到一个月的演示效果。
#!/bin/bash for day in {1..30} do date -s "2017-06-$day 12:00:00" /bin/bash /root/bakup/mysql_backup.sh done 四、脚本使用方法：
