MySQL備份恢復(fù)策略：mysqldump、XtraBackup與binlog實(shí)戰(zhàn)

一、概述

1.1 背景介紹

備份是數(shù)據(jù)庫運(yùn)維中最重要也最容易被忽視的環(huán)節(jié)。"重要"體現(xiàn)在數(shù)據(jù)丟失時(shí)備份是唯一的救命稻草，"忽視"體現(xiàn)在很多團(tuán)隊(duì)有備份腳本但從未做過恢復(fù)演練，等到真正需要恢復(fù)時(shí)才發(fā)現(xiàn)備份文件損壞或恢復(fù)流程不熟悉。

MySQL 的備份策略需要在 RTO（恢復(fù)時(shí)間目標(biāo)）、RPO（恢復(fù)點(diǎn)目標(biāo)）和備份成本之間做權(quán)衡。沒有一種方案能同時(shí)滿足"備份快、恢復(fù)快、存儲(chǔ)小"，選型時(shí)必須明確業(yè)務(wù)對(duì)這三個(gè)維度的優(yōu)先級(jí)。

1.2 技術(shù)特點(diǎn)

mysqldump：邏輯備份，SQL 文本格式，可讀性強(qiáng)，跨版本兼容，適合小庫

XtraBackup：物理備份，直接復(fù)制 InnoDB 數(shù)據(jù)文件，速度快，支持增量，適合大庫

binlog：增量日志，配合全量備份實(shí)現(xiàn) PITR（Point-in-Time Recovery），將 RPO 降到分鐘級(jí)

1.3 適用場景

mysqldump：數(shù)據(jù)庫總量 < 50GB，需要跨版本遷移，或需要備份特定表

XtraBackup：數(shù)據(jù)庫總量 > 50GB，需要快速恢復(fù)，或需要搭建從庫

binlog PITR：對(duì) RPO 要求嚴(yán)格（< 5 分鐘），需要恢復(fù)到任意時(shí)間點(diǎn)

1.4 環(huán)境要求

二、詳細(xì)步驟

2.1 三種備份方式對(duì)比

2.2 mysqldump 配置與使用

2.2.1 核心參數(shù)詳解

# 生產(chǎn)環(huán)境標(biāo)準(zhǔn)備份命令
mysqldump 
 --host=127.0.0.1 
 --port=3306 
 --user=backup_user 
 --password="${BACKUP_PASS}"
 --single-transaction     # InnoDB 一致性快照，不鎖表（關(guān)鍵參數(shù)）
 --master-data=2       # 記錄 binlog position，注釋形式寫入備份文件
 --source-data=2       # 8.4 中替代 --master-data
 --flush-logs         # 備份前切換 binlog，便于后續(xù) PITR
 --hex-blob          # BLOB 字段用十六進(jìn)制，避免字符集問題
 --routines          # 備份存儲(chǔ)過程和函數(shù)
 --triggers          # 備份觸發(fā)器
 --events           # 備份定時(shí)事件
 --compress          # 客戶端與服務(wù)端傳輸壓縮
 --databases production_db 
 | gzip -9 > /backup/mysql/production_db_$(date +%Y%m%d_%H%M%S).sql.gz

2.2.2 備份賬號(hào)權(quán)限

-- 創(chuàng)建最小權(quán)限備份賬號(hào)
CREATEUSER'backup_user'@'127.0.0.1'
IDENTIFIEDWITHcaching_sha2_passwordBY'BackupPass@2024';

GRANTSELECT,SHOWVIEW,TRIGGER,LOCKTABLES,
  REPLICATIONCLIENT, PROCESS,EVENT
ON*.*TO'backup_user'@'127.0.0.1';

-- 8.4 中 REPLICATION CLIENT 已改名為 REPLICATION_CLIENT_ADMIN
-- 但舊名稱仍兼容
FLUSHPRIVILEGES;

2.2.3 恢復(fù)流程

# 解壓并恢復(fù)
gunzip < /backup/mysql/production_db_20240115_020000.sql.gz 
? | mysql -u root -p production_db

# 查看備份文件中的 binlog position（用于 PITR）
zcat /backup/mysql/production_db_20240115_020000.sql.gz 
? | grep?"CHANGE MASTER|CHANGE REPLICATION SOURCE"?| head -5
# 輸出示例：
# -- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='mysql-bin.000123', SOURCE_LOG_POS=4567890;

2.3 XtraBackup 全量 + 增量備份

2.3.1 安裝 XtraBackup 8.4

# Ubuntu 22.04
wget https://downloads.percona.com/downloads/percona-xtrabackup-8.4/
percona-xtrabackup-8.4.0-1/binary/debian/jammy/x86_64/
percona-xtrabackup-84_8.4.0-1.jammy_amd64.deb

dpkg -i percona-xtrabackup-84_8.4.0-1.jammy_amd64.deb
apt-get install -f # 安裝依賴

2.3.2 全量備份

# 全量備份
xtrabackup 
 --backup 
 --user=backup_user 
 --password="${BACKUP_PASS}"
 --host=127.0.0.1 
 --target-dir=/backup/xtrabackup/full_$(date +%Y%m%d) 
 --compress          # 壓縮備份文件
 --compress-threads=4     # 壓縮并行線程數(shù)
 --parallel=4         # 備份并行線程數(shù)
 --slave-info         # 如果是從庫，記錄主庫 binlog position
 --safe-slave-backup     # 從庫備份時(shí)暫停復(fù)制，確保一致性

# 備份完成后準(zhǔn)備（prepare）階段
# prepare 階段將 redo log 應(yīng)用到數(shù)據(jù)文件，使備份達(dá)到一致狀態(tài)
xtrabackup 
 --prepare 
 --target-dir=/backup/xtrabackup/full_20240115

2.3.3 增量備份

# 基于全量備份做第一次增量
xtrabackup 
 --backup 
 --user=backup_user 
 --password="${BACKUP_PASS}"
 --target-dir=/backup/xtrabackup/inc_$(date +%Y%m%d_%H) 
 --incremental-basedir=/backup/xtrabackup/full_20240115 
 --compress 
 --compress-threads=4 
 --parallel=4

# 基于上一次增量做第二次增量
xtrabackup 
 --backup 
 --user=backup_user 
 --password="${BACKUP_PASS}"
 --target-dir=/backup/xtrabackup/inc_20240115_12 
 --incremental-basedir=/backup/xtrabackup/inc_20240115_06 
 --compress 
 --compress-threads=4

2.3.4 增量備份恢復(fù)流程

# 步驟 1：準(zhǔn)備全量備份（不應(yīng)用 redo log，等待增量合并）
xtrabackup --prepare --apply-log-only 
 --target-dir=/backup/xtrabackup/full_20240115

# 步驟 2：合并第一個(gè)增量到全量
xtrabackup --prepare --apply-log-only 
 --target-dir=/backup/xtrabackup/full_20240115 
 --incremental-dir=/backup/xtrabackup/inc_20240115_06

# 步驟 3：合并最后一個(gè)增量（去掉 --apply-log-only，應(yīng)用 redo log）
xtrabackup --prepare 
 --target-dir=/backup/xtrabackup/full_20240115 
 --incremental-dir=/backup/xtrabackup/inc_20240115_12

# 步驟 4：停止 MySQL，恢復(fù)數(shù)據(jù)文件
systemctl stop mysql
mv /var/lib/mysql /var/lib/mysql.bak
xtrabackup --copy-back 
 --target-dir=/backup/xtrabackup/full_20240115
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

2.4 binlog PITR（基于時(shí)間點(diǎn)恢復(fù)）

2.4.1 前提條件

# MySQL 必須開啟 binlog
[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 0      # 不自動(dòng)刪除，由備份腳本管理
binlog_expire_logs_seconds = 604800 # 7 天，8.4 推薦用這個(gè)參數(shù)

2.4.2 PITR 恢復(fù)流程

# 場景：全量備份時(shí)間 2024-01-15 0200，誤操作發(fā)生在 1000
# 目標(biāo)：恢復(fù)到 1059

# 步驟 1：恢復(fù)全量備份（mysqldump 方式）
gunzip < /backup/mysql/production_db_20240115_020000.sql.gz 
? | mysql -u root -p production_db

# 步驟 2：從備份文件獲取 binlog position
BINLOG_FILE="mysql-bin.000123"
BINLOG_POS=4567890

# 步驟 3：應(yīng)用 binlog 到誤操作前一刻
mysqlbinlog 
? --start-position=${BINLOG_POS}?
? --stop-datetime="2024-01-15 1059"?
? --database=production_db 
? /var/log/mysql/mysql-bin.000123 
? /var/log/mysql/mysql-bin.000124 
? | mysql -u root -p production_db

# 如果需要跳過某個(gè)誤操作事務(wù)（已知 GTID）
mysqlbinlog 
? --start-position=${BINLOG_POS}?
? --stop-datetime="2024-01-15 1059"?
? --exclude-gtids="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:12345"?
? /var/log/mysql/mysql-bin.000123 
? | mysql -u root -p production_db

三、示例代碼和配置

3.1 自動(dòng)化備份腳本

#!/bin/bash
# 文件名：mysql-backup.sh
# 功能：MySQL 全量備份 + binlog 歸檔，支持本地和遠(yuǎn)程存儲(chǔ)

set-euo pipefail

# 配置
MYSQL_USER="backup_user"
MYSQL_PASS="${MYSQL_BACKUP_PASS}"# 從環(huán)境變量讀取，不硬編碼
MYSQL_HOST="127.0.0.1"
BACKUP_BASE="/backup/mysql"
BINLOG_DIR="/var/log/mysql"
REMOTE_BUCKET="s3://company-mysql-backup"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/mysql-backup.log"

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

# 全量備份
full_backup() {
 localbackup_file="${BACKUP_BASE}/full_${DATE}.sql.gz"
 log"開始全量備份:${backup_file}"

  mysqldump 
    --host="${MYSQL_HOST}"
    --user="${MYSQL_USER}"
    --password="${MYSQL_PASS}"
    --single-transaction 
    --source-data=2 
    --flush-logs 
    --hex-blob 
    --routines 
    --triggers 
    --events 
    --all-databases 
    2>>"${LOG_FILE}"
    | gzip -6 >"${backup_file}"

 localsize
  size=$(du -sh"${backup_file}"| cut -f1)
 log"全量備份完成，大小:${size}"

 # 上傳到對(duì)象存儲(chǔ)
  aws s3 cp"${backup_file}""${REMOTE_BUCKET}/full/"
    --storage-class STANDARD_IA 
    2>>"${LOG_FILE}"
 log"備份已上傳到 S3"
}

# binlog 歸檔
archive_binlog() {
 log"開始?xì)w檔 binlog"
 # 切換到新的 binlog 文件
  mysql --host="${MYSQL_HOST}"
     --user="${MYSQL_USER}"
     --password="${MYSQL_PASS}"
     -e"FLUSH BINARY LOGS;"2>>"${LOG_FILE}"

 # 獲取當(dāng)前 binlog 文件列表（排除最新的，它還在寫入）
 localcurrent_binlog
  current_binlog=$(mysql --host="${MYSQL_HOST}"
             --user="${MYSQL_USER}"
             --password="${MYSQL_PASS}"
             -sNe"SHOW MASTER STATUS"2>>"${LOG_FILE}"| awk'{print $1}')

 # 上傳除當(dāng)前文件外的所有 binlog
 forbinlogin"${BINLOG_DIR}"/mysql-bin.[0-9]*;do
   localfilename
    filename=$(basename"${binlog}")
   if[["${filename}"!="${current_binlog}"]];then
      aws s3 cp"${binlog}""${REMOTE_BUCKET}/binlog/"
        --storage-class STANDARD_IA 
        2>>"${LOG_FILE}"&& 
     log"已歸檔:${filename}"
   fi
 done
}

# 清理過期備份
cleanup_old_backups() {
 log"清理${RETENTION_DAYS}天前的本地備份"
  find"${BACKUP_BASE}"-name"full_*.sql.gz"
    -mtime"+${RETENTION_DAYS}"-delete
}

# 備份驗(yàn)證（隨機(jī)抽查）
verify_backup() {
 locallatest_backup
  latest_backup=$(ls -t"${BACKUP_BASE}"/full_*.sql.gz | head -1)
 log"驗(yàn)證備份文件:${latest_backup}"

 # 檢查文件完整性
 ifgzip -t"${latest_backup}"2>>"${LOG_FILE}";then
   log"備份文件完整性驗(yàn)證通過"
 else
   log"ERROR: 備份文件損壞！"
   exit1
 fi

 # 檢查備份文件包含關(guān)鍵表
 ifzcat"${latest_backup}"| grep -q"CREATE TABLE.*orders";then
   log"關(guān)鍵表驗(yàn)證通過"
 else
   log"ERROR: 備份文件缺少關(guān)鍵表！"
   exit1
 fi
}

main() {
  mkdir -p"${BACKUP_BASE}"
 log"=== MySQL 備份開始 ==="
  full_backup
  archive_binlog
  verify_backup
  cleanup_old_backups
 log"=== MySQL 備份完成 ==="
}

main"$@"

3.2 恢復(fù)演練腳本

#!/bin/bash
# 文件名：mysql-restore-test.sh
# 功能：在測試環(huán)境驗(yàn)證備份可恢復(fù)性

BACKUP_FILE=$1
TEST_DB="restore_test_$(date +%Y%m%d)"
MYSQL_ROOT_PASS="${MYSQL_ROOT_PASS}"

if[[ -z"${BACKUP_FILE}"]];then
 echo"用法:$0"
 exit1
fi

echo"創(chuàng)建測試數(shù)據(jù)庫:${TEST_DB}"
mysql -u root -p"${MYSQL_ROOT_PASS}"-e"CREATE DATABASE${TEST_DB};"

echo"開始恢復(fù)備份..."
time gunzip 

	

	四、最佳實(shí)踐和注意事項(xiàng)

	4.1 最佳實(shí)踐

	4.1.1 備份策略設(shè)計(jì)

	根據(jù) RTO/RPO 目標(biāo)選擇備份組合：

				業(yè)務(wù)級(jí)別
			

				RPO 目標(biāo)
			

				RTO 目標(biāo)
			

				推薦策略
		

				核心交易
			

				< 5 分鐘
			

				< 30 分鐘
			

				XtraBackup 每日全量 + binlog 實(shí)時(shí)歸檔
		

				一般業(yè)務(wù)
			

				< 1 小時(shí)
			

				< 2 小時(shí)
			

				mysqldump 每日全量 + binlog 每小時(shí)歸檔
		

				非關(guān)鍵數(shù)據(jù)
			

				< 24 小時(shí)
			

				< 4 小時(shí)
			

				mysqldump 每日全量
		

	4.1.2 3-2-1 備份原則

	3 份備份：原始數(shù)據(jù) + 本地備份 + 遠(yuǎn)程備份

	2 種介質(zhì)：本地磁盤 + 對(duì)象存儲(chǔ)（S3/OSS）

	1 份異地：備份存儲(chǔ)在不同地域，防止機(jī)房級(jí)故障

	4.1.3 備份監(jiān)控

	
# 檢查備份是否按時(shí)完成（Prometheus 告警規(guī)則）
# 如果最新備份文件超過 25 小時(shí)未更新，觸發(fā)告警
find /backup/mysql -name"full_*.sql.gz"-mmin -1500 | wc -l
# 結(jié)果為 0 時(shí)說明備份失敗，需要告警


	

	4.2 注意事項(xiàng)

	4.2.1 常見錯(cuò)誤

	警告：以下錯(cuò)誤在生產(chǎn)環(huán)境中曾導(dǎo)致數(shù)據(jù)無法恢復(fù)。

	mysqldump 不加--single-transaction會(huì)鎖表，對(duì) InnoDB 表必須加此參數(shù)

	XtraBackup 版本必須與 MySQL 版本嚴(yán)格匹配，8.0 的 XtraBackup 無法備份 8.4 的 MySQL

	binlog 必須在全量備份完成后才能刪除，否則 PITR 會(huì)有空洞

	4.2.2 常見錯(cuò)誤排查

				錯(cuò)誤現(xiàn)象
			

				原因分析
			

				解決方案
		

				mysqldump 卡住不動(dòng)
			

				有長事務(wù)持有表鎖
			

				SHOW PROCESSLIST 找到長事務(wù)并 KILL
		

				XtraBackup 報(bào) "redo log archiving" 錯(cuò)誤
			

				MySQL 8.4 新特性沖突
			

				升級(jí) XtraBackup 到對(duì)應(yīng)版本
		

				binlog 恢復(fù)后數(shù)據(jù)不一致
			

				binlog position 記錄錯(cuò)誤
			

				使用 GTID 模式替代 position 模式
		

				備份文件 gzip 損壞
			

				磁盤空間不足導(dǎo)致寫入中斷
			

				備份前檢查磁盤空間，設(shè)置告警閾值 80%
		

				恢復(fù)速度極慢
			

				innodb_buffer_pool_size 太小
			

				恢復(fù)時(shí)臨時(shí)調(diào)大 buffer pool
		

	五、故障排查和監(jiān)控

	5.1 備份失敗排查

	5.1.1 常見問題排查

	問題一：mysqldump 連接超時(shí)

	
# 診斷：檢查 MySQL 連接狀態(tài)
mysql -u root -p -e"SHOW STATUS LIKE 'Threads_connected';"
mysql -u root -p -e"SHOW PROCESSLIST;"| grep -v Sleep

# 解決：增加超時(shí)參數(shù)
mysqldump --net-read-timeout=3600 --net-write-timeout=3600 ...


	

	問題二：XtraBackup 備份中斷

	
# 查看 XtraBackup 日志
tail -100 /backup/xtrabackup/full_20240115/xtrabackup_info

# 常見原因：磁盤空間不足
df -h /backup/

# 檢查 MySQL 錯(cuò)誤日志
tail -50 /var/log/mysql/error.log


	

	問題三：binlog 文件缺失

	
-- 查看當(dāng)前 binlog 列表
SHOWBINARYLOGS;

-- 查看 binlog 中的事件（驗(yàn)證內(nèi)容）
SHOWBINLOGEVENTSIN'mysql-bin.000123'LIMIT20;

-- 如果 binlog 已被清理，檢查 expire 配置
SHOWVARIABLESLIKE'binlog_expire_logs_seconds';


	

	5.2 性能監(jiān)控

	5.2.1 備份性能指標(biāo)

	
# 監(jiān)控備份速度（MB/s）
iostat -x 1 | grep -E"Device|sda"

# 監(jiān)控備份進(jìn)度（XtraBackup）
# XtraBackup 會(huì)輸出已處理的數(shù)據(jù)量
tail -f /var/log/xtrabackup.log | grep"MB/s"

# 監(jiān)控 mysqldump 進(jìn)度
# 通過 performance_schema 查看當(dāng)前執(zhí)行的 SQL
mysql -e"SELECT * FROM performance_schema.processlist WHERE user='backup_user'G"


	

	5.2.2 監(jiān)控指標(biāo)說明

				指標(biāo)名稱
			

				正常范圍
			

				告警閾值
			

				說明
		

				備份完成時(shí)間
			

				< 4 小時(shí)
			

				> 6 小時(shí)
			

				超時(shí)說明數(shù)據(jù)量增長或性能下降
		

				備份文件大小變化
			

				±20%
			

				> 50% 增長
			

				突增可能是數(shù)據(jù)異常
		

				binlog 生成速率
			

				業(yè)務(wù)基線
			

				基線 3x
			

				突增可能是大批量寫入
		

				磁盤使用率
			

				< 70%
			

				> 80%
			

				備份磁盤空間告警
		

	5.3 備份與恢復(fù)

	5.3.1 定期恢復(fù)演練

	
# 每月執(zhí)行一次恢復(fù)演練，驗(yàn)證備份可用性
# 在測試環(huán)境執(zhí)行，記錄恢復(fù)時(shí)間

# 1. 從 S3 下載最新備份
aws s3 cp s3://company-mysql-backup/full/full_latest.sql.gz /tmp/

# 2. 記錄開始時(shí)間
START_TIME=$(date +%s)

# 3. 執(zhí)行恢復(fù)
gunzip < /tmp/full_latest.sql.gz | mysql -u root -p test_restore_db

# 4. 計(jì)算恢復(fù)時(shí)間
END_TIME=$(date +%s)
echo?"恢復(fù)耗時(shí):?$((END_TIME - START_TIME)) 秒"

# 5. 驗(yàn)證數(shù)據(jù)
mysql -u root -p test_restore_db -e?"
? SELECT COUNT(*) as order_count FROM orders;
? SELECT MAX(created_at) as latest_order FROM orders;
"


	

	六、總結(jié)

	6.1 技術(shù)要點(diǎn)回顧

	方案選型：50GB 以下用 mysqldump，以上用 XtraBackup，兩者都需要配合 binlog 實(shí)現(xiàn) PITR

	備份驗(yàn)證：備份完成后必須驗(yàn)證文件完整性，每月做一次完整恢復(fù)演練

	3-2-1 原則：本地 + 遠(yuǎn)程雙份存儲(chǔ)，防止單點(diǎn)故障

	RTO/RPO 對(duì)齊：備份策略必須與業(yè)務(wù)的恢復(fù)目標(biāo)對(duì)齊，不能憑感覺設(shè)計(jì)

	6.2 進(jìn)階學(xué)習(xí)方向

	MySQL InnoDB Cluster 內(nèi)置備份：MySQL Shell 的util.dumpInstance()是 mysqldump 的現(xiàn)代替代品，支持并行導(dǎo)出，速度提升 10 倍以上

	Percona XtraBackup 流式備份：直接通過網(wǎng)絡(luò)流傳輸?shù)絺浞莘?wù)器，無需本地中轉(zhuǎn)

	備份加密：使用--encrypt參數(shù)對(duì)備份文件加密，滿足合規(guī)要求

	6.3 參考資料

	XtraBackup 8.4 官方文檔

	MySQL 8.4 備份恢復(fù)文檔

	MySQL Shell Dump & Load

	附錄

	A. 命令速查表

	
# mysqldump 全庫備份
mysqldump --single-transaction --source-data=2 --all-databases | gzip > backup.sql.gz

# XtraBackup 全量備份
xtrabackup --backup --target-dir=/backup/full --compress --parallel=4

# XtraBackup prepare
xtrabackup --prepare --target-dir=/backup/full

# 查看 binlog 內(nèi)容
mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000123 | head -100

# PITR 恢復(fù)到指定時(shí)間
mysqlbinlog --start-position=4567890 --stop-datetime="2024-01-15 1059"mysql-bin.000123 | mysql -u root -p

# 查看備份文件中的 binlog position
zcat backup.sql.gz | grep"CHANGE REPLICATION SOURCE"


	

	B. 配置參數(shù)詳解

				參數(shù)
			

				推薦值
			

				說明
		

				binlog_expire_logs_seconds
			

				604800
			

				binlog 保留 7 天
		

				sync_binlog
			

				1
			

				每次提交刷盤，防止 binlog 丟失
		

				innodb_flush_log_at_trx_commit
			

				1
			

				事務(wù)提交時(shí)刷 redo log
		

				max_binlog_size
			

				1G
			

				單個(gè) binlog 文件最大 1GB
		

	C. 術(shù)語表

				術(shù)語
			

				英文
			

				解釋
		

				邏輯備份
			

				Logical Backup
			

				以 SQL 語句形式備份，可讀性強(qiáng)
		

				物理備份
			

				Physical Backup
			

				直接復(fù)制數(shù)據(jù)文件，速度快
		

				增量備份
			

				Incremental Backup
			

				只備份上次備份后變化的數(shù)據(jù)
		

				PITR
			

				Point-in-Time Recovery
			

				基于時(shí)間點(diǎn)恢復(fù)，精確到秒
		

				RTO
			

				Recovery Time Objective
			

				恢復(fù)時(shí)間目標(biāo)，故障到恢復(fù)的最長時(shí)間
		

				RPO
			

				Recovery Point Objective
			

				恢復(fù)點(diǎn)目標(biāo)，最多丟失多少數(shù)據(jù)