萬盛學電腦網

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

MySQL 備份還原全策略:mysqldump、mysqlhotcopy、二進制日誌與檔案級備份

備份工具對比

工具類型適用引擎優點缺點
mysqldump邏輯備份 (SQL)所有跨版本相容、可編輯、支援壓縮大資料庫匯入慢、鎖表影響寫入
mysqlhotcopy物理備份 (檔案複製)MyISAM速度極快、不影響運行 (僅短暫鎖表)僅限 Unix/NetWare、不支援 InnoDB
BACKUP TABLESQL 指令物理備份MyISAM線上備份不備份索引、還原慢、已棄用
SELECT INTO OUTFILE資料導出文字檔所有格式靈活、易處理不含結構、需 FILE 權限
二進制日誌增量備份所有即時、支援時間點復原需規劃保留策略、佔磁碟
直接複製資料目錄物理冷備份所有最快、最完整需停機或全域讀鎖、無增量

mysqldump 核心參數精選

# MyISAM 推薦
mysqldump -u$user -p$pwd --default-character-set=utf8 \
  --opt --extended-insert=FALSE --triggers --routines --hex-blob \
  --lock-all-tables db_name > backup.sql

# InnoDB 推薦 (熱備份不鎖表)
mysqldump -u$user -p$pwd --default-character-set=utf8 \
  --opt --extended-insert=FALSE --triggers --routines --hex-blob \
  --single-transaction --quick db_name > backup.sql
  • --opt = 開啟多項優化預設值。
  • --quick 大表直接串流輸出,不緩存記憶體。
  • --single-transaction InnoDB 一致性快照,不阻塞寫入。
  • --extended-insert=FALSE 每行獨立 INSERT,避免 max_allowed_packet 限制。

還原方式

# 方法 1: mysql 客戶端
mysql -u$user -p$pwd db_name < backup.sql

# 方法 2: SOURCE 指令 (在 mysql shell 內)
SOURCE /path/backup.sql;

壓縮備份與跨機還原

# 備份並壓縮
mysqldump --quick db_name | gzip > backup.sql.gz

# 遠端還原 (解壓管線導入)
gunzip < backup.sql.gz | mysql -h remote_host -u$user -p$pwd db_name

mysqlhotcopy 快速備份 (僅 MyISAM)

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

# 正則備份多庫
mysqlhotcopy -h localhost -u user -p pass 'db_.*' /backup/dir

# 還原: 直接複製回 datadir 並修正權限
cp -rf /backup/dir/db_name /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/db_name

二進制日誌增量備份

  1. 啟用:my.cnf 加入 log-bin=binlog、server-id=1,重啟。
  2. 切換日誌:FLUSH LOGS; (產生新 binlog 檔)。
  3. 備份:複製舊 binlog 檔 + binlog.index + 從庫的 master.info/relay-log.info。
  4. 查看:mysqlbinlog binlog.000001。
  5. 還原:mysqlbinlog binlog.000001 | mysql -u$user -p$pwd db_name。

備份策略建議

  • 中型系統:首次全備 + 每日增量 (binlog) + 每周全備。
  • 核心系統:每日全備 + 每小時增量,採主從架構在從庫備份不影響主庫。
  • 定期演練還原,驗證備份可用性。

資料表維護與災難恢復

-- MyISAM
CHECK TABLE tbl; REPAIR TABLE tbl; OPTIMIZE TABLE tbl; ANALYZE TABLE tbl;

-- InnoDB 整理碎片
ALTER TABLE tbl ENGINE=InnoDB;

-- InnoDB 自動恢復 (my.cnf)
innodb_force_recovery = 1  # 1-6 遞增積極度,生產慎用
copyright © 萬盛學電腦網 all rights reserved