MySQL 備份與恢復機制深度解析：mysqldump、xtrabackup、binlog、文件級備份全對比

備份工具全景圖

mysqldump 核心參數深度解析

字符集與兼容性

--default-character-set=utf8mb4   # 必須指定，否則 latin1 導致亂碼
--compatible=mysql40              # 目標版本兼容模式

輸出格式優化

--extended-insert=FALSE           # 每行獨立 INSERT，便於審計/部分恢復
--complete-insert                 # 包含列名，避免 Schema 變更導入失敗
--hex-blob                        # 二進制列 (BLOB/BINARY) 十六進制輸出

鎖策略選擇

# MyISAM 表
--lock-all-tables (-x)            # 全局讀鎖，一致性最強
--lock-tables                     # 庫級鎖表

# InnoDB 表 (推薦)
--single-transaction              # 事務快照，無阻塞，需 REPEATABLE READ 隔離級
--master-data=1                   # 輸出 CHANGE MASTER TO，記錄 binlog 位點

效能參數

--quick (-q)                      # 流式讀取，不緩存結果集到內存 (大表必加)
--opt                             # 綁定組合：--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset
--skip-opt                        # 禁用上述組合，完全自定義

存儲程序/觸發器

--routines (-R)                   # 導出存儲過程/函數
--triggers                        # 導出觸發器 (默認開啟)

生產環境推薦命令模板

MyISAM 表：

mysqldump -u$user -p$pass \
  --default-character-set=utf8mb4 \
  --opt --extended-insert=FALSE \
  --triggers -R --hex-blob \
  --lock-all-tables \
  $db_name > $db_name.sql

InnoDB 表 (熱備份首選)：

mysqldump -u$user -p$pass \
  --default-character-set=utf8mb4 \
  --opt --extended-insert=FALSE \
  --triggers -R --hex-blob \
  --single-transaction \
  $db_name > $db_name.sql

搭建從庫專用 (含 binlog 位點)：

mysqldump -u$user -p$pass \
  --default-character-set=utf8mb4 \
  --opt --master-data=1 \
  --single-transaction --flush-logs \
  $db_name > $db_name.sql

還原方式

# 方式 1: mysql 客戶端管道導入
mysql -u$user -p$pass $db_name < $db_name.sql

# 方式 2: SOURCE 命令 (需絕對路徑、mysqld 用戶可讀)
mysql> SOURCE /full/path/to/db_name.sql;

mysqlhotcopy (僅 MyISAM、類 Unix)

# 備份單庫
mysqlhotcopy -h localhost -u$user -p$pass db_name /backup/dir/

# 備份多庫/正則
mysqlhotcopy -h localhost -u$user -p$pass db_name_1 ... db_name_n /backup/dir/
mysqlhotcopy -h localhost -u$user -p$pass db_name./regex/ /backup/dir/

# 還原: 直接拷貝到 datadir 並修正權限
cp -rf db_name /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/db_name

SQL 語法級備份 (了解即可，不推薦生產使用)

-- 備份表結構+數據 (僅 MyISAM，不含索引，恢復慢)
BACKUP TABLE tbl_name TO '/backup/dir/';

-- 導出文本文件
SELECT * INTO OUTFILE '/backup/dir/tbl_name.txt' 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
FROM tbl_name;

-- 恢復
RESTORE TABLE tbl_name FROM '/backup/dir/';
LOAD DATA INFILE '/backup/dir/tbl_name.txt' INTO TABLE tbl_name;

Binlog 增量備份策略

啟用配置

# my.cnf
[mysqld]
server-id = 1
log-bin = binlog
log-bin-index = binlog.index
expire_logs_days = 7

增量備份操作

# 刷新日誌產生新 binlog 文件
mysql -e "FLUSH LOGS;"

# 備份舊 binlog 文件 (除當前寫入文件外)
# 從庫還需備份 master.info、relay-log.info

恢復/解析

# 解析指定庫、時間範圍
mysqlbinlog --database=db_name --start-datetime="2023-01-01 00:00:00" \
  --stop-datetime="2023-01-02 00:00:00" binlog.000001 | mysql -u$user -p$pass db_name

文件級冷備份

# 1. 鎖表刷盤
mysql -e "FLUSH TABLES WITH READ LOCK;"

# 2. 拷貝數據目錄 (含 ibdata1、ib_logfile*、.frm、.ibd 等)
rsync -av /var/lib/mysql/ /backup/mysql_$(date +%F)/

# 3. 解鎖
mysql -e "UNLOCK TABLES;"

# InnoDB 必須同時備份 ib_logfile* (重做日誌)，用於崩潰恢復

備份策略制定建議

最佳架構：主庫零備份壓力 → 從庫掛載 xtrabackup/邏輯備份 → 異地備份存儲。主從復制天然提供 binlog 增量流。

數據維護與災難恢復

MyISAM 維護

CHECK TABLE tbl_name;          -- 檢查
REPAIR TABLE tbl_name;         -- 修復
OPTIMIZE TABLE tbl_name;       -- 整理碎片/統計信息
ANALYZE TABLE tbl_name;        -- 更新統計信息

InnoDB 維護

-- 整理碎片、重建索引 (Online DDL 5.6+)
ALTER TABLE tbl_name ENGINE=InnoDB;

-- 啟動自動恢復 (需保留 ib_logfile*)
innodb_force_recovery = 1  # 1-6 級別，僅讀取數據導出
# my.cnf 添加後重啟，導出數據後移除參數重啟

總結

備份無小事，恢復才是硬道理。建立「全量基線 + 增量流 + 定期演練」三位一體體系：

1. 日常：從庫 xtrabackup 物理全量 + binlog 增量
2. 遷移/升級：mysqldump 邏輯導出
3. 應急：文件系統快照 + binlog 時間點恢復
4. 季度：全鏈路恢復演練，驗證 RTO/RPO