| 維度 | 邏輯備份 (mysqldump) | 物理備份 (文件拷貝/xtrabackup) |
|---|---|---|
| 原理 | 導出 SQL 文本（CREATE + INSERT） | 直接拷貝數據文件（.ibd, .frm, .MYD, .MYI） |
| 跨版本/架構遷移 | ✅ 支持（文本可編輯） | ❌ 僅限同版本、同架構、同存儲引擎 |
| 速度 | 慢（SQL 解析、索引重建） | 快（流式拷貝，無解析開銷） |
| 一致性 | --single-transaction 熱備 | 需鎖表或快照（LVM/ZFS） |
| 恢復粒度 | 庫/表/行級（文本編輯） | 通常為實例/庫級 |
| 壓縮 | 管道 gzip 易實現 | 需額外工具 |
log_bin=ON，配合 binlog_format=ROW。備份是「全量基準點」，Binlog 是「增量重放」，二者結合實現**任意時間點恢復 (PITR)**dbname_YYYYMMDD_HHMMSS.sql.gz，避免 backup1.sql 這類無意義名稱mysqldump 邏輯備份深度實戰mysqldump --single-transaction --master-data=2 --flush-logs \
--routines --triggers --events \
--hex-blob --quick --opt \
-u backup_user -p dbname | gzip > /backup/dbname_$(date +%F_%H%M%S).sql.gz
參數解讀：
--single-transaction：InnoDB 熱備，一致性快照，不阻塞寫入--master-data=2：輸出 CHANGE MASTER TO 註釋（含 Binlog 文件/位點），用於搭建從庫/PITR--flush-logs：輪轉 Binlog，使備份位點與新 Binlog 文件對齊--routines/triggers/events：導出存儲過程、觸發器、事件--hex-blob：二進制列十六進制導出，避免字符集問題--quick：流式讀取，不緩存全量結果集--opt：組合優化參數（--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset）# 獲取所有表名
tables=$(mysql -N -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='dbname'")
# 並行導出（需 GNU parallel 或 xargs -P）
echo "$tables" | xargs -I {} -P 4 sh -c 'mysqldump --single-transaction -u backup_user -p dbname {} | gzip > /backup/dbname_{}_$(date +%F).sql.gz'
# 只結構
mysqldump --no-data -u backup_user -p dbname > schema.sql
# 只數據
mysqldump --no-create-info -u backup_user -p dbname > data.sql
# 條件導出（如近 30 天訂單）
mysqldump --where="created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)" -u backup_user -p dbname orders > recent_orders.sql
xtrabackup` (Percona XtraBackup) 熱備首選--prepare 恢復）xtrabackup --backup --target-dir=/backup/full_$(date +%F) --user=backup_user --password=xxx --databases=dbname
# 基於上次全量/增量的 LSN
xtrabackup --backup --target-dir=/backup/inc_$(date +%F_%H) \
--incremental-basedir=/backup/full_20240101 --user=backup_user --password=xxx
# 1. 準備全量備份（回放事務日志，使數據一致）
xtrabackup --prepare --target-dir=/backup/full_20240101
# 2. 合併增量備份（依次應用）
xtrabackup --prepare --target-dir=/backup/full_20240101 --incremental-dir=/backup/inc_20240101_01
xtrabackup --prepare --target-dir=/backup/full_20240101 --incremental-dir=/backup/inc_20240101_02
# ...
# 3. 恢復數據目錄（需停庫）
xtrabackup --copy-back --target-dir=/backup/full_20240101 --datadir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
[mysqld]
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7 # 自動清理 7 天前 Binlog
max_binlog_size = 1G
sync_binlog = 1 # 每次提交同步刷盤（最安全）
binlog_rows_query_log_events = ON # 記錄原始 SQL（5.6+），利於審計
full_20240101.sql.gz）及其對應 Binlog 位點（--master-data=2 註釋中）zcat full_20240101.sql.gz | mysql -u root -p dbname# 查看 Binlog 事件
mysqlbinlog --start-position=12345 --stop-datetime="2024-01-02 10:30:00" mysql-bin.000001 > replay.sql
# 或管道直接重放
mysqlbinlog --start-position=12345 --stop-datetime="2024-01-02 10:30:00" mysql-bin.000001 | mysql -u root -p dbname
# 停寫入（維護窗口）
# 恢復全量
mysql -u root -p dbname < full_backup.sql
# 重放 Binlog 到故障點
mysqlbinlog --start-position=XXX mysql-bin.00000X | mysql -u root -p dbname
sed/awk 或 mysqldump 重導）mysqldump 導出單表 → 導入生產庫mysqlbinlog --database=dbname --table=table_name 過濾，5.6+ 支持）DROP TABLE/DELETE 緊急恢復read_only=ON）mysqlbinlog --base64-output=DECODE-ROWS -v）# 目標機創建庫
mysqladmin -h target_host -u root -p create dbname
# 源機管道傳輸（壓縮加速）
mysqldump --single-transaction --opt dbname | gzip -c | ssh target_host "gunzip | mysql -u root -p dbname"
xtrabackup` 流式傳輸# 源機
xtrabackup --backup --stream=xbstream --target-dir=/tmp --databases=dbname | ssh target_host "xbstream -x -C /var/lib/mysql"
# 目標機需 --prepare 後啟動
| 層級 | 工具/策略 | RPO | RTO | 適用場景 |
|---|---|---|---|---|
| 核心交易庫 | xtrabackup 全量日增 + 增量小時級 + Binlog PITR | < 1 分鐘 | 分鐘級 | 金融、訂單、用戶中心 |
| 業務分析庫 | mysqldump 每日全量 + Binlog | 24 小時 | 小時級 | 報表、數據倉庫 |
| 配置/字典表 | Git 管理 SQL 文件 / mysqldump 變更時觸發 | 即時 | 秒級 | 元數據、字典表 |
| 測試/開發庫 | 定期快照 / 不備份（可重建） | 不可用 | 不可用 | 非持久化環境 |
備份不是目的，**可恢復**才是目的。建立「備份→驗證→恢復演練→文檔更新」閉環，讓團隊在災難面前從容不迫。