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

MySQL 資料庫備份與還原完整指南：mysqldump 參數詳解與壓縮/增量/跨機同步

編輯：MySQL 資料庫相關知識

核心備份命令


# 1. 單庫備份（結構+資料+存儲過程+觸發器）
mysqldump -h host -u user -p'pass' --single-transaction --routines --triggers `db` > backup.sql

# 2. 僅結構
mysqldump -d `db` > struct.sql

# 3. 僅資料
mysqldump -t `db` > data.sql

# 4. 多庫備份
mysqldump --databases db1 db2 db3 > multi.sql

# 5. 全例備份
mysqldump --all-databases > all.sql

# 6. 壓縮備份（管道流式，不落地中間檔）
mysqldump `db` | gzip > backup_$(date +%F).sql.gz

# 7. 帶 DROP TABLE（覆蓋還原用）
mysqldump --add-drop-table `db` > backup.sql

關鍵參數說明

參數	用途
`--single-transaction`	InnoDB 熱備份關鍵，啟動一致性快照，不鎖表
`--routines`	包含存儲過程/函數
`--triggers`	包含觸發器
`--events`	包含事件調度器
`--master-data=2`	輸出 CHANGE MASTER TO 註解（含 binlog 位置），主從搭建必備
`--flush-logs`	備份前切換新 binlog，配合增量備份
`--quick`	大表逐行輸出，避免記憶體暴漲（預設開啟）
`--lock-tables=false`	配合 --single-transaction 避免鎖表

還原命令


# 一般還原
mysql -h host -u user -p'pass' `db` < backup.sql

# 壓縮檔直接還原
gunzip < backup.sql.gz | mysql -u user -p'pass' `db`

# 指定字元集還原（防亂碼）
mysql --default-character-set=utf8mb4 `db` < backup.sql

增量備份策略：全量 + Binlog

配置 log-bin=mysql-bin、expire_logs_days=7。
每日全量：mysqldump --single-transaction --flush-logs --master-data=2 `db` | gzip > full_$(date +%F).sql.gz
增量：binlog 自動產生，定期備份 mysql-bin.* 檔案。
恢復：先導入全量 → mysqlbinlog mysql-bin.000003 | mysql `db` 重放增量。

時間點恢復（PITR）


# 恢復到 2024-01-15 10:00:00 之前
mysqlbinlog --stop-datetime="2024-01-15 10:00:00" mysql-bin.000003 | mysql `db`

# 跳過錯誤時段 10:00~10:05
mysqlbinlog --start-datetime="2024-01-15 10:05:00" mysql-bin.000003 | mysql `db`

跨主機直接同步（無中間檔）


mysqldump -h src -u u -p'p' --single-transaction `db` | mysql -h dst -u u -p'p' `db`

定時備份腳本範例（/etc/cron.daily/mysql_backup）


#!/bin/bash
set -euo pipefail
DB_USER="backup"
DB_PASS="xxxx"
DB_NAME="prod"
BAK_DIR="/backup/mysql"
KEEP_DAYS=7

mkdir -p "$BAK_DIR"
FILE="$BAK_DIR/${DB_NAME}_$(date +%F).sql.gz"
mysqldump -u"$DB_USER" -p"$DB_PASS" --single-transaction --routines --triggers --events "$DB_NAME" | gzip > "$FILE"
find "$BAK_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +$KEEP_DAYS -delete

授權 chmod +x 並加入 cron.daily 即可每日自動備份並保留 7 天。