社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
Py学习  »  DATABASE

MySQL备份恢复全攻略:mysqldump与xtrabackup实战

马哥Linux运维 • 10 月前 • 389 次点击  

一、概述

1.1 背景介绍

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

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

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

MySQL备份主要有两大工具:

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

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

1.2 技术特点

mysqldump特点

特性
说明
备份类型
逻辑备份(导出SQL语句)
锁机制
默认锁表,可用--single-transaction实现一致性读
备份速度
较慢,需要查询所有数据
恢复速度
较慢,需要执行SQL语句
适用场景
小型数据库(<50GB)、跨版本迁移、表级恢复
优点
简单、可读、跨平台、跨版本
缺点
大数据量时速度慢

Percona XtraBackup特点

特性
说明
备份类型
物理备份(复制数据文件)
锁机制
几乎无锁(InnoDB热备)
备份速度
快,直接复制文件
恢复速度
快,直接替换数据目录
适用场景
大型数据库(>50GB)、要求低影响
优点
快速、低影响、支持增量备份
缺点
只支持同版本恢复、配置较复杂

1.3 适用场景

场景
推荐工具
备份策略
开发测试环境
mysqldump
每日全备
小型生产(<50GB)
mysqldump
每日全备 + binlog
中型生产(50-500GB)
XtraBackup
每周全备 + 每日增量
大型生产(>500GB)
XtraBackup
每周全备 + 每日增量 + 流式备份
跨版本迁移
mysqldump
逻辑导出导入
表级恢复
mysqldump
单表导出
时间点恢复(PITR)
XtraBackup + binlog
全备 + binlog应用

1.4 环境要求

组件
版本要求
说明
MySQL Server
8.0.35+ 或 8.4 LTS

Percona XtraBackup
8.0.35+
版本需与MySQL匹配
操作系统
Rocky 9 / Ubuntu 24.04

磁盘空间
数据量的2-3倍
备份+压缩+恢复空间
qpress
最新版
XtraBackup压缩支持

二、详细步骤

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

参数说明表:

参数
说明
推荐
--single-transaction
InnoDB一致性读,备份期间不锁表
必须
--source-data=2
记录binlog位置,2表示注释形式
推荐
--routines
包含存储过程和函数
推荐
--triggers
包含触发器
推荐
--events
包含事件
推荐
--set-gtid-purged
GTID处理:OFF/ON/AUTO
AUTO
--quick
大表逐行读取,节省内存
推荐
--lock-tables
锁定所有表(MyISAM)
非InnoDB时
--flush-logs
备份前刷新binlog
按需
--hex-blob
BLOB字段十六进制导出
推荐
--compress
压缩传输(远程备份)
远程时

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
DELETE FROM users WHERE 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 常见错误

错误场景
原因
解决方案
mysqldump导出乱码
字符集不匹配
添加--default-character-set=utf8mb4
XtraBackup备份失败
版本不匹配
XtraBackup版本需与MySQL匹配
恢复后数据不完整
binlog未应用
确保应用所有增量binlog
备份文件损坏
传输错误或存储问题
启用校验和验证
恢复后权限丢失
未备份mysql库
使用--all-databases

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 备份监控指标

指标
说明
告警阈值
备份持续时间
备份执行耗时
超过预期1.5倍
备份大小
备份文件大小
异常增长>20%
备份成功率
备份成功次数/总次数
<100%
备份延迟
上次成功备份距今时间
>设定周期
binlog归档延迟
binlog归档滞后
>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. 命令速查表

命令
说明
mysqldump -u user -p --all-databases
备份所有数据库
mysqldump --single-transaction
一致性读备份
mysqldump --source-data=2
记录binlog位置
xtrabackup --backup
全量备份
xtrabackup --backup --incremental-basedir=
增量备份
xtrabackup --prepare
准备备份
xtrabackup --copy-back
恢复备份
mysqlbinlog --start-position= --stop-position=
binlog解析

B. 配置参数详解

参数
默认值
说明
--single-transaction
OFF
InnoDB一致性读
--source-data
0
记录binlog位置(0/1/2)
--routines
OFF
包含存储过程
--triggers
ON
包含触发器
--events
OFF
包含事件
--compress
OFF
压缩备份
--parallel
1
并行线程数
--stream
-
流式输出格式

C. 术语表

术语
英文
说明
全量备份
Full Backup
完整数据备份
增量备份
Incremental Backup
只备份变化数据
差异备份
Differential Backup
相对全备的变化
物理备份
Physical Backup
复制数据文件
逻辑备份
Logical Backup
导出SQL语句
热备份
Hot Backup
运行时备份
RTO
Recovery Time Objective
恢复时间目标
RPO
Recovery Point Objective
恢复点目标
PITR
Point-In-Time Recovery
时间点恢复

文末福利


网络监控是保障网络系统和数据安全的重要手段,能够帮助运维人员及时发现并应对各种问题,及时发现并解决,从而确保网络的顺畅运行。

谢谢一路支持,给大家分享6款开源免费的网络监控工具,并准备了对应的资料文档,建议运维工程师收藏(文末一键领取)。

图片
备注:【监控合集】
图片


100%免费领取


一、zabbix

图片
图片

二、Prometheus


图片

内容较多,6款常用网络监控工具(zabbix、Prometheus、Cacti、Grafana、OpenNMS、Nagios )不再一一介绍, 需要的朋友扫码备注【监控合集】,即可100%免费领取。

图片

 以上所有资料获取请扫码

备注:【监控合集】
图片


100%免费领取

(后台不再回复,扫码一键领取)


Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/191693