mysql数据库定时备份（保留近7天的备份）

#!/bin/bash
# File: mysql_backup.sh
# Usage:  bash mysql_backup.sh
# Description: Backup mysql all databases. 排除以下数据库：information_schema|binlogs|mysql|test|Database|performance_schema|hive
# CRONTAB: Backup data every 23:00
# 00 23 * * * bash /data/app/mysql/scripts/mysql_backup.sh

BACKUP_DIR="/data/appData/backup/mysql"
MYSQL_BIN="/data/app/mysql/bin/mysql"
MYSQL_DUMP="/data/app/mysql/bin/mysqldump"
MYSQL_IP="127.0.0.1"
MYSQL_PORT=3306
MYSQL_USER="用户名"
MYSQL_PWD="密码"
CLEAR_INTERVAL=7

if [[ ! -d $BACKUP_DIR ]]; then
    mkdir -p $BACKUP_DIR
fi

if [ "x$MYSQL_PWD" == "x" ]; then
    $MYSQL_BIN -P$MYSQL_PORT -u$MYSQL_USER -h$MYSQL_IP -e 'exit' >/dev/null 2>&1
else
        $MYSQL_BIN -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PWD -h$MYSQL_IP -e 'exit' >/dev/null 2>&1
fi
if [ $? -ne 0 ]; then
    echo "Error: MySQL User and Password Error."
    exit 1
fi

nowTime=$(date +%Y%m%d%H%M)
if [ "x$MYSQL_PWD" == "x" ]; then
    databases=$($MYSQL_BIN -u$MYSQL_USER -h$MYSQL_IP -P$MYSQL_PORT -e 'show databases;' 2>/dev/null | egrep -v 'information_schema|binlogs|mysql|test|Database|performance_schema|hive')
else
    databases=$($MYSQL_BIN -u$MYSQL_USER -p$MYSQL_PWD -h$MYSQL_IP -P$MYSQL_PORT -e 'show databases;' 2>/dev/null | egrep -v 'information_schema|binlogs|mysql|Database|performance_schema|hive')
fi

for dataName in $databases; do
    if [ "x$MYSQL_PWD" == "x" ]; then
        $MYSQL_DUMP --single-transaction -P$MYSQL_PORT -u$MYSQL_USER -h$MYSQL_IP -a --default-character-set=utf8 --skip-comments $dataName 2>/dev/null | gzip -n >$BACKUP_DIR/$dataName-$nowTime.sql.gz
    else
        $MYSQL_DUMP --single-transaction -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PWD -h$MYSQL_IP -a --default-character-set=utf8 --skip-comments $dataName 2>/dev/null | gzip -n >$BACKUP_DIR/$dataName-$nowTime.sql.gz
    fi
done

find $BACKUP_DIR -type f -name *.sql.gz -mtime ${CLEAR_INTERVAL} | xargs rm -rf