萬盛學電腦網

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

MySQL 資料庫備份與還原完整教學:mysqldump 參數詳解與檔案級快速備份

資料備份是 DBA 核心職責。MySQL 提供邏輯備份工具 mysqldump 與檔案級物理備份兩大路徑。本文詳細解析 mysqldump 關鍵參數、常用備份/還原指令、以及檔案級複製的適用場景與風險。

一、mysqldump 邏輯備份 (官方推薦)

基本語法

mysqldump [OPTIONS] database [tables] > backup.sql
# 不指定 tables 則備份整庫

核心參數速查表

參數說明
--opt組合參數:--quick --add-drop-table --add-locks --extended-insert --lock-tables,產出最快匯入效能的 SQL (預設開啟)
--single-transactionInnoDB 專用:啟動一致性快照,不鎖表,適合線上熱備份
--master-data=1在輸出檔寫入 CHANGE MASTER TO 陳述式,記錄 Binlog 位置,利於從庫搭建
--quick / -q不緩衝查詢結果,直接串流輸出,防大表 OOM
--extended-insert / -e多行 INSERT 語法,縮小檔案、加快匯入
--add-drop-table每個 CREATE TABLE 前加 DROP TABLE,還原時自動重建
--lock-tables / -l備份前鎖定所有表 (MyISAM 必須);InnoDB 用 --single-transaction 替代
--no-data / -d僅匯出結構 (Schema),不含資料
--no-create-info / -t僅匯出資料,不含 CREATE TABLE
--where / -w只備份符合條件的列,如 -w "id>1000"
--compress / -C客戶端/伺服器間壓縮傳輸
--host / -h, --port / -P, --user / -u, --password / -p連線參數

常用備份指令範例

# 1. 完整線上熱備份 (InnoDB),含 Binlog 位置,壓縮輸出
mysqldump --single-transaction --master-data=1 --quick --extended-insert --compress db_name | gzip > db_$(date +%F).sql.gz

# 2. 僅結構備份 (Schema 版控)
mysqldump --no-data --routines --triggers --events db_name > schema.sql

# 3. 分表匯出 (每表一個 .sql + .txt),需在伺服器本機執行
mysqldump --tab=/backup/dir --fields-terminated-by=',' --fields-enclosed-by='"' db_name

還原指令

# 建立目標資料庫
mysqladmin create target_db

# 還原 (若備份含 CREATE DATABASE 則無需建庫)
mysql target_db < backup.sql
# 或壓縮檔直接管道
zcat backup.sql.gz | mysql target_db

二、檔案級物理備份 (實驗性/特定場景)

直接複製 datadir 下資料庫目錄 (如 C:\mysql\data\TestA) 到另一台同版本 MySQL 的 datadir,重啟或 FLUSH TABLES 後即可識別。

  • 優點:極速、無 SQL 解析開銷。
  • 缺點:強制要求同版本、同架構、同參數 (innodb_log_file_size 等);InnoDB 需 FLUSH TABLES WITH READ LOCK 確保一致性;不支援跨版本升級;非官方支援,生產環境慎用。

三、備份策略建議

  • 每日完整備份 + 每小時增量 Binlog 備份 (或 xtrabackup 實體增量)。
  • 異地儲存、定期演練還原、監控備份大小與耗時。
  • 重要系統採用 Percona XtraBackup 實體熱備份,支援流式傳輸、壓縮、並行、部分備份,企業級首選。

備份無價,唯有經過驗證的還原流程才算真正的備份。

copyright © 萬盛學電腦網 all rights reserved