一、概述
1.1 背景介绍
数据库备份是DBA工作中最重要的任务之一,没有之一。
2018年某次事故至今记忆犹新:开发人员误执行了一条没有WHERE条件的DELETE语句,直接删除了用户表的全部数据。当时的备份策略只有每天凌晨一次全量备份,而事故发生在下午5点。最终通过binlog恢复数据,但整个过程耗时4小时,造成了严重的业务影响。
从那之后,备份策略从简单的每日全备,升级为"全量备份+增量备份+binlog归档"的完整方案,RTO(恢复时间目标)从4小时降低到30分钟,RPO(恢复点目标)从24小时降低到5分钟。
MySQL备份主要有两大工具:
- mysqldump:官方逻辑备份工具,简单可靠,适合小型数据库
- Percona XtraBackup:物理热备份工具,高效快速,适合大型数据库
本文将详细介绍这两种工具的使用方法、适用场景以及生产环境的最佳实践。
1.2 技术特点
mysqldump特点
| |
|---|
|
|
| 默认锁表,可用--single-transaction实现一致性读 |
| |
| |
| |
| |
|
|
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
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 常见错误
| | |
| | 添加--default-character-set=utf8mb4 |
| | |
| | |
| |
|
| | |
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 技术要点回顾
mysqldump适合小型数据库:简单可靠,支持跨版本,但速度较慢
XtraBackup适合大型数据库:热备份、增量支持、速度快
备份策略要完整:全备+增量+binlog,形成完整的恢复链
定期演练恢复:备份的价值在于能成功恢复,定期测试必不可少
6.2 进阶学习方向
- MySQL Clone Plugin:MySQL 8.0+原生克隆功能
- MySQL Enterprise Backup:官方企业备份工具
- mydumper/myloader:高性能并行备份工具
- Percona Server备份加密:数据安全保护
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 | |
xtrabackup --backup | |
xtrabackup --backup --incremental-basedir= | |
xtrabackup --prepare | |
xtrabackup --copy-back | |
mysqlbinlog --start-position= --stop-position= | |
B. 配置参数详解
C. 术语表