一个备份mysql全部数据库的脚本

脚本内容

#!/bin/bash  

set -x 
 
# MySQL 配置  
HOST="目标mysql地址"
PORT="端口"
MYSQL_USER="用户名"  
MYSQL_PASSWORD="密码"  
DATE=$(date +\%Y\%m\%d)  
BASE_BACKUP_DIR="/data/mysqldump"  
BACKUP_DIR="$BASE_BACKUP_DIR/$DATE"  
ARCHIVE_FILE="$BASE_BACKUP_DIR/${DATE}.tar.gz"

# 创建备份目录（如果不存在）  
mkdir -p $BACKUP_DIR  
  
# 获取所有数据库列表（这里只备份名称包含"cdp"的数据库）  
#DATABASES=$(mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -E "cdp")  
DATABASES=$(mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -h $HOST -P $PORT -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)")  
  
# 遍历所有数据库并进行备份  
for DB in $DATABASES; do  
  BACKUP_FILE="$BACKUP_DIR/${DB}_backup_$DATE.sql"  
  echo "$BACKUP_FILE"  
  mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD -h $HOST -P $PORT -R -E --single-transaction $DB > $BACKUP_FILE  
  
  # 检查备份是否成功  
  if [ $? -ne 0 ]; then  
    echo "[$DATE] Backup failed for database: $DB" >> $BASE_BACKUP_DIR/backup.log  
  else  
    echo "[$DATE] Backup successful for database: $DB, file: $BACKUP_FILE" >> $BASE_BACKUP_DIR/backup.log  
  fi  
done  

# 创建备份的 tar.gz 文件
tar -zcvf $ARCHIVE_FILE -C $BASE_BACKUP_DIR $DATE

# 检查 tar.gz 创建是否成功
if [ $? -eq 0 ]; then
  echo "[$DATE] Archive created successfully: $ARCHIVE_FILE" >> $BASE_BACKUP_DIR/backup.log
  
  # 删除原始的备份文件夹
  rm -rf $BACKUP_DIR
  echo "[$DATE] Original backup directory deleted: $BACKUP_DIR" >> $BASE_BACKUP_DIR/backup.log
else
  echo "[$DATE] Archive creation failed" >> $BASE_BACKUP_DIR/backup.log
fi


# 删除4周前的备份文件  
find $BASE_BACKUP_DIR/* -type d  -mtime +28 -exec rm -r {} \;  
find $BASE_BACKUP_DIR -type f -name "*.tar.gz" -mtime +28 -exec rm {} \;

echo "[$DATE] Old backups deleted (older than 4 weeks)" >> $BASE_BACKUP_DIR/backup.log

定时任务

0 23  * * 7  /data/mysqldump/backupall.sh  >/dev/null 2>&1 &