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

踩坑之路

已于 2022-08-08 15:09:57 修改

阅读量1k

点赞数

分类专栏： Shell 文章标签： mysql 数据库 hive

于 2022-08-08 13:42:34 首次发布

本文链接：https://blog.csdn.net/qq_15735767/article/details/126225819

版权

Shell 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

#!/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