线上MySQL备份脚本

最近在研究数据库备份，定时执行备份任务，这里直接把备份脚本设置为crontab命令定时执行，脚本内容如下:

[root@mysql-db ~]# cat

#!/bin/bash

dbpwd='possw0rd123'

dbuser=root

host=127.0.0.1

port=3306

dbarg=" -u$dbuser -p$dbpwd -h$host -P$port --default-character=utf8 "

DATE=`date +%Y%m%d`

BACKUP_PATH=/DB_Backup/backup/DB_BACKUP

LOG=$BACKUP_PATH/$DATE/backup.log

keep_backup_days=7

mkdir -p $BACKUP_PATH/$DATE/$port

for db in `echo show databases|mysql $dbarg|grep -v "information_schema\|test\|mysql\|Database" ` 

do 

    echo -e "$DATE `date +%T` DUMP $port $db  .. \c " >>$LOG

    mysqldump -B $dbarg -R $db |gzip >$BACKUP_PATH/$DATE/$port/${db}_${port}.sql.gz

    echo -e "`date +%T` OK " >>$LOG

done

for dir in `ls $BACKUP_PATH|grep [0-9]$`

do

    if [ `ls -l $BACKUP_PATH|grep [0-9]$|wc -l` -gt 2 ];then

        find ${BACKUP_PATH}/$dir -name "*.sql.gz*"  -ctime +${keep_backup_days}|xargs rm -f

        find ${BACKUP_PATH}/$dir -name backup.log  -ctime +${keep_backup_days}|xargs rm -f

        find ${BACKUP_PATH}/$dir -type d -empty |xargs rm -rf

#清除空目录

        find ${BACKUP_PATH}/$dir -type d -empty |xargs rm -rf

    fi

done

[root@mysql-db ~]# crontab -l

1 4 * * * sh /DB-Backup/shell/DB_backup.sh