Shell脚本一键备份MySQL数据库

在我公司原来的备份脚本做的优化改动给大家分享下

使用时注意变量 ，配合cron执行脚本实现备份效果

#!/bin/bash

export PATH=/bin:/usr/bin:/usr/local/bin

TODAY=$(date +%Y-%m-%d)

date=$(date +%s)

echo "Backup date: $TODAY"

################## 这块根据自己情况修改 ########################
#备份路径
DB_BACKUP_PATH='/home/backup/'
#数据库地址
MYSQL_HOST='localhost'
#端口
MYSQL_PORT='3306'
#数据库用户
MYSQL_USER='root'
#数据库密码
MYSQL_PASSWORD='123456'

# 需要备份的库
DATABASES=( "data" "manage" "base" "engine" "cloud" "nacos")
#删除大于多少天的备份
BACKUP_RETAIN_DAYS=25

#################################################################

mkdir -p "$DB_BACKUP_PATH/$TODAY"
echo "Backup directory creation done. Start backup."

# 这里注意自己mysqldump的路径
for DATABASE_NAME in "${DATABASES[@]}"; do
  /usr/local/mysql/bin/mysqldump --single-transaction -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" "$DATABASE_NAME" | gzip > "$DB_BACKUP_PATH/$TODAY/$DATABASE_NAME-$TODAY.sql.gz"

  if [ $? -eq 0 ]; then
    echo "Database backup for $DATABASE_NAME done"
  else
    echo "Error while backing up $DATABASE_NAME"
    exit 1
  fi
done

# 提前做免密
# ssh-keygen -t rsa 一直回车
# ssh-copy-id -i root@172.11.17.43  发送到远程服务器防止单点故障
for DATABASE_NAME in "${DATABASES[@]}"; do
  scp -r "$DB_BACKUP_PATH/$TODAY" root@172.11.17.43:/home/backup
done

# 删除目录年龄大于25天的备份
for directory in "$DB_BACKUP_PATH"*/; do
    if [ -d "$directory" ]; then
        dir_date=$(date -r "$directory" +%s)
        age=$(( (date - dir_date) / 86400 ))
        
        if [ "$age" -gt "$BACKUP_RETAIN_DAYS" ]; then
          rm -rf "$directory"
        fi
    fi
done