Mysql数据仓库备份脚本

#!/bin/bash# MySQL数据库完整备份脚本
# 功能: 查询所有数据库 -> 分别导出 -> 压缩打包# ==================== 配置区域 ====================
# MySQL连接信息
MYSQL_USER="root"
MYSQL_PASSWORD="root"
MYSQL_HOST="localhost"
MYSQL_PORT="3306"# 备份配置
BACKUP_BASE_DIR="/home/db"
BACKUP_FOLDER="mysql_backup_$(date +%Y%m%d_%H%M%S)"
BACKUP_DIR="$BACKUP_BASE_DIR/$BACKUP_FOLDER"
ARCHIVE_FILE="$BACKUP_BASE_DIR/${BACKUP_FOLDER}.tar.gz"
LOG_FILE="/home/db/mysql_full_backup.log"# ==================== 函数定义 ====================
# 日志记录函数
log_message() {echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}# 检查和创建备份目录
check_backup_dir() {log_message "检查和创建备份目录..."# 创建基础备份目录if [ ! -d "$BACKUP_BASE_DIR" ]; thenlog_message "创建基础备份目录: $BACKUP_BASE_DIR"mkdir -p "$BACKUP_BASE_DIR"if [ $? -ne 0 ]; thenlog_message "错误: 无法创建基础备份目录 $BACKUP_BASE_DIR"exit 1fifi# 创建本次备份目录if [ ! -d "$BACKUP_DIR" ]; thenlog_message "创建本次备份目录: $BACKUP_DIR"mkdir -p "$BACKUP_DIR"if [ $? -ne 0 ]; thenlog_message "错误: 无法创建备份目录 $BACKUP_DIR"exit 1fifi
}# 检查MySQL连接
check_mysql_connection() {log_message "正在检查MySQL连接..."mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "SELECT 1;" >/dev/null 2>&1if [ $? -ne 0 ]; thenlog_message "错误: 无法连接到MySQL数据库"exit 1filog_message "MySQL连接正常"
}# 查询所有数据库
get_all_databases() {log_message "查询所有数据库..."DATABASES=($(mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "SHOW DATABASES;" 2>>"$LOG_FILE" | grep -v Database))log_message "发现 ${#DATABASES[@]} 个数据库: ${DATABASES[*]}"
}# 导出单个数据库
export_database() {local db_name=$1local output_file="$BACKUP_DIR/${db_name}.sql"local temp_file="${output_file}.tmp"log_message "导出数据库: $db_name"mysqldump \-h"$MYSQL_HOST" \-P"$MYSQL_PORT" \-u"$MYSQL_USER" \-p"$MYSQL_PASSWORD" \--single-transaction \--routines \--triggers \--events \--set-gtid-purged=OFF \--opt \--hex-blob \"$db_name" > "$temp_file" 2>>"$LOG_FILE"if [ $? -eq 0 ] && [ -s "$temp_file" ]; thenmv "$temp_file" "$output_file"local file_size=$(du -h "$output_file" | cut -f1)log_message "数据库 $db_name 导出成功 (大小: $file_size)"return 0elselog_message "错误: 数据库 $db_name 导出失败"rm -f "$temp_file" 2>/dev/nullreturn 1fi
}# 导出所有数据库
export_all_databases() {log_message "开始导出所有数据库..."local success_count=0local failed_count=0for db in "${DATABASES[@]}"; doif export_database "$db"; thensuccess_count=$((success_count + 1))elsefailed_count=$((failed_count + 1))fidonelog_message "数据库导出完成: 成功 $success_count 个, 失败 $failed_count 个"return $failed_count
}# 压缩备份文件夹
compress_backup_folder() {log_message "开始压缩备份文件夹: $BACKUP_DIR"# 检查备份目录是否存在且不为空if [ ! -d "$BACKUP_DIR" ] || [ -z "$(ls -A "$BACKUP_DIR")" ]; thenlog_message "错误: 备份目录不存在或为空"return 1fi# 进入基础目录并压缩cd "$BACKUP_BASE_DIR" || return 1tar -czf "$ARCHIVE_FILE" "$BACKUP_FOLDER" 2>>"$LOG_FILE"if [ $? -eq 0 ] && [ -f "$ARCHIVE_FILE" ]; thenlocal archive_size=$(du -h "$ARCHIVE_FILE" | cut -f1)log_message "备份文件夹压缩完成: $ARCHIVE_FILE (大小: $archive_size)"return 0elselog_message "错误: 备份文件夹压缩失败"return 1fi
}# 验证压缩文件
verify_compressed_file() {if [ ! -f "$ARCHIVE_FILE" ]; thenlog_message "警告: 压缩文件不存在: $ARCHIVE_FILE"return 1fi# 检查文件大小local file_size=$(du -h "$ARCHIVE_FILE" | cut -f1)if [ "$file_size" = "0" ]; thenlog_message "警告: 压缩文件为空: $ARCHIVE_FILE"return 1filog_message "压缩文件验证通过: $ARCHIVE_FILE (大小: $file_size)"return 0
}# 清理原始备份目录（压缩成功后）
cleanup_backup_directory() {log_message "清理原始备份目录..."if [ -d "$BACKUP_DIR" ]; thenrm -rf "$BACKUP_DIR"if [ $? -eq 0 ]; thenlog_message "原始备份目录清理完成"elselog_message "警告: 原始备份目录清理失败"fifi
}# 生成备份摘要
generate_backup_summary() {local failed_count=$1local summary_file="$BACKUP_BASE_DIR/backup_summary_$(date +%Y%m%d_%H%M%S).txt"{echo "MySQL数据库备份摘要"echo "========================"echo "备份时间: $(date)"echo "备份目录: $BACKUP_DIR"echo "压缩文件: $ARCHIVE_FILE"echo "数据库总数: ${#DATABASES[@]}"echo "备份成功: $(( ${#DATABASES[@]} - failed_count )) 个"echo "备份失败: $failed_count 个"echo ""echo "数据库列表:"for db in "${DATABASES[@]}"; doecho "  - $db"doneecho ""if [ -f "$ARCHIVE_FILE" ]; thenecho "压缩文件信息:"echo "  文件路径: $ARCHIVE_FILE"echo "  文件大小: $(du -h "$ARCHIVE_FILE" | cut -f1)"echo "  修改时间: $(stat -c %y "$ARCHIVE_FILE")"fi} > "$summary_file"log_message "备份摘要已生成: $summary_file"
}# ==================== 主程序 ====================
main() {log_message "========== MySQL数据库完整备份开始 =========="# 1. 检查环境和创建目录check_backup_dir# 2. 检查MySQL连接check_mysql_connection# 3. 查询所有数据库get_all_databases# 4. 导出所有数据库local failed_count=0if ! export_all_databases; thenfailed_count=$?fi# 5. 压缩备份文件夹local compress_success=0if compress_backup_folder; thencompress_success=1# 验证压缩文件verify_compressed_file# 清理原始目录cleanup_backup_directoryfi# 6. 生成备份摘要generate_backup_summary $failed_countlog_message "========== MySQL数据库完整备份结束 =========="# 输出最终结果echo ""echo "备份完成:"echo "  备份目录: $BACKUP_DIR"echo "  压缩文件: $ARCHIVE_FILE"echo "  数据库总数: ${#DATABASES[@]}"echo "  失败数量: $failed_count"if [ $failed_count -gt 0 ]; thenlog_message "警告: 存在备份失败的数据库"exit 1fiif [ $compress_success -eq 0 ]; thenlog_message "警告: 压缩备份文件夹失败"exit 1filog_message "所有操作成功完成"
}# ==================== 脚本执行 ====================
# 捕获中断信号
trap 'log_message "备份被用户中断"; rm -rf "$BACKUP_DIR" 2>/dev/null; exit 130' INT TERM# 执行主程序
main "$@"exit 0