您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 資料庫備份與恢復完整指南：邏輯/物理備份、工具選型與實戰腳本

編輯：MySQL 資料庫相關知識

MySQL 備份分為邏輯備份 (SQL 語句) 與物理備份 (資料檔案) 兩大類。本文系統整理四種備份類型、三種備份狀態、主流工具對比與企業級備份腳本實戰。

一、備份分類體系

按範圍分類

類型	說明	頻率建議	優/缺點
完全備份	整個資料庫所有物件	每週 1 次	完整但耗時耗空間
交易日誌備份	增量變更記錄	每小時/更頻繁	快、空間小、需完整備份為基準
差異/增量備份	上次完全備份後的變更	每天 1 次	恢復快、介於完全與日誌間
檔案備份	資料庫檔案層面備份	極大資料庫專用	需多檔案儲存架構

按資料庫狀態分類

冷備份： 資料庫關閉，一致性最佳，需停機
熱備份： 資料庫運行中，依賴日誌實現一致性 (InnoDB 支援良好)
邏輯備份： 導出 SQL 語句，跨版本遷移友善

二、主流備份工具深度對比

1. mysqldump - 官方邏輯備份工具

# MyISAM (需鎖表)
mysqldump --databases test --lock-all-tables --flush-logs > backup_`date +%F-%H-%M`.sql

# InnoDB (熱備份，無需鎖表)
mysqldump --databases test --single-transaction --flush-logs --master-data=2 > backup_`date +%F-%H-%M`.sql

# 只備份結構
mysqldump -u user -p -d --add-drop-table db_name > structure.sql

恢復注意： SET SQL_LOG_BIN=0; 關閉二進制日誌加速恢復。

2. LVM 快照備份 - 物理備份利器

# 1. 加全域讀鎖並刷新日誌
mysql> FLUSH TABLES WITH READ LOCK;
mysql> FLUSH LOGS;

# 2. 建立快照卷 (需 LVM 環境)
lvcreate -L 1G -s -n data-snap -p -r /dev/myvg/mydata

# 3. 釋放鎖
mysql> UNLOCK TABLES;

# 4. 掛載快照備份
mount -r /dev/myvg/data-snap /mnt/snap
tar -czf backup.tar.gz /mnt/snap

# 5. 釋放快照
umount /mnt/snap
lvremove /dev/myvg/data-snap

3. tar 打包備份 - 簡單物理冷/溫備份

# 視窗 1: 加鎖並記錄 binlog 位置
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;  -- 記錄 File, Position

# 視窗 2: 打包資料目錄
cd /usr/local/mysql/data
tar -zcvf mysql_backup_`date +%F`.tar.gz ./

# 視窗 1: 釋放鎖
mysql> UNLOCK TABLES;

4. Percona XtraBackup - 企業級物理熱備份首選

支援 InnoDB 完全/增量/部分備份
非阻塞、速度極快 (流式複製 + crash-recovery 恢復一致性)
支援單表備份 (需開啟 innodb_file_per_table=ON)
支援從庫備份不影響主庫

# 完全備份
xtrabackup --backup --target-dir=/backup/full --user=root --password=xxx

# 增量備份 (基於上次完全備份)
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full

# 準備/恢復
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full --incremental-dir=/backup/inc1
xtrabackup --copy-back --target-dir=/backup/full