MySQL 备份恢复实战 mysqldump 与 XtraBackup 用法 生产备份策略

数据安全是数据库管理的核心，而备份与恢复是保障数据安全的关键手段。结合实际生产事故经验，合理选择备份工具、制定科学备份策略，能有效降低数据丢失风险、缩短恢复时间。本文详细讲解 mysqldump 与 Percona XtraBackup 两大工具的用法、适用场景，搭配实战脚本与故障排查方案，助力 DBA 高效完成备份与恢复工作。

一、概述

1.1 背景介绍

数据库备份是 DBA 工作中最重要的任务之一，没有之一。

2018 年某次事故至今记忆犹新：开发人员误执行了一条没有 WHERE 条件的 DELETE 语句，直接删除了用户表的全部数据。当时的备份策略只有每天凌晨一次全量备份，而事故发生在下午 5 点。最终通过 binlog 恢复数据，但整个过程耗时 4 小时，造成了严重的业务影响。

从那之后，备份策略从简单的每日全备，升级为”全量备份+增量备份+binlog 归档”的完整方案，RTO（恢复时间目标）从 4 小时降低到 30 分钟，RPO（恢复点目标）从 24 小时降低到 5 分钟。

MySQL 备份主要有两大工具：

• mysqldump：官方逻辑备份工具，简单可靠，适合小型数据库；
• Percona XtraBackup：物理热备份工具，高效快速，适合大型数据库。

本文将详细介绍这两种工具的使用方法、适用场景以及生产环境的最佳实践。

1.2 技术特点

mysqldump 特点

Percona XtraBackup 特点

1.3 适用场景

1.4 环境要求

二、详细步骤

2.1 准备工作

2.1.1 安装备份工具

# Rocky Linux 9 / CentOS Stream 9
# 安装 Percona 仓库
sudo dnf install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release setup pxb-80

# 安装 XtraBackup
sudo dnf install -y percona-xtrabackup-80

# 安装压缩工具
sudo dnf install -y qpress lz4

# 验证安装
xtrabackup --version
# xtrabackup version 8.0.35-30 based on MySQL server 8.0.35

# Ubuntu 24.04
sudo apt-get update
sudo apt-get install -y wget gnupg2 lsb-release
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
sudo percona-release setup pxb-80
sudo apt-get install -y percona-xtrabackup-80 qpress

2.1.2 创建备份用户

-- 创建备份专用用户
CREATEUSER'backup'@'localhost'IDENTIFIEDBY'BackupPass@2024';

-- mysqldump 所需权限
GRANTSELECT, SHOWVIEW, TRIGGER, LOCKTABLES, EVENTON *.* TO'backup'@'localhost';

-- XtraBackup 所需权限
GRANT RELOAD, PROCESS, LOCKTABLES, REPLICATIONCLIENTON *.* TO'backup'@'localhost';
GRANT BACKUP_ADMIN ON *.* TO'backup'@'localhost';  -- MySQL 8.0+
GRANTSELECTON performance_schema.log_status TO'backup'@'localhost';
GRANTSELECTON performance_schema.keyring_component_status TO'backup'@'localhost';

FLUSHPRIVILEGES;

2.1.3 规划备份目录

# 创建备份目录结构
sudo mkdir -p /backup/mysql/{full,incremental,binlog,scripts,logs}
sudo chown -R mysql:mysql /backup/mysql
sudo chmod 750 /backup/mysql

# 目录说明：
# /backup/mysql/full        - 全量备份
# /backup/mysql/incremental - 增量备份
# /backup/mysql/binlog      - binlog 归档
# /backup/mysql/scripts     - 备份脚本
# /backup/mysql/logs        - 备份日志

# 建议：备份目录使用独立磁盘或 NFS 挂载

2.2 核心配置

2.2.1 mysqldump 基础用法

# 备份单个数据库
mysqldump -u backup -p'BackupPass@2024' \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    mydb > /backup/mysql/full/mydb_$(date +%Y%m%d).sql

# 备份多个数据库
mysqldump -u backup -p'BackupPass@2024' \
    --single-transaction \
    --routines \
    --triggers \
    --databases db1 db2 db3 > /backup/mysql/full/multi_db_$(date +%Y%m%d).sql

# 备份所有数据库
mysqldump -u backup -p'BackupPass@2024' \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --all-databases > /backup/mysql/full/all_db_$(date +%Y%m%d).sql

# 备份单个表
mysqldump -u backup -p'BackupPass@2024' \
    --single-transaction \
    mydb users > /backup/mysql/full/mydb_users_$(date +%Y%m%d).sql

# 只备份表结构
mysqldump -u backup -p'BackupPass@2024' \
    --no-data \
    mydb > /backup/mysql/full/mydb_schema_$(date +%Y%m%d).sql

# 只备份数据
mysqldump -u backup -p'BackupPass@2024' \
    --no-create-info \
    --single-transaction \
    mydb > /backup/mysql/full/mydb_data_$(date +%Y%m%d).sql

2.2.2 mysqldump 关键参数详解

# 生产环境推荐参数组合
mysqldump -u backup -p'BackupPass@2024' \
    --single-transaction \          # InnoDB 一致性读，不锁表
    --master-data=2 \               # 记录 binlog 位置（注释形式）
    --routines \                    # 包含存储过程和函数
    --triggers \                    # 包含触发器
    --events \                      # 包含事件调度器
    --set-gtid-purged=AUTO \        # GTID 处理（自动判断）
    --hex-blob \                    # 二进制数据使用十六进制
    --quick \                       # 逐行读取，减少内存使用
    --max-allowed-packet=512M \     # 大数据包支持
    --default-character-set=utf8mb4 \  # 字符集
    --all-databases \
    | gzip > /backup/mysql/full/all_db_$(date +%Y%m%d).sql.gz

# MySQL 8.0.26+ 使用新参数名
# --master-data 改为 --source-data
mysqldump -u backup -p'BackupPass@2024' \
    --single-transaction \
    --source-data=2 \               # 新参数名
    --all-databases \
    | gzip > /backup/mysql/full/all_db_$(date +%Y%m%d).sql.gz

参数说明表：

2.2.3 XtraBackup 基础用法

# 全量备份
xtrabackup --backup \
    --user=backup \
    --password='BackupPass@2024' \
    --target-dir=/backup/mysql/full/$(date +%Y%m%d)

# 备份并压缩
xtrabackup --backup \
    --user=backup \
    --password='BackupPass@2024' \
    --target-dir=/backup/mysql/full/$(date +%Y%m%d) \
    --compress \
    --compress-threads=4

# 备份并流式传输到远程
xtrabackup --backup \
    --user=backup \
    --password='BackupPass@2024' \
    --stream=xbstream \
    --compress \
    | ssh backup@remote-server "cat > /backup/mysql/full_$(date +%Y%m%d).xbstream"

# 准备备份（应用 redo log）
xtrabackup --prepare \
    --target-dir=/backup/mysql/full/20240115

# 恢复备份
xtrabackup --copy-back \
    --target-dir=/backup/mysql/full/20240115

# 设置权限
chown -R mysql:mysql /data/mysql/data

2.2.4 XtraBackup 增量备份

# 第一步：创建全量备份（基准备份）
xtrabackup --backup \
    --user=backup \
    --password='BackupPass@2024' \
    --target-dir=/backup/mysql/full/base

# 第二步：创建第一次增量备份
xtrabackup --backup \
    --user=backup \
    --password='BackupPass@2024' \
    --target-dir=/backup/mysql/incremental/inc1 \
    --incremental-basedir=/backup/mysql/full/base

# 第三步：创建第二次增量备份（基于第一次增量）
xtrabackup --backup \
    --user=backup \
    --password='BackupPass@2024' \
    --target-dir=/backup/mysql/incremental/inc2 \
    --incremental-basedir=/backup/mysql/incremental/inc1

# 准备增量备份（恢复前必须）
# 1. 准备基准备份（不应用 redo log）
xtrabackup --prepare \
    --apply-log-only \
    --target-dir=/backup/mysql/full/base

# 2. 应用第一次增量
xtrabackup --prepare \
    --apply-log-only \
    --target-dir=/backup/mysql/full/base \
    --incremental-dir=/backup/mysql/incremental/inc1

# 3. 应用最后一次增量（不加--apply-log-only）
xtrabackup --prepare \
    --target-dir=/backup/mysql/full/base \
    --incremental-dir=/backup/mysql/incremental/inc2

# 4. 最终准备
xtrabackup --prepare \
    --target-dir=/backup/mysql/full/base

2.3 启动和验证

2.3.1 备份验证方法

方法一：恢复到测试环境验证

#!/bin/bash
# 文件：/backup/mysql/scripts/verify_backup.sh
# 功能：备份验证脚本

BACKUP_FILE=$1
TEST_DATADIR="/tmp/mysql_verify_$(date +%s)"
TEST_PORT=3307

echo"Starting backup verification..."

# 解压备份（如果是压缩的）
if [[ $BACKUP_FILE == *.gz ]]; then
    gunzip -c $BACKUP_FILE > /tmp/verify_backup.sql
    BACKUP_FILE="/tmp/verify_backup.sql"
fi

# 创建临时数据目录
mkdir -p $TEST_DATADIR
mysqld --initialize-insecure --datadir=$TEST_DATADIR --user=mysql

# 启动临时 MySQL 实例
mysqld --datadir=$TEST_DATADIR --port=$TEST_PORT --socket=/tmp/mysql_verify.sock &
MYSQL_PID=$!
sleep 10

# 导入备份
mysql -uroot -S /tmp/mysql_verify.sock < $BACKUP_FILE
IMPORT_STATUS=$?

# 验证数据
if [ $IMPORT_STATUS -eq 0 ]; then
    echo"Checking tables..."
    mysql -uroot -S /tmp/mysql_verify.sock -e "
        SELECT table_schema, table_name, table_rows
        FROM information_schema.TABLES
        WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
        ORDER BY table_schema, table_name;
    "
    echo"Backup verification PASSED"
else
    echo"Backup verification FAILED"
fi

# 清理
kill$MYSQL_PID 2>/dev/null
rm -rf $TEST_DATADIR /tmp/mysql_verify.sock /tmp/verify_backup.sql

方法二：检查备份文件完整性

# 检查 mysqldump 备份
# 文件应该以特定注释开头和结尾
head -20 /backup/mysql/full/all_db_20240115.sql
tail -20 /backup/mysql/full/all_db_20240115.sql

# 检查是否有错误
grep -i "error\|warning" /backup/mysql/full/all_db_20240115.sql

# 检查 XtraBackup 备份
# 查看备份信息
cat /backup/mysql/full/20240115/xtrabackup_checkpoints
# backup_type = full-backuped
# from_lsn = 0
# to_lsn = 123456789
# last_lsn = 123456789

cat /backup/mysql/full/20240115/xtrabackup_info

2.3.2 备份元数据记录

-- 创建备份记录表
CREATEDATABASEIFNOTEXISTS backup_meta;
USE backup_meta;

CREATETABLE backup_history (
    idINT AUTO_INCREMENT PRIMARY KEY,
    backup_type ENUM('full', 'incremental', 'binlog') NOTNULL,
    backup_tool VARCHAR(50) NOTNULL,
    backup_path VARCHAR(500) NOTNULL,
    backup_size_bytes BIGINT,
    compressed_size_bytes BIGINT,
    start_time DATETIME NOTNULL,
    end_time DATETIME,
    duration_seconds INT,
    status ENUM('running', 'success', 'failed') DEFAULT'running',
    binlog_file VARCHAR(100),
    binlog_position BIGINT,
    gtid_executed TEXT,
    error_message TEXT,
    verified TINYINTDEFAULT0,
    verified_time DATETIME,
    INDEX idx_backup_time (start_time),
    INDEX idx_status (status)
) ENGINE=InnoDB;

-- 插入备份记录示例
INSERTINTO backup_history (backup_type, backup_tool, backup_path, start_time, binlog_file, binlog_position)
VALUES ('full', 'xtrabackup', '/backup/mysql/full/20240115', NOW(), 'mysql-bin.000123', 456789);

三、示例代码和配置

3.1 完整配置示例

3.1.1 生产环境 mysqldump 备份脚本

#!/bin/bash
# 文件：/backup/mysql/scripts/mysqldump_backup.sh
# 功能：生产环境 mysqldump 备份脚本
# 用法：./mysqldump_backup.sh [full|single] [database_name]

set -e

# 配置
MYSQL_USER="backup"
MYSQL_PASS="BackupPass@2024"
MYSQL_HOST="localhost"
BACKUP_DIR="/backup/mysql/full"
LOG_DIR="/backup/mysql/logs"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="${LOG_DIR}/mysqldump_${DATE}.log"

# 备份元数据库
META_DB="backup_meta"
RECORD_BACKUP=true

# 初始化
mkdir -p ${BACKUP_DIR}${LOG_DIR}

log() {
    echo"[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a ${LOG_FILE}
}

record_backup() {
    local status=$1
    local error_msg=$2
    local end_time=$(date '+%Y-%m-%d %H:%M:%S')
    local backup_size=$(stat -c%s ${BACKUP_FILE} 2>/dev/null || echo 0)
    local compressed_size=$(stat -c%s ${BACKUP_FILE}.gz 2>/dev/null || echo 0)

    if [ "$RECORD_BACKUP" = true ]; then
        mysql -u${MYSQL_USER} -p${MYSQL_PASS}${META_DB} << EOF
UPDATE backup_history SET
    end_time = '${end_time}',
    duration_seconds = TIMESTAMPDIFF(SECOND, start_time, '${end_time}'),
    status = '${status}',
    backup_size_bytes = ${backup_size},
    compressed_size_bytes = ${compressed_size},
    error_message = '${error_msg}'
WHERE id = ${BACKUP_ID};
EOF
    fi
}

do_full_backup() {
    log"Starting full backup..."
    BACKUP_FILE="${BACKUP_DIR}/all_databases_${DATE}.sql"

    # 获取 binlog 位置
    BINLOG_INFO=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -N -e "SHOW MASTER STATUS")
    BINLOG_FILE=$(echo$BINLOG_INFO | awk '{print $1}')
    BINLOG_POS=$(echo$BINLOG_INFO | awk '{print $2}')
    GTID_EXECUTED=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -N -e "SELECT @@global.gtid_executed" | tr '\n'' ')

    # 记录备份开始
    if [ "$RECORD_BACKUP" = true ]; then
        BACKUP_ID=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS}${META_DB} -N -e "
            INSERT INTO backup_history (backup_type, backup_tool, backup_path, start_time, binlog_file, binlog_position, gtid_executed)
            VALUES ('full', 'mysqldump', '${BACKUP_FILE}', NOW(), '${BINLOG_FILE}', ${BINLOG_POS}, '${GTID_EXECUTED}');
            SELECT LAST_INSERT_ID();
        ")
    fi

    # 执行备份
    log"Backup file: ${BACKUP_FILE}"
    log"Binlog: ${BINLOG_FILE}:${BINLOG_POS}"

    mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} \
        --single-transaction \
        --source-data=2 \
        --routines \
        --triggers \
        --events \
        --set-gtid-purged=AUTO \
        --hex-blob \
        --quick \
        --max-allowed-packet=512M \
        --default-character-set=utf8mb4 \
        --all-databases > ${BACKUP_FILE} 2>>${LOG_FILE}

    if [ $? -eq 0 ]; then
        log"Backup completed successfully"

        # 压缩
        log"Compressing backup..."
        gzip ${BACKUP_FILE}
        log"Compressed size: $(du -h ${BACKUP_FILE}.gz | cut -f1)"

        record_backup "success"""
    else
        log"Backup FAILED!"
        record_backup "failed""mysqldump failed"
        exit 1
    fi
}

do_single_backup() {
    local DB_NAME=$1
    log"Starting single database backup: ${DB_NAME}"
    BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql"

    mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} \
        --single-transaction \
        --source-data=2 \
        --routines \
        --triggers \
        --set-gtid-purged=AUTO \
        --hex-blob \
        --quick \
        ${DB_NAME} > ${BACKUP_FILE} 2>>${LOG_FILE}

    if [ $? -eq 0 ]; then
        log"Single database backup completed: ${DB_NAME}"
        gzip ${BACKUP_FILE}
    else
        log"Single database backup FAILED: ${DB_NAME}"
        exit 1
    fi
}

cleanup_old_backups() {
    log"Cleaning up backups older than ${RETENTION_DAYS} days..."
    find ${BACKUP_DIR} -name "*.sql.gz" -mtime +${RETENTION_DAYS} -delete
    find ${LOG_DIR} -name "*.log" -mtime +${RETENTION_DAYS} -delete
    log"Cleanup completed"
}

# 主程序
case"$1"in
    full)
        do_full_backup
        ;;
    single)
        if [ -z "$2" ]; then
            log"Error: Database name required"
            exit 1
        fi
        do_single_backup $2
        ;;
    *)
        do_full_backup
        ;;
esac

cleanup_old_backups

log"Backup script completed"

3.1.2 生产环境 XtraBackup 备份脚本

#!/bin/bash
# 文件：/backup/mysql/scripts/xtrabackup_backup.sh
# 功能：生产环境 XtraBackup 备份脚本
# 用法：./xtrabackup_backup.sh [full|incremental]

set -e

# 配置
MYSQL_USER="backup"
MYSQL_PASS="BackupPass@2024"
BACKUP_BASE="/backup/mysql"
FULL_BACKUP_DIR="${BACKUP_BASE}/full"
INCR_BACKUP_DIR="${BACKUP_BASE}/incremental"
LOG_DIR="${BACKUP_BASE}/logs"
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="${LOG_DIR}/xtrabackup_${DATE}.log"
COMPRESS_THREADS=4
PARALLEL_THREADS=4

# 保留策略
FULL_RETENTION_DAYS=30
INCR_RETENTION_DAYS=7

# 初始化
mkdir -p ${FULL_BACKUP_DIR}${INCR_BACKUP_DIR}${LOG_DIR}

log() {
    echo"[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a ${LOG_FILE}
}

get_latest_full_backup() {
    ls -td ${FULL_BACKUP_DIR}/*/ 2>/dev/null | head -1
}

get_latest_incremental() {
    local base_dir=$1
    local base_name=$(basename $base_dir)

    # 查找基于该全备的最新增量
    ls -td ${INCR_BACKUP_DIR}/${base_name}_inc*/ 2>/dev/null | head -1
}

do_full_backup() {
    log"Starting full backup..."
    local BACKUP_PATH="${FULL_BACKUP_DIR}/${DATE}"

    xtrabackup --backup \
        --user=${MYSQL_USER} \
        --password=${MYSQL_PASS} \
        --target-dir=${BACKUP_PATH} \
        --parallel=${PARALLEL_THREADS} \
        --compress \
        --compress-threads=${COMPRESS_THREADS} \
        2>>${LOG_FILE}

    if [ $? -eq 0 ]; then
        log"Full backup completed: ${BACKUP_PATH}"

        # 记录备份信息
        cat ${BACKUP_PATH}/xtrabackup_checkpoints | tee -a ${LOG_FILE}

        # 计算大小
        local SIZE=$(du -sh ${BACKUP_PATH} | cut -f1)
        log"Backup size: ${SIZE}"
    else
        log"Full backup FAILED!"
        exit 1
    fi
}

do_incremental_backup() {
    log"Starting incremental backup..."

    # 获取基准目录
    local LATEST_FULL=$(get_latest_full_backup)
    if [ -z "$LATEST_FULL" ]; then
        log"No full backup found, performing full backup instead"
        do_full_backup
        return
    fi

    local FULL_NAME=$(basename $LATEST_FULL)
    log"Base full backup: ${LATEST_FULL}"

    # 检查是否有之前的增量
    local LATEST_INCR=$(get_latest_incremental $LATEST_FULL)
    local BASE_DIR=$LATEST_FULL

    if [ -n "$LATEST_INCR" ]; then
        BASE_DIR=$LATEST_INCR
        log"Base incremental backup: ${LATEST_INCR}"
    fi

    # 增量序号
    local INCR_COUNT=$(ls -d ${INCR_BACKUP_DIR}/${FULL_NAME}_inc*/ 2>/dev/null | wc -l)
    local INCR_NAME="${FULL_NAME}_inc$((INCR_COUNT + 1))_${DATE}"
    local BACKUP_PATH="${INCR_BACKUP_DIR}/${INCR_NAME}"

    xtrabackup --backup \
        --user=${MYSQL_USER} \
        --password=${MYSQL_PASS} \
        --target-dir=${BACKUP_PATH} \
        --incremental-basedir=${BASE_DIR} \
        --parallel=${PARALLEL_THREADS} \
        --compress \
        --compress-threads=${COMPRESS_THREADS} \
        2>>${LOG_FILE}

    if [ $? -eq 0 ]; then
        log"Incremental backup completed: ${BACKUP_PATH}"
        cat ${BACKUP_PATH}/xtrabackup_checkpoints | tee -a ${LOG_FILE}
    else
        log"Incremental backup FAILED!"
        exit 1
    fi
}

cleanup_old_backups() {
    log"Cleaning up old backups..."

    # 清理旧的全量备份
    find ${FULL_BACKUP_DIR} -maxdepth 1 -type d -mtime +${FULL_RETENTION_DAYS} -exec rm -rf {} \;

    # 清理旧的增量备份
    find ${INCR_BACKUP_DIR} -maxdepth 1 -type d -mtime +${INCR_RETENTION_DAYS} -exec rm -rf {} \;

    # 清理旧日志
    find ${LOG_DIR} -name "*.log" -mtime +${FULL_RETENTION_DAYS} -delete

    log"Cleanup completed"
}

# 主程序
case"$1"in
    full)
        do_full_backup
        ;;
    incremental|incr)
        do_incremental_backup
        ;;
    *)
        log"Usage: $0 [full|incremental]"
        exit 1
        ;;
esac

cleanup_old_backups

log"Backup script completed"

3.1.3 定时任务配置

# 编辑 crontab
crontab -e

# mysqldump 备份策略（小型数据库）
# 每天凌晨 2 点执行全备
0 2 * * * /backup/mysql/scripts/mysqldump_backup.sh full >> /backup/mysql/logs/cron.log 2>&1

# XtraBackup 备份策略（大型数据库）
# 每周日凌晨 2 点执行全备
0 2 * * 0 /backup/mysql/scripts/xtrabackup_backup.sh full >> /backup/mysql/logs/cron.log 2>&1
# 周一到周六凌晨 2 点执行增量
0 2 * * 1-6 /backup/mysql/scripts/xtrabackup_backup.sh incremental >> /backup/mysql/logs/cron.log 2>&1

# binlog 定期归档（每小时）
0 * * * * /backup/mysql/scripts/binlog_archive.sh >> /backup/mysql/logs/cron.log 2>&1

3.2 实际应用案例

3.2.1 案例一：误删数据恢复

场景：开发人员误执行 DELETE 语句，删除了 users 表中 today 的数据

-- 误操作 SQL
DELETEFROMusersWHERE created_at >= '2024-01-15';
-- 应该是：DELETE FROM users WHERE created_at >= '2024-01-15' AND status = 'inactive';

恢复步骤：

# 第一步：立即记录当前 binlog 位置
mysql -uroot -p -e "SHOW MASTER STATUS"
# File: mysql-bin.000150
# Position: 123456789

# 第二步：找到最近的备份
ls -la /backup/mysql/full/
# all_databases_20240115_020000.sql.gz

# 第三步：从备份恢复到临时库
# 解压备份
gunzip -c /backup/mysql/full/all_databases_20240115_020000.sql.gz > /tmp/restore.sql

# 创建临时库
mysql -uroot -p -e "CREATE DATABASE users_recovery"

# 只恢复 users 表
# 方法 1：使用 sed 提取特定表
sed -n '/^-- Table structure for table `users`/,/^-- Table structure for table/p' /tmp/restore.sql > /tmp/users_table.sql

# 方法 2：使用 awk 更精确地提取
awk '/^-- Table structure for table `users`/{f=1} f; /^UNLOCK TABLES;/{if(f) exit}' /tmp/restore.sql > /tmp/users_table.sql

# 导入到临时库
mysql -uroot -p users_recovery < /tmp/users_table.sql

# 第四步：确认备份时间点
grep "CHANGE MASTER TO" /tmp/restore.sql | head -1
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000145', MASTER_LOG_POS=789012;

# 第五步：应用 binlog 增量恢复
# 找到误删操作的位置
mysqlbinlog /data/mysql/binlog/mysql-bin.000145 mysql-bin.000146 mysql-bin.000147 mysql-bin.000148 mysql-bin.000149 \
    --start-position=789012 \
    --database=mydb \
    -v | grep -B5 -A5 "DELETE FROM.*users"

# 找到误删操作的位置后，应用 binlog 到误删之前
mysqlbinlog /data/mysql/binlog/mysql-bin.000145 mysql-bin.000146 mysql-bin.000147 mysql-bin.000148 \
    --start-position=789012 \
    --stop-position=误删操作位置 \
    --database=mydb | mysql -uroot -p users_recovery

# 第六步：将恢复的数据导回生产库
mysqldump -uroot -p users_recovery users | mysql -uroot -p mydb

3.2.2 案例二：时间点恢复（PITR）

场景：需要将数据库恢复到指定时间点（2024-01-15 14:30:00）

#!/bin/bash
# 文件：point_in_time_recovery.sh
# 功能：时间点恢复脚本

RECOVERY_TIME="2024-01-15 14:30:00"
BACKUP_DIR="/backup/mysql/full/20240115"
BINLOG_DIR="/data/mysql/binlog"
RECOVERY_DIR="/data/mysql_recovery"

# 第一步：准备备份
echo"Preparing backup..."
cp -r ${BACKUP_DIR}${RECOVERY_DIR}

# 解压压缩文件
cd${RECOVERY_DIR}
for f in *.qp; do
    qpress -d $f ./
done

# 准备备份
xtrabackup --prepare --target-dir=${RECOVERY_DIR}

# 第二步：获取备份的 binlog 位置
BINLOG_INFO=$(cat ${RECOVERY_DIR}/xtrabackup_binlog_info)
BINLOG_FILE=$(echo$BINLOG_INFO | awk '{print $1}')
BINLOG_POS=$(echo$BINLOG_INFO | awk '{print $2}')

echo"Backup binlog position: ${BINLOG_FILE}:${BINLOG_POS}"

# 第三步：找到需要应用的 binlog 文件
BINLOG_FILES=$(ls ${BINLOG_DIR}/mysql-bin.* | sort | awk -v start="${BINLOG_FILE}"'$0 >= start')

# 第四步：应用 binlog 到指定时间点
echo"Applying binlog until ${RECOVERY_TIME}..."
mysqlbinlog ${BINLOG_FILES} \
    --start-position=${BINLOG_POS} \
    --stop-datetime="${RECOVERY_TIME}" \
    > ${RECOVERY_DIR}/binlog_recovery.sql

# 第五步：恢复数据
echo"Stopping MySQL..."
systemctl stop mysqld

# 备份当前数据目录
mv /data/mysql/data /data/mysql/data_old_$(date +%Y%m%d_%H%M%S)

# 恢复备份
xtrabackup --copy-back --target-dir=${RECOVERY_DIR}
chown -R mysql:mysql /data/mysql/data

# 启动 MySQL
systemctl start mysqld

# 应用 binlog 增量
echo"Applying binlog recovery..."
mysql -uroot -p < ${RECOVERY_DIR}/binlog_recovery.sql

echo"Point-in-time recovery completed to ${RECOVERY_TIME}"

3.2.3 案例三：大表备份和恢复策略

场景：orders 表有 5 亿条数据，单表 500GB

# 备份策略：分区导出
#!/bin/bash
# 文件：large_table_backup.sh

TABLE="orders"
DB="ecommerce"
BACKUP_DIR="/backup/mysql/large_tables"
DATE=$(date +%Y%m%d)
PARALLEL_JOBS=4

mkdir -p ${BACKUP_DIR}/${DATE}

# 获取表的主键范围
MAX_ID=$(mysql -ubackup -p'BackupPass@2024' -N -e "SELECT MAX(id) FROM ${DB}.${TABLE}")
BATCH_SIZE=$((MAX_ID / PARALLEL_JOBS))

# 并行导出
for i in $(seq 1 ${PARALLEL_JOBS}); do
    START_ID=$(( (i-1) * BATCH_SIZE + 1 ))
    END_ID=$(( i * BATCH_SIZE ))

    if [ $i -eq ${PARALLEL_JOBS} ]; then
        END_ID=${MAX_ID}
    fi

    echo"Exporting ${TABLE} rows ${START_ID} to ${END_ID}..."

    mysqldump -ubackup -p'BackupPass@2024' \
        --single-transaction \
        --no-create-info \
        --where="id >= ${START_ID} AND id <= ${END_ID}" \
        ${DB}${TABLE} | gzip > ${BACKUP_DIR}/${DATE}/${TABLE}_part${i}.sql.gz &
done

# 等待所有任务完成
wait

# 导出表结构
mysqldump -ubackup -p'BackupPass@2024' \
    --no-data \
    ${DB}${TABLE} > ${BACKUP_DIR}/${DATE}/${TABLE}_schema.sql

echo"Large table backup completed"

# 恢复策略：并行导入
#!/bin/bash
# 文件：large_table_restore.sh

DB="ecommerce"
TABLE="orders"
BACKUP_DIR="/backup/mysql/large_tables/20240115"
PARALLEL_JOBS=4

# 先导入表结构
mysql -uroot -p ${DB} < ${BACKUP_DIR}/${TABLE}_schema.sql

# 禁用索引（加速导入）
mysql -uroot -p -e "ALTER TABLE ${DB}.${TABLE} DISABLE KEYS"

# 并行导入数据
for f in${BACKUP_DIR}/${TABLE}_part*.sql.gz; do
    echo"Importing ${f}..."
    gunzip -c $f | mysql -uroot -p ${DB} &

    # 控制并行数
    while [ $(jobs -r | wc -l) -ge ${PARALLEL_JOBS} ]; do
        sleep 1
    done
done

wait

# 重建索引
mysql -uroot -p -e "ALTER TABLE ${DB}.${TABLE} ENABLE KEYS"

echo"Large table restore completed"

四、最佳实践和注意事项

4.1 最佳实践

4.1.1 备份策略设计

小型数据库（<50GB）

备份策略：
- 工具：mysqldump
- 全备：每天凌晨
- binlog：实时归档
- 保留：7 天

恢复目标：
- RTO: 1 小时
- RPO: 1 小时

中型数据库（50-500GB）

备份策略：
- 工具：XtraBackup
- 全备：每周一次
- 增量：每天一次
- binlog：每小时归档
- 保留：全备 30 天，增量 7 天

恢复目标：
- RTO: 2 小时
- RPO: 1 小时

大型数据库（>500GB）

备份策略：
- 工具：XtraBackup 流式备份
- 全备：每周一次，流式传输到备份存储
- 增量：每天一次
- binlog：实时归档
- 异地备份：同步到灾备中心
- 保留：全备 60 天，增量 14 天

恢复目标：
- RTO: 4 小时
- RPO: 5 分钟

4.1.2 安全加固

# 1. 备份文件加密
# 使用 openssl 加密
mysqldump ... | gzip | openssl enc -aes-256-cbc -salt -pass pass:YourEncryptKey > backup.sql.gz.enc

# 解密
openssl enc -d -aes-256-cbc -pass pass:YourEncryptKey -in backup.sql.gz.enc | gunzip > backup.sql

# 2. XtraBackup 加密备份
xtrabackup --backup \
    --user=backup \
    --password='BackupPass@2024' \
    --target-dir=/backup/mysql/full/20240115 \
    --encrypt=AES256 \
    --encrypt-key-file=/etc/mysql/backup.key

# 3. 备份文件权限控制
chmod 600 /backup/mysql/full/*.sql.gz
chown mysql:mysql /backup/mysql/full/*.sql.gz

# 4. 备份目录访问控制
chmod 750 /backup/mysql
chown mysql:mysql /backup/mysql

4.1.3 高可用设计

# 1. 在从库执行备份，不影响主库性能
# XtraBackup 从库备份
xtrabackup --backup \
    --user=backup \
    --password='BackupPass@2024' \
    --target-dir=/backup/mysql/full/20240115 \
    --slave-info \       # 记录主库 binlog 位置
    --safe-slave-backup  # 安全从库备份

# 2. 备份到远程存储
# 流式备份到 S3
xtrabackup --backup \
    --user=backup \
    --password='BackupPass@2024' \
    --stream=xbstream \
    --compress | aws s3 cp - s3://backup-bucket/mysql/full_$(date +%Y%m%d).xbstream

# 3. 多地备份
# 本地 + NFS + 云存储

4.2 注意事项

4.2.1 常见错误

4.2.2 配置注意事项

-- 1. 确保 binlog 开启
SHOWVARIABLESLIKE'log_bin';
-- 必须为 ON

-- 2. binlog 格式
SHOWVARIABLESLIKE'binlog_format';
-- 推荐 ROW 格式

-- 3. GTID 配置
SHOWVARIABLESLIKE'gtid_mode';
-- 推荐开启 GTID

-- 4. binlog 保留时间
SHOWVARIABLESLIKE'binlog_expire_logs_seconds';
-- 设置合理的保留时间，确保能覆盖两个全备周期
SETGLOBAL binlog_expire_logs_seconds = 604800;  -- 7 天

4.2.3 恢复测试

#!/bin/bash
# 定期恢复测试脚本
# 建议：每周执行一次恢复演练

# 1. 选择最近的备份
LATEST_BACKUP=$(ls -t /backup/mysql/full/*.sql.gz | head -1)

# 2. 恢复到测试环境
# ...

# 3. 数据一致性验证
mysql -uroot -p -e "
SELECT
    table_schema,
    table_name,
    table_rows,
    data_length,
    index_length
FROM information_schema.TABLES
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY data_length DESC
LIMIT 20;
"

# 4. 记录恢复时间和结果
echo"Recovery test completed at $(date)" >> /backup/mysql/logs/recovery_test.log

五、故障排查和监控

5.1 故障排查

5.1.1 mysqldump 常见问题

# 问题 1：备份过程中断
# 原因：连接超时
# 解决：
mysqldump --net-buffer-length=16K \
    --max-allowed-packet=1G \
    --single-transaction \
    ...

# 问题 2：内存不足
# 原因：大表导出占用过多内存
# 解决：使用--quick 选项
mysqldump --quick ...

# 问题 3：锁等待超时
# 原因：备份期间有大事务
# 解决：使用--single-transaction，在低峰期执行

# 问题 4：导入失败
# 查看错误日志
mysql -uroot -p 2>&1 | tee import.log
# 检查外键约束
SET FOREIGN_KEY_CHECKS=0;
# 导入...
SET FOREIGN_KEY_CHECKS=1;

5.1.2 XtraBackup 常见问题

# 问题 1：版本不兼容
xtrabackup: Error: unsupported server version
# 解决：升级 XtraBackup 到匹配版本

# 问题 2：磁盘空间不足
xtrabackup: Error: Failed to write to file
# 解决：清理空间或使用流式备份

# 问题 3：prepare 失败
xtrabackup: error: applying incremental backup
# 原因：增量备份链断裂
# 解决：重新从全备恢复

# 问题 4：复制信息丢失
# 解决：使用--slave-info 选项
cat backup_dir/xtrabackup_slave_info

5.2 性能监控

5.2.1 备份监控指标

5.2.2 监控脚本

#!/bin/bash
# 文件：/backup/mysql/scripts/backup_monitor.sh
# 功能：备份监控和告警

BACKUP_DIR="/backup/mysql/full"
ALERT_URL="https://alert.example.com/api/alert"
MAX_BACKUP_AGE=86400  # 24 小时

# 检查最近备份时间
latest_backup=$(ls -t ${BACKUP_DIR}/*.sql.gz 2>/dev/null | head -1)

if [ -z "$latest_backup" ]; then
    echo"No backup found!"
    curl -X POST ${ALERT_URL} -d '{"level":"critical","message":"No MySQL backup found"}'
    exit 1
fi

backup_time=$(stat -c %Y "$latest_backup")
current_time=$(date +%s)
backup_age=$((current_time - backup_time))

if [ $backup_age -gt $MAX_BACKUP_AGE ]; then
    echo"Backup is too old: ${backup_age} seconds"
    curl -X POST ${ALERT_URL} -d "{\"level\":\"warning\",\"message\":\"MySQL backup is ${backup_age}s old\"}"
fi

# 检查备份大小趋势
current_size=$(stat -c %s "$latest_backup")
# 与上次备份比较...

echo"Backup monitoring completed"

5.3 备份与恢复

5.3.1 灾难恢复演练

#!/bin/bash
# 文件：disaster_recovery_drill.sh
# 功能：灾难恢复演练脚本

echo"=== MySQL Disaster Recovery Drill ==="
echo"Start time: $(date)"

# 模拟场景：主库完全宕机，需要从备份恢复

# 1. 记录当前状态
mysql -uroot -p -e "SELECT COUNT(*) FROM mydb.orders" > /tmp/pre_drill_count.txt

# 2. 停止 MySQL
systemctl stop mysqld

# 3. 备份当前数据目录（安全起见）
mv /data/mysql/data /data/mysql/data_drill_backup

# 4. 恢复最新备份
LATEST_BACKUP=$(ls -td /backup/mysql/full/*/ | head -1)
xtrabackup --prepare --target-dir=${LATEST_BACKUP}
xtrabackup --copy-back --target-dir=${LATEST_BACKUP}
chown -R mysql:mysql /data/mysql/data

# 5. 启动 MySQL
systemctl start mysqld

# 6. 应用 binlog（如需要）
# ...

# 7. 验证数据
mysql -uroot -p -e "SELECT COUNT(*) FROM mydb.orders" > /tmp/post_drill_count.txt

# 8. 比较结果
echo"Pre-drill count:"
cat /tmp/pre_drill_count.txt
echo"Post-drill count:"
cat /tmp/post_drill_count.txt

# 9. 恢复原数据（演练环境）
# systemctl stop mysqld
# rm -rf /data/mysql/data
# mv /data/mysql/data_drill_backup /data/mysql/data
# systemctl start mysqld

echo"=== Drill completed ==="
echo"End time: $(date)"

六、总结

6.1 技术要点回顾

1. mysqldump 适合小型数据库：简单可靠，支持跨版本，但速度较慢；
2. XtraBackup 适合大型数据库：热备份、增量支持、速度快；
3. 备份策略要完整：全备+增量+binlog，形成完整的恢复链；
4. 定期演练恢复：备份的价值在于能成功恢复，定期测试必不可少；
5. 监控和告警：备份任务必须有监控，失败及时告警。

6.2 进阶学习方向

• MySQL Clone Plugin：MySQL 8.0+原生克隆功能；
• MySQL Enterprise Backup：官方企业备份工具；
• mydumper/myloader：高性能并行备份工具；
• Percona Server 备份加密：数据安全保护；
• 云原生备份方案：Kubernetes 环境备份策略。

6.3 参考资料

• MySQL 8.0 mysqldump Documentation
• Percona XtraBackup Documentation
• MySQL Backup and Recovery
• Percona MySQL Backup Best Practices

附录

A. 命令速查表

B. 配置参数详解

以上关于MySQL 备份恢复实战 mysqldump 与 XtraBackup 用法 生产备份策略的文章就介绍到这了，更多相关内容请搜索码云笔记以前的文章或继续浏览下面的相关文章，希望大家以后多多支持码云笔记。