您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 資料庫備份基礎知識大全：從檔案複製到 mysqldump 企業級策略

編輯：MySQL 綜合教學

一、非官方捷徑：資料目錄直接複製 (僅限 MyISAM、同版本、離線)

原理： MyISAM 引擎以檔案儲存表結構 (.frm)、資料 (.MYD)、索引 (.MYI)，直接複製資料目錄即可「備份」。

# 前提：MySQL 服務完全停止
service mysql stop

# 複製整個資料庫目錄
cp -rp /var/lib/mysql/test_db /backup/test_db_$(date +%F)

# 還原：複製回目錄、修正權限、啟動服務
cp -rp /backup/test_db_20240115 /var/lib/mysql/test_db
chown -R mysql:mysql /var/lib/mysql/test_db
service mysql start

嚴重限制： 僅適用 MyISAM、同主版本、同架構、服務離線。InnoDB 共享表空間、Redo/Undo Log、雙寫緩衝區導致直接複製**不可用**。**生產環境嚴禁使用此法。**

二、官方標準：mysqldump 邏輯備份全解析

1. 基礎語法

mysqldump [OPTIONS] database [tables] > backup.sql

2. 核心參數分類

類別	參數	說明
效能優化	--quick / -q	不緩衝結果集，直接串流輸出 (大表必加)
	--opt	組合：--quick --add-drop-table --add-locks --extended-insert --lock-tables
	--single-transaction	InnoDB 熱備份關鍵：啟動交易、一致性快照、不鎖表
	--master-data=2	輸出 CHANGE MASTER TO 註解，記錄 Binlog 位置 (複寫同步用)
結構控制	--no-create-info / -t	僅導出資料，無 CREATE TABLE
	--no-data / -d	僅導出結構，無 INSERT
	--add-drop-table	每張表前加 DROP TABLE IF EXISTS
輸出格式	--complete-insert / -c	INSERT 含欄位名，便於跨版本/異構遷移
	--extended-insert / -e	多行 INSERT (預設)，大幅縮小體積、加快匯入
	--hex-blob	二進位欄位 (BLOB/BINARY) 以十六進制輸出
壓縮與傳輸	--compress / -C	Client-Server 壓縮傳輸 (需雙端支援)
	\| gzip	管線壓縮輸出檔案
	--host / -h	遠端主機備份

3. 企業級備份指令範本

# 完整備份 (含結構、資料、Binlog 位置、InnoDB 一致性)
mysqldump --single-transaction --master-data=2 --opt --hex-blob \
  -u backup_user -p'StrongPass' --databases db1 db2 db3 | \
  gzip > /backup/full_$(date +%F_%H%M).sql.gz

# 僅結構備份 (Schema 版控)
mysqldump --no-data --routines --triggers --events \
  -u backup_user -p'StrongPass' --databases db1 db2 > /backup/schema_$(date +%F).sql

# 大表分表備份 (配合 parallel)
mysqldump --single-transaction --opt --hex-blob -u backup_user -p'Pass' db1 large_table | gzip > /backup/large_table_$(date +%F).sql.gz

4. 還原驗證流程

# 1. 建立目標資料庫
mysqladmin -u root -p create target_db

# 2. 還原 (解壓管線)
gunzip < /backup/full_20240115_0200.sql.gz | mysql -u root -p target_db

# 3. 驗證
mysql -u root -p target_db -e "SHOW TABLES; SELECT COUNT(*) FROM core_table;"

三、進階備份策略架構

1. 分層備份矩陣

層級	頻率	保留	工具	RTO/RPO
完整邏輯備份	每日 02:00	30 天	mysqldump + gzip	RPO 24h / RTO 視資料量
增量備份	每小時	7 天	Binlog (mysqlbinlog) / Percona XtraBackup	RPO 1h / RTO 分鐘級
實體熱備份	每週	8 週	Percona XtraBackup / MariaDB Backup	RPO 168h / RTO 秒級 (複製回)
異地同步	即時	永久	主從複寫 / Galera Cluster	RPO 接近 0 / RTO 秒級

2. XtraBackup 實體熱備份 (InnoDB 必備)

# 完整備份
xtrabackup --backup --target-dir=/backup/full_$(date +%F) --user=backup_user --password='Pass'

# 增量備份 (基於上次完整)
xtrabackup --backup --target-dir=/backup/inc_$(date +%F) --incremental-basedir=/backup/full_20240101 --user=backup_user --password='Pass'

# 還原準備 (apply log)
xtrabackup --prepare --target-dir=/backup/full_20240115
xtrabackup --prepare --target-dir=/backup/full_20240115 --incremental-dir=/backup/inc_20240116

# 複製回 (需停止 MySQL、清空資料目錄)
xtrabackup --copy-back --target-dir=/backup/full_20240115
chown -R mysql:mysql /var/lib/mysql
service mysql start