MySQL5.7~8热备份

#!/bin/bash


WORKDIR=$(cd $(dirname $0); pwd)

DB_PROJECT=$1
DB_HOST=$2
DB_PORT=$3

TODAY=$(date +"%Y%m%d")
BACKUP_DIR="$WORKDIR/db_backup/$DB_PROJECT/$TODAY"
LOG_DIR="$WORKDIR/logs"
TEMP_DIR="$LOG_DIR/temp"
CLIENT_FILE="$WORKDIR/${4:-.my.cnf}"



# Check if MYSQL can be connected
function checkConnect() {
    echo $CLIENT_FILE $DB_HOST $DB_PORT
    mysqlshow --defaults-file=${CLIENT_FILE} --host=$DB_HOST --port=$DB_PORT
    return $?
}

# Send to Telegram
function sendToMessage() {
    TOKEN="you-telegram-token"
    ID="you-telegram-id"
    URL="https://api.telegram.org/bot$TOKEN/sendMessage"
    curl -s -X POST $URL -d chat_id=$ID -d text="$(echo -e "数据库项目: $1\n数据库地址: $2\n数据库名称: $3\n备份状态: $4\n备份时间: $5\n结束时间: $6\n备份耗时: $7s")"  > /dev/null 2>&1
}


# If unable to connect, send email
checkConnect
CHECK_DB_STATUS=$?
if [[ "$CHECK_DB_STATUS" -ne 0 ]];then
    # ##########################
    sendToMessage  $DB_PROJECT $DB_HOST - "Can't connect to MySQL server -_-" - - -
    # #########################
    exit 1
fi




# Create backup directory, log directory, temporary directory (if it does not exist)
[ -d $BACKUP_DIR ] || mkdir -p $BACKUP_DIR
#[ -d $LOG_DIR ] || mkdir -p $LOG_DIR
[ -d $TEMP_DIR ] || mkdir -p $TEMP_DIR


# Export the database using mysqldump
EXCLUDE_DB="Data|info|per|sys|recycle_bin"
DB_NAME=`mysqlshow --defaults-file=${CLIENT_FILE} --host=$DB_HOST --port=$DB_PORT  | grep  -Ev $EXCLUDE_DB | grep  -v + | awk '{print  $2}'`

for dbname in $DB_NAME;do
    
    # for Calculate the total backup time
    startTime=`date +%Y%m%d-%H:%M:%S`
    startTime_s=`date +%s`
    # -----------------------------------
    echo -e "------- $startTime  backup database: $dbname BEGIN ----------"


    # Current date as backup file name ==============================
    DATE=$(date +"%Y%m%d_%H%M%S")

    BACKUP_FILE="$BACKUP_DIR/${dbname}_$DATE.sql"
  
    TEMP_FILE=$TEMP_DIR/temp_${DB_HOST}_${dbname}_${DATE}.log
    #mysqldump --defaults-file=${CLIENT_FILE}  --host=$DB_HOST --port=$DB_PORT -R --triggers --single-transaction --quick --databases $dbname --log-error=$TEMP_FILE > $BACKUP_FILE
    mysqldump --defaults-file=${CLIENT_FILE}  --host=$DB_HOST --port=$DB_PORT --triggers --routines --single-transaction --set-gtid-purged=OFF --default-character-set=utf8mb4  --quick --databases $dbname --log-error=$TEMP_FILE > $BACKUP_FILE
    #mysqldump --defaults-file=${CLIENT_FILE}  --host=$DB_HOST --port=$DB_PORT --single-transaction --databases $dbname --log-error=$TEMP_FILE > $BACKUP_FILE

    # for Calculate the total backup time
    endTime=`date +%Y%m%d-%H:%M:%S`
    endTime_s=`date +%s`
    sumTime=$[ $endTime_s - $startTime_s ]
    # -------------------------------

    echo "TempFile: $TEMP_FILE"
    result=$(grep -Ei "access denied|error" $TEMP_FILE)
    # Check if backup is successful
    #if [[ $? -eq 0 ]];then
    if [[ "$result" == "" ]];then
       echo -e "`date +%Y-%m-%d\ %H:%M:%S` MySQL backup completed successfully. Backup saved to: $BACKUP_FILE COMPLETE"
       
       # ##########################
       sendToMessage  $DB_PROJECT $DB_HOST $dbname "SUCCESS ^_^" $startTime $endTime $sumTime
       # #########################
    else
       echo -e "`date +%Y-%m-%d\ %H:%M:%S` Error: MySQL $dbname backup failed."
       echo "so backup sql change file name  $BACKUP_FILE to ${BACKUP_FILE}_temp"
       mv  $BACKUP_FILE  ${BACKUP_FILE}_temp
       # ##########################
       sendToMessage  $DB_PROJECT $DB_HOST $dbname "FAILED -_-" $startTime $endTime $sumTime
       # #########################
    fi

    # ===============================================================

    #echo -e "------- `date +%Y-%m-%d\ %H:%M:%S`  backup database: $dbname END ----------"
    echo "$startTime ---> $endTime"  "Total: $sumTime seconds backup database: $dbname"

    sleep 3
done

如何使用

#!/bin/sh

# ########################################################
# Author: ck
# ClienFile: .my.cnf
# Example: sh db_back_templates.sh <项目英文标识> <MYSQL_HOST> <MYSQL_PORT> <存放的备份用户文件(默认寻找当前 .my.cnf)>
# ########################################################


workdir=$(cd $(dirname $0); pwd)
cd $workdir
[ -d "logs" ] || mkdir logs


# sh template_backup_db.sh <项目英文标识> <MYSQL_HOST> <MYSQL_PORT> 2>&1>> $workdir/logs/db_backup.log

用户密码格式

[root@localhost new_backup]# more .my.cnf 
[client]
user=you-backup-user
password=you-backup-pwd