游乐游手机版
首页/数据库/文章详情

MySQL数据库备份恢复脚本实战分享

时间：2026-08-04 06:35
在日常数据库运维实践中，全库备份并非最常用的操作，更多情况下我们只需针对特定数据库进行备份与恢复。本文分享一套本人实际使用的MySQL备份恢复脚本，专为指定数据库设计，支持分表备份与单库恢复，开箱即用，可直接部署。 备份脚本：指定数据库分表备份 ! bin bash MySQL 连接参数 MY

在日常数据库运维实践中，全库备份并非最常用的操作，更多情况下我们只需针对特定数据库进行备份与恢复。本文分享一套本人实际使用的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 1
fi

# 指定的数据库列表
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
    fi
done

if [ $? -ne 0 ]; then
    log "错误: 无法连接到 MySQL 服务器或获取数据库列表"
    exit 1
fi

log "找到数据库: $(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
    done
done

# 备份完成统计
TOTAL_TABLES=0
for 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))
    fi
done

log "备份完成! 总共备份了 ${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 1
fi

BACKUP_DIR="$1"
DB_NAME="$2"
DB_BACKUP_DIR="$BACKUP_DIR/$DB_NAME"

# 检查备份目录和数据库备份是否存在
if [ ! -d "$BACKUP_DIR" ]; then
    echo "错误: 备份目录不存在: $BACKUP_DIR"
    exit 1
fi

if [ ! -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 1
fi

echo "开始恢复指定数据库"
echo "备份目录: $BACKUP_DIR"
echo "数据库名: $DB_NAME"
echo "MySQL 主机: $MYSQL_HOST:$MYSQL_PORT"
echo "=========================================="

# 确认操作
read -p "确认要恢复数据库 '$DB_NAME' 吗？这将删除现有数据库并重新导入！(y/N): " confirm
case "$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/null
if [ $? -ne 0 ]; then
    echo "错误: 无法创建数据库 '$DB_NAME'"
    exit 1
fi

# 恢复所有表
TABLE_COUNT=0
FAILED_COUNT=0
echo "开始恢复表数据..."
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
    fi
done

# 记录整个恢复过程的结束时间
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 个"
fi
echo "总耗时: ${OVERALL_DURATION} 秒"
echo "备份目录: $BACKUP_DIR"

# 显示数据库总行数统计
echo "------------------------------------------"
echo "数据库行数统计:"
TOTAL_ROWS=0
for 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
    fi
done
echo "  总计: $TOTAL_ROWS 行"

restore-onedb.sh 脚本功能解析

该恢复脚本虽然专注于单一数据库恢复，但在细节处理上非常完善。以下列举几个值得关注的设计亮点：

  • 脚本不仅记录了整个恢复过程的起止时间，还对每个表的恢复耗时进行了单独统计。在处理大型数据库时，这一功能对于性能分析与优化极为实用，能快速定位恢复缓慢的表。
  • 恢复前，脚本会检测目标数据库是否存在。若存在，则先删除再重新创建，确保恢复环境完全纯净，避免旧数据干扰。同时，创建数据库时明确指定字符集为 utf8mb4、排序规则为 utf8mb4_unicode_ci，以支持完整的 Unicode 字符集。
  • 恢复结束后，脚本会自动查询每张表的行数，以此验证数据恢复的完整性。尽管这种行数校验方法简单，但作为一项保底措施，能有效防范数据丢失或损坏。
来源：https://www.jb51.net/database/365059nsf.htm
MySQL备份
上一篇Redis集群宕机后重启失败问题与解决方案 下一篇Oracle数据库归档日志爆满ORA-00257紧急处理与自动清理方案
本站内容用于信息整理与展示，如有侵权或内容问题请及时联系处理。

相关推荐

补充同频道和同主题内容，方便继续浏览更多相关内容。

如何利用云存储API实现MySQL备份自动上传到对象存储 MySQL备份失败自动告警通知配置教程 MySQL数据库全量与增量备份操作指南 如何利用MySQL Pump工具进行多数据库并行备份 MySQL备份文件意外截断导致SQL语法错误的解决方法 2026年MySQL数据库备份与恢复新手完整指南

同类最新

继续查看同栏目最近更新的文章。

更多
Redis是什么：核心特性、架构与应用场景解析
数据库 · 2026-09-01

Redis是什么：核心特性、架构与应用场景解析

Redis是一款基于内存的键值型NoSQL数据库，以超高读写速度和丰富的数据结构著称。本文系统梳理Redis的核心特性、架构组成、性能优势及典型应用场景，并通过与Memcached、MySQL、MongoDB的对比，帮助开发者快速判断Redis是否适合当前业务需求。

 Windows 安装 MongoDB 完整图文教程
数据库 · 2026-09-01

Windows 安装 MongoDB 完整图文教程

本文详细介绍在 Windows 系统上安装 MongoDB 的完整流程。从官网下载 MSI 安装包开始，逐步演示自定义安装路径、配置 Windows 服务、跳过 MongoDB Compass 等关键选项，并提供通过系统服务列表验证安装是否成功的方法，帮助开发者快速搭建本地 MongoDB 环境。

 Linux 安装 MongoDB 完整指南：依赖配置、环境变量与服务启动
数据库 · 2026-09-01

Linux 安装 MongoDB 完整指南：依赖配置、环境变量与服务启动

本文详解在 Linux 系统下安装 MongoDB 的完整流程，涵盖依赖包安装、二进制包下载解压、环境变量配置、数据与日志目录创建及服务启动验证。通过标准化命令与路径说明，帮助开发者快速完成部署并确认服务状态。

 MacOS安装MongoDB完整教程
数据库 · 2026-09-01

MacOS安装MongoDB完整教程

本文介绍在MacOS系统下安装MongoDB的完整流程，涵盖下载、解压、目录配置、环境变量设置及服务启动。通过明确的命令与参数说明，帮助开发者快速完成环境搭建并验证安装结果。

 Ubuntu系统安装与配置Redis完整指南
数据库 · 2026-09-01

Ubuntu系统安装与配置Redis完整指南

本文详解在Ubuntu系统中安装Redis的两种主流方式：apt在线安装与源码编译安装。涵盖版本选择逻辑、服务启停与状态检查、连接验证方法，以及在线练习工具与桌面GUI客户端的对比与使用建议，帮助开发者快速搭建并验证Redis运行环境。