萬盛學電腦網

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

MySQL 資料庫備份與還原完整指南:mysqldump、mysqlhotcopy、二進制日誌與檔案級備份

一、mysqldump:邏輯備份首選工具

將資料表導出為 SQL 腳本,跨版本升級相容性佳,最常用。

核心參數詳解

參數說明
--compatible=name指定相容模式:ansi、mysql323、mysql40、postgresql、oracle 等,多值用逗號分隔。
--complete-insert, -c完整 INSERT(含欄位名),提高插入效率,但可能受 max_allowed_packet 限制。
--default-character-set=charset關鍵:非 latin1 編碼資料庫必須指定,否則還原產生亂碼。
--disable-keys在 INSERT 前後加 ALTER TABLE ... DISABLE/ENABLE KEYS,批量插入後再建索引,大幅提速(僅 MyISAM)。
--extended-insert=false關閉預設的多行 INSERT,改用單行 INSERT,便於除錯與相容。
--hex-blob二進制欄位(BINARY、VARBINARY、BLOB)以十六進制導出,避免編碼問題。
--lock-all-tables, -x全域讀鎖,保證一致性,自動關閉 --single-transaction。
--lock-tables鎖定當前導出的表(MyISAM 適用),InnoDB 請用 --single-transaction。
--no-create-info, -t僅導出資料,不含 CREATE TABLE。
--no-data, -d僅導出結構,不含資料。
--opt快捷選項組合(預設開啟),含 --add-drop-tables --disable-keys --extended-insert --lock-tables --quick --set-charset 等。
--quick, -q大表必備:直接輸出查詢結果,不緩存到記憶體。
--routines, -R導出預存程序與自定義函數。
--single-transactionInnoDB 專用:啟動事務導出,不阻塞寫入,保證一致性視圖。需搭配 --quick。
--triggers導出觸發器(預設開啟)。

常用備份命令範例

# MyISAM 表
mysqldump -uuser -ppass --default-character-set=utf8 --opt --extended-insert=false --triggers -R --hex-blob -x db_name > db_name.sql

# InnoDB 表
mysqldump -uuser -ppass --default-character-set=utf8 --opt --extended-insert=false --triggers -R --hex-blob --single-transaction db_name > db_name.sql

還原方式

  1. mysql 客戶端導入:mysql -uuser -ppass db_name < db_name.sql
  2. SOURCE 命令(在 mysql 客戶端內):SOURCE /absolute/path/db_name.sql;(需 mysqld 執行用戶有讀取權限)

二、mysqlhotcopy:MyISAM 物理熱備份最快方案

  • Perl 腳本,使用 LOCK TABLES、FLUSH TABLES + cp/scp。
  • 限制:僅支援 MyISAM、類 Unix/NetWare 系統、需在資料檔所在機器執行。
  • 需 SELECT、RELOAD 權限及資料目錄讀取權限。

使用範例

mysqlhotcopy -h=localhost -u=user -p=pass db_name /backup/dir
mysqlhotcopy -h=localhost -u=user -p=pass db_name_1 db_name_2 /backup/dir
mysqlhotcopy -h=localhost -u=user -p=pass db_name./regex/ /backup/dir

還原

cp -rf db_name /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/db_name

三、SQL 語法備份(原生指令)

BACKUP TABLE / RESTORE TABLE

  • 鎖表後拷貝 .frm、.MYD,不含索引檔,還原需重建索引,較慢。
  • 語法:BACKUP TABLE tbl_name TO '/path/'; / RESTORE TABLE tbl_name FROM '/path/';
  • 需 FILE 權限,目標目錄需 mysqld 可寫,檔案不可覆蓋。

SELECT INTO OUTFILE / LOAD DATA INFILE

  • 導出為文字檔,可自定義分隔符,便於資料處理。
  • 導出:SELECT * INTO OUTFILE '/path/tbl.txt' FROM tbl_name;
  • 導入:LOAD DATA INFILE '/path/tbl.txt' INTO TABLE tbl_name [REPLACE|IGNORE];
  • 同樣需 FILE 權限,檔案不可覆蓋,表需預先存在。

四、啟用二進制日誌:支援增量備份與時間點復原

啟用步驟

  1. 關閉 mysqld,編輯 my.cnf:
    [mysqld]
    server-id = 1
    log-bin = binlog
    log-bin-index = binlog.index
  2. 重啟 mysqld,產生 binlog.000001、binlog.index。

增量備份操作

FLUSH LOGS;  -- 切換新 binlog,舊檔案即為增量備份

從庫還需備份 master.info、relay-log.info。

查看與還原

# 查看
mysqlbinlog /path/binlog.000001

# 還原
mysqlbinlog /path/binlog.000001 | mysql -uuser -ppass db_name

架構建議

採用「主從複制」,在從庫執行備份:低成本、不影響主庫效能、支援增量備份、分擔查詢壓力。

五、直接備份資料檔(最快、最直接)

FLUSH TABLES WITH READ LOCK;  -- 刷新記憶體到磁碟,鎖定所有表
# 拷貝整個 datadir 目錄
# InnoDB 還需備份 ib_logfile*(崩潰恢復依賴)
UNLOCK TABLES;
  • 優點:速度最快、操作最簡單。
  • 缺點:難以實現增量備份、需鎖表影響寫入。

六、備份策略建議

系統等級策略
中等業務量首次全量 → 每日增量 → 每周全量
核心高併發系統每日全量 → 每小時增量 → 甚至更頻繁

最佳實踐:主從複制 + 從庫備份,實現線上無鎖增量備份。

七、資料維護與災難恢復

MyISAM 表維護

  • CHECK TABLE / REPAIR TABLE:檢查/修復
  • OPTIMIZE TABLE:碎片整理
  • ANALYZE TABLE:更新統計資訊
  • 以上均可用 myisamchk 工具完成。

InnoDB 表維護

ALTER TABLE tbl_name ENGINE = InnoDB;

看似無操作,實則重建表空間、整理碎片、提升索引速度。

InnoDB 崩潰恢復

  • 依賴 ib_logfile* 重做日誌。
  • my.cnf 設定 innodb_force_recovery = 4 可強制啟動並嘗試修復(參考手冊調整等級)。
copyright © 萬盛學電腦網 all rights reserved