MYSQL 自动备份脚本

1.备份脚本

cat /usr/local/script/BackupDatabase  #!/bin/bash      #Shell Command For Backup MySQL Database Everyday Automatically By Crontab      #time 2015-5-20      #name huxianglin     USER=root     PASSWORD=xxxxxxxx     DATABASE1=zblog     DATABASE2=zabbix     BACKUP_DIR=/data/backup/database/  #备份数据库文件的路径     LOGFILE=/data/backup/database/data_backup.log    #备份数据库脚本的日志文件     DATE=`date +%Y%m%d-%H%M -d -3minute`     #获取当前系统时间-3分钟     DUMPFILE1=$DATE-zblog.sql                #需要备份的数据库名称     DUMPFILE2=$DATE-zabbix.sql     ARCHIVE1=$DUMPFILE1-tar.gz                #备份的数据库压缩后的名称     ARCHIVE2=$DUMPFILE2-tar.gz     if [ ! -d $BACKUP_DIR ];                 #判断备份路径是否存在，若不存在则创建该路径     then      mkdir -p "$BACKUP_DIR"     fi      echo -e "\n" >> $LOGFILE       echo "------------------------------------" >> $LOGFILE      echo "BACKUP DATE:$DATE">> $LOGFILE      echo "------------------------------------" >> $LOGFILE      cd $BACKUP_DIR                           #跳到备份路径下     /usr/local/mysql/bin/mysqldump -u$USER -p$PASSWORD $DATABASE1 > $DUMPFILE1    #使用mysqldump备份数据库     if [[ $? == 0 ]]; then     tar czvf $ARCHIVE1 $DUMPFILE1 >> $LOGFILE 2>&1                               #判断是否备份成功，若备份成功，则压缩备份数据库，否则将错误日志写入日志文件中去。     echo "$ARCHIVE1 BACKUP SUCCESSFUL!" >> $LOGFILE      rm -f $DUMPFILE1     else      echo “$ARCHIVE1 Backup Fail!” >> $LOGFILE      fi     /usr/local/mysql/bin/mysqldump -u$USER -p$PASSWORD $DATABASE2 > $DUMPFILE2     if [[ $? == 0 ]]; then     tar czvf $ARCHIVE2 $DUMPFILE2 >> $LOGFILE 2>&1     echo "$ARCHIVE2 BACKUP SUCCESSFUL!" >> $LOGFILE     rm -f $DUMPFILE2     else     echo “$ARCHIVE2 Backup Fail!” >> $LOGFILE     fi 

2.删除过时备份脚本

#!/bin/bash #time 2015-05-21 #name huxianglin BACKUPDIR="/data/backup/database/"                                     #定义备份文件路径 KEEPTIME=7                                                             #定义需要删除的文件距离当前的天数 DELFILE=`find $BACKUPDIR -type f -mtime +$KEEPTIME -exec ls {} \;`     #找到天数大于7天的文件 for delfile in ${DELFILE}                                              #循环删除满足天数大于七天的文件 do rm -f $delfile done 

3.crontab 定时执行脚本

at /etc/crontab  SHELL=/bin/bash PATH=/sbin:/bin:/usr/sbin:/usr/bin MAILTO=root # For details see man 4 crontabs # Example of job definition: # .---------------- minute (0 - 59) # |  .------------- hour (0 - 23) # |  |  .---------- day of month (1 - 31) # |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ... # |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat # |  |  |  |  | # *  *  *  *  * user-name  command to be executed   01 00 * * * root /usr/local/script/BackupDatabase              #定义每天凌晨0点01分执行备份数据库脚本   02 00 * * 0 root /usr/local/script/CleanDatabase               #定义每周日凌晨0点02分执行删除数据库备份文件