一文分享：MySQL数据库备份恢复脚本

本人亲测可用

在日常业务场景，通常情况下，我们不需要进行全库备份，而是只备份需要的数据库。本文分享一个MySQL数据库备份恢复脚本，用于备份和恢复指定的数据库。

备份脚本

#!/bin/bash# MySQL 连接参数MYSQL_HOST="127.0.0.1"MYSQL_PORT="3306"MYSQL_USER="root"MYSQL_PASS='your_password'# 备份目录BACKUP_DIR="mysql-backup/$(date +%Y%m%d_%H%M%S)"LOG_FILE="${BACKUP_DIR}/backup.log"# 创建备份目录mkdir -p "${BACKUP_DIR}"# 日志函数log() {    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "${LOG_FILE}"}# 开始备份log "开始 MySQL 分表备份"# 获取所有数据库列表（排除系统数据库）# 检查是否提供了数据库参数if [ $# -eq 0 ]; then    echo "用法: $0 <数据库1> [数据库2] [数据库3] ..."    echo "例如: $0 db1 db2 db3"    exit 1fi# 指定的数据库列表DATABASES=("$@")# 验证指定的数据库是否存在for DB in "${DATABASES[@]}"; do    # 检查数据库是否存在    DB_EXISTS=$(mysql -h "${MYSQL_HOST}" -P "${MYSQL_PORT}" -u "${MYSQL_USER}" -p${MYSQL_PASS}         -e "SHOW DATABASES LIKE '${DB}';" -s --skip-column-names)    if [ -n "$DB_EXISTS" ]; then        log "[INFO]数据库存在: $DB"    else        log "[ERROR]: 数据库 '$DB' 不存在，跳过"        exit 1    fidoneif [ $? -ne 0 ]; then    log "错误: 无法连接到 MySQL 服务器或获取数据库列表"    exit 1filog "找到数据库: $(echo ${DATABASES} | tr 'n' ' ')"# 遍历每个数据库for DB in ${DATABASES[@]}; do    log "正在处理数据库: ${DB}"    # 创建数据库目录    DB_DIR="${BACKUP_DIR}/${DB}"    mkdir -p "${DB_DIR}"    # 获取数据库中的所有表    TABLES=$(mysql -h "${MYSQL_HOST}" -P "${MYSQL_PORT}" -u "${MYSQL_USER}" -p${MYSQL_PASS}         -e "SHOW TABLES FROM `${DB}`;" -s --skip-column-names)    if [ $? -ne 0 ]; then        log "错误: 无法获取数据库 ${DB} 的表列表"        continue    fi    log "数据库 ${DB} 中找到表: $(echo ${TABLES} | tr 'n' ' ')"    # 遍历每个表进行备份    for TABLE in ${TABLES}; do        log "正在备份表: ${DB}.${TABLE}"        BACKUP_FILE="${DB_DIR}/${TABLE}.sql"        # 执行表备份（不包含存储过程、函数和事件）        mysqldump             -h "${MYSQL_HOST}"             -P "${MYSQL_PORT}"             -u "${MYSQL_USER}"             -p${MYSQL_PASS}             --single-transaction             --triggers             --hex-blob             --opt             --set-gtid-purged=OFF             --default-character-set=utf8mb4             --lock-tables=FALSE             --add-locks=FALSE             --skip-triggers             "${DB}" "${TABLE}" > "${BACKUP_FILE}" 2>> "${LOG_FILE}"        if [ $? -eq 0 ]; then            # 检查文件大小，如果太小可能是空表或备份失败            FILE_SIZE=$(stat -c%s "${BACKUP_FILE}" 2>/dev/null || stat -f%z "${BACKUP_FILE}")            if [ "${FILE_SIZE}" -lt 100 ]; then                log "警告: 表 ${DB}.${TABLE} 的备份文件可能为空或异常"            else                log "成功备份表 ${DB}.${TABLE} 到 ${BACKUP_FILE} (大小: ${FILE_SIZE} 字节)"            fi        else            log "错误: 备份表 ${DB}.${TABLE} 失败"        fi    donedone# 备份完成统计TOTAL_TABLES=0for DB in ${DATABASES}; do    if [ -d "${BACKUP_DIR}/${DB}" ]; then        DB_TABLES=$(ls "${BACKUP_DIR}/${DB}"/*.sql 2>/dev/null | wc -l)        TOTAL_TABLES=$((TOTAL_TABLES + DB_TABLES))    fidonelog "备份完成! 总共备份了 ${TOTAL_TABLES} 个表"log "备份文件保存在: ${BACKUP_DIR}"

备份目录结构如下：

mysql-backup/

└── YYYYMMDD_HHMMSS/

    ├── backup.log

    ├── db1/

    │   ├── table1.sql

    │   ├── table2.sql

    │   └── ...

    ├── db2/

    │   ├── table1.sql

    │   ├── table2.sql

    │   └── ...

    └── ...

其中：

• YYYYMMDD_HHMMSS 是备份时间戳（年、月、日、时、分、秒）
• backup.log 是备份过程的日志文件
• 每个数据库都有自己的目录，目录名与数据库名相同
• 每个表都以 .sql 文件格式单独备份，文件名与表名相同

恢复脚本

这个恢复脚本是和上面备份脚本配套使用的，用于恢复指定数据库的备份。只能恢复一个数据库，但是也够用了。

restore-onedb.sh

#!/bin/bash# MySQL 连接参数MYSQL_HOST="127.0.0.1"MYSQL_PORT="3306"MYSQL_USER="root"MYSQL_PASS="your_mysql_password"# 检查参数if [ $# -ne 2 ]; then    echo "用法: $0 <备份目录路径> <数据库名>"    echo "例如: $0 /opt/mysql-backup/20231201_143022 mydatabase"    exit 1fiBACKUP_DIR="$1"DB_NAME="$2"DB_BACKUP_DIR="$BACKUP_DIR/$DB_NAME"# 检查备份目录和数据库备份是否存在if [ ! -d "$BACKUP_DIR" ]; then    echo "错误: 备份目录不存在: $BACKUP_DIR"    exit 1fiif [ ! -d "$DB_BACKUP_DIR" ]; then    echo "错误: 在备份目录中未找到数据库 '$DB_NAME' 的备份"    echo "可用的数据库备份:"    find "$BACKUP_DIR" -maxdepth 1 -type d -not -path "$BACKUP_DIR" -not -name ".*" -exec basename {} ; | grep -v -E "(backup|log)"    exit 1fiecho "开始恢复指定数据库"echo "备份目录: $BACKUP_DIR"echo "数据库名: $DB_NAME"echo "MySQL 主机: $MYSQL_HOST:$MYSQL_PORT"echo "=========================================="# 确认操作read -p "确认要恢复数据库 '$DB_NAME' 吗？这将删除现有数据库并重新导入！(y/N): " confirmcase "$confirm" in    [yY]|[yY][eE][sS])        echo "开始恢复..."        ;;    *)        echo "操作已取消"        exit 0        ;;esac# 记录整个恢复过程的开始时间OVERALL_START_TIME=$(date +%s)# 检查数据库是否存在DB_EXISTS=$(mysql -h "${MYSQL_HOST}" -P "${MYSQL_PORT}" -u "${MYSQL_USER}" -p${MYSQL_PASS}     -e "SHOW DATABASES LIKE '${DB_NAME}';" -s --skip-column-names 2>/dev/null)if [ -n "$DB_EXISTS" ]; then    echo "数据库 '$DB_NAME' 已存在，正在删除..."    mysql -h "${MYSQL_HOST}" -P "${MYSQL_PORT}" -u "${MYSQL_USER}" -p${MYSQL_PASS}         -e "DROP DATABASE `${DB_NAME}`;" 2>/dev/null    if [ $? -ne 0 ]; then        echo "错误: 无法删除数据库 '$DB_NAME'"        exit 1    fi    echo "数据库 '$DB_NAME' 已成功删除"fi# 创建新数据库echo "创建数据库 '$DB_NAME'..."mysql -h "${MYSQL_HOST}" -P "${MYSQL_PORT}" -u "${MYSQL_USER}" -p${MYSQL_PASS}     -e "CREATE DATABASE `${DB_NAME}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" 2>/dev/nullif [ $? -ne 0 ]; then    echo "错误: 无法创建数据库 '$DB_NAME'"    exit 1fi# 恢复所有表TABLE_COUNT=0FAILED_COUNT=0echo "开始恢复表数据..."echo "------------------------------------------"for TABLE_FILE in "$DB_BACKUP_DIR"/*.sql; do    if [ -f "$TABLE_FILE" ]; then        TABLE_NAME=$(basename "$TABLE_FILE" .sql)        # 记录单个表恢复的开始时间        TABLE_START_TIME=$(date +%s)        # 获取SQL文件大小        FILE_SIZE=$(du -h "$TABLE_FILE" | cut -f1)        echo "恢复表: $DB_NAME.$TABLE_NAME (文件大小: $FILE_SIZE)"        # 恢复表        mysql -h "${MYSQL_HOST}" -P "${MYSQL_PORT}" -u "${MYSQL_USER}" -p${MYSQL_PASS}             "$DB_NAME" < "$TABLE_FILE"        if [ $? -eq 0 ]; then            # 记录单个表恢复的结束时间            TABLE_END_TIME=$(date +%s)            TABLE_DURATION=$((TABLE_END_TIME - TABLE_START_TIME))            # 查询表的行数            ROW_COUNT=$(mysql -h "${MYSQL_HOST}" -P "${MYSQL_PORT}" -u "${MYSQL_USER}" -p${MYSQL_PASS}                 -e "SELECT COUNT(*) FROM `${DB_NAME}`.`${TABLE_NAME}`;" -s --skip-column-names 2>/dev/null)            if [ $? -eq 0 ]; then                echo "✓ 成功恢复表: $DB_NAME.$TABLE_NAME | 耗时: ${TABLE_DURATION}秒 | 行数: $ROW_COUNT"            else                echo "✓ 成功恢复表: $DB_NAME.$TABLE_NAME | 耗时: ${TABLE_DURATION}秒 | 行数: 查询失败"            fi            TABLE_COUNT=$((TABLE_COUNT + 1))        else            echo "✗ 错误: 恢复表 $DB_NAME.$TABLE_NAME 失败"            FAILED_COUNT=$((FAILED_COUNT + 1))        fi    fidone# 记录整个恢复过程的结束时间OVERALL_END_TIME=$(date +%s)OVERALL_DURATION=$((OVERALL_END_TIME - OVERALL_START_TIME))echo "=========================================="echo "数据库 '$DB_NAME' 恢复完成!"echo "成功恢复表: $TABLE_COUNT 个"if [ $FAILED_COUNT -gt 0 ]; then    echo "恢复失败表: $FAILED_COUNT 个"fiecho "总耗时: ${OVERALL_DURATION} 秒"echo "备份目录: $BACKUP_DIR"# 显示数据库总行数统计echo "------------------------------------------"echo "数据库行数统计:"TOTAL_ROWS=0for TABLE_FILE in "$DB_BACKUP_DIR"/*.sql; do    if [ -f "$TABLE_FILE" ]; then        TABLE_NAME=$(basename "$TABLE_FILE" .sql)        ROW_COUNT=$(mysql -h "${MYSQL_HOST}" -P "${MYSQL_PORT}" -u "${MYSQL_USER}" -p${MYSQL_PASS}             -e "SELECT COUNT(*) FROM `${DB_NAME}`.`${TABLE_NAME}`;" -s --skip-column-names 2>/dev/null)        if [ $? -eq 0 ] && [ -n "$ROW_COUNT" ]; then            echo "  $TABLE_NAME: $ROW_COUNT 行"            TOTAL_ROWS=$((TOTAL_ROWS + ROW_COUNT))        fi    fidoneecho "  总计: $TOTAL_ROWS 行"

restore-onedb.sh 脚本代码分析

该恢复脚本设计用于恢复单个数据库的备份，并提供了完善的错误处理、用户交互和统计功能。

• 记录了整个恢复过程的开始和结束时间，以及每个表恢复的单独时间，便于性能分析和优化，特别是在处理大型数据库时。
• 脚本会检查目标数据库是否存在，如果存在则先删除再创建，确保恢复环境干净。创建数据库时指定了字符集和排序规则为utf8mb4，支持完整的Unicode字符，确保数据编码正确性。
• 脚本会查询每个恢复后的表的行数，用于验证数据恢复的完整性。