萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 資料庫備份與恢復全攻略:mysqldump、物理拷貝、主從同步三大方案深度解析

資料庫備份是資料安全的最後防線。本文系統梳理 MySQL 四大備份方式、物理文件結構解析、mysqldump 參數化實戰、物理拷貝適用邊界,以及恢復操作的標準化流程。

四大備份方式對比

方式類型優點缺點適用場景
直接拷貝文件物理冷備份速度最快、最簡單需停庫、不適用 InnoDB、版本強綁定開發環境、MyISAM 唯讀庫
mysqldump邏輯熱備份跨版本、可壓縮、可篩選、文本可讀大數據量慢、鎖表 (無單事務參數)中小型庫、跨版本遷移、開發測試
mysqlhotcopy物理熱備份速度快、僅鎖表瞬間僅支援 MyISAM、已棄用 (5.7 移除)舊版本 MyISAM 庫
主從同步實時增量備份RPO≈0、可做讀寫分離、高可用基石架構複雜、非邏輯備份替代品生產核心庫、高可用架構

MySQL 物理文件結構速查

日誌文件

  • *.err:錯誤日誌
  • mysql-bin.*:二進制日誌 (Binlog)
  • *.log:一般查詢日誌
  • slow-query.log:慢查詢日誌
  • ib_logfile*:InnoDB Redo Log

數據文件

引擎結構文件數據文件索引文件
MyISAM.frm.MYD.MYI
InnoDB (獨立表空間).frm (≤5.6) / .ibd 含字典 (5.7+).ibd.ibd (共存)
InnoDB (共享表空間)-ibdata1 (含數據+索引+系統表空間)

系統與複制文件

  • my.cnf:配置文件
  • *.pid:進程 ID
  • *.sock:Unix Socket
  • master.info、relay-log.*、relay-log.index:從庫複制元數據

mysqldump 實戰參數大全

備份單庫

mysqldump -u root -p --single-transaction --routines --triggers --events \
  --master-data=2 --hex-blob --quick test_db > /backup/test_db_$(date +%F).sql
  • --single-transaction:InnoDB 一致性熱備份關鍵 (啟動事務快照)
  • --master-data=2:輸出 Binlog 位置為註釋,便於增量恢復定點
  • --hex-blob:二進制字段十六進制輸出,避免字符集問題
  • --quick:流式讀取,不緩存全表結果集

備份多庫 / 全庫

# 多庫
mysqldump -u root -p --databases db1 db2 --single-transaction ... > multi.sql

# 全庫 (含 mysql 系統庫)
mysqldump -u root -p --all-databases --single-transaction ... > full.sql

恢復操作標準流程

# 1. 建立目標庫 (若備份未含 CREATE DATABASE)
mysql -u root -p -e "CREATE DATABASE test_db CHARSET utf8mb4;"

# 2. 全量恢復
mysql -u root -p test_db < /backup/test_db_2023-01-01.sql

# 3. 增量恢復 (基於 Binlog)
mysqlbinlog --start-position=XXX --stop-position=YYY /var/log/mysql/mysql-bin.000001 | mysql -u root -p test_db

物理拷貝適用邊界 (重要)

  • ✅ MyISAM 表:直接拷貝 .frm/.MYD/.MYI 即可
  • ❌ InnoDB 表:嚴禁直接拷貝 .ibd 或 ibdata*,除非配合 FLUSH TABLES ... FOR EXPORT + IMPORT TABLESPACE (5.6+)
  • ✅ 同版本、同架構、同配置的從庫克隆可用物理備份 (Percona XtraBackup)

生產環境備份策略建議

  • 每日全量:mysqldump --single-transaction + 壓縮上傳對象儲存
  • 每小時增量:Binlog 歸檔 (或 XtraBackup 增量)
  • 定期演練:季度級全量恢復演練,驗證 RTO/RPO
  • 異地多備:至少 3 副本、2 種介質、1 異地

備份不僅是備份,可恢復的備份才是備份。建立標準化 SOP,納入運維體系定期覆核。

copyright © 萬盛學電腦網 all rights reserved