mysqldump 完整指南：資料匯入、備份與增量恢復實戰

前言

mysqldump 是 MySQL 官方提供的邏輯備份工具，支援完整備份、增量備份、跨主機遷移等核心場景。本文系統整理從基礎語法到生產環境自動化部署的完整知識體系，助你構建可靠的資料保護策略。

一、核心使用模式

1.1 基礎備份與還原

# 備份單一資料庫
mysqldump -u root -p database_name > backup.sql

# 還原資料庫
mysql -u root -p database_name < backup.sql

預設輸出包含 CREATE TABLE 結構語句與 INSERT 資料語句，適用於中小型資料庫的完整邏輯備份。

1.2 進階參數優化 (--opt)

啟用 --opt 參數（預設開啟）可獲得：

• DROP TABLE IF EXISTS 保冪等性重建
• LOCK TABLES / UNLOCK TABLES 確保備份一致性
• 擴展插入語句 (--extended-insert) 減輸出體積

1.3 跨主機直接遷移

mysqldump --host=source_host --opt source_db | mysql --host=target_host -C target_db

-C 啟用壓縮傳輸，適合內網高速遷移場景。

1.4 結構與資料分離備份

# 僅備份結構
mysqldump --no-data --databases db1 db2 db3 > schema.sql

# 備份所有資料庫
mysqldump --all-databases > full_backup.sql

二、生產環境自動化備份策略

2.1 定時任務配置

編輯 /etc/crontab 實現每日壓縮備份：

30 1 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/backup/db_$(date +\%m-\%d-\%Y).sql.gz

關鍵點：

• 使用管道直連 gzip 節省磁碟 I/O
• 日期格式化確保檔名唯一
• 建議掛載獨立儲存卷 (/mnt/backup) 隔離故障域

2.2 Shell 腳本範例

#!/bin/bash
BACKUP_DIR="/backup"
OLD_DIR="/oldbackup"
DATE=$(date +%F)

cd $BACKUP_DIR
mv backup_* $OLD_DIR/
mysqldump -u user -p'password' database_name > backup_${DATE}.sql
echo "備份完成: backup_${DATE}.sql"

搭配 crontab -e 設定排程，實現舊備份自動歸檔與新備份生成的閉環管理。

三、增量備份架構：全量 + 二進位日誌

3.1 核心原理

mysqldump 還原後會丟失備份點之後的變更，需結合 mysqlbinlog 實現時間點恢復 (PITR)。

3.2 關鍵配置

[mysqld]
log-bin=mysql-bin          # 啟用二進位日誌
server-id=1                # 主從架構必備

3.3 協同備份命令

mysqldump --single-transaction --flush-logs --master-data=2 > full_backup.sql

參數解析：

• --single-transaction: InnoDB 熱備份不鎖表
• --flush-logs: 切換新的 binlog 檔案作為增量起點
• --master-data=2: 註釋記錄 binlog 位置 (CHANGE MASTER TO 語法)

3.4 分階段恢復流程

# 1. 還原全量備份
mysql -u root -p < full_backup.sql

# 2. 套用增量日誌
mysqlbinlog mysql-bin.000003 | mysql -u root -p

3.5 精準時間點恢復

# 恢復至指定時間前
mysqlbinlog --stop-date="2024-01-15 10:00:00" mysql-bin.000003 | mysql -u root -p

# 跳過錯誤時段繼續恢復
mysqlbinlog --start-date="2024-01-15 10:05:00" mysql-bin.000003 | mysql -u root -p

3.6 位置基準恢復 (更精確)

# 先導出可讀 SQL 分析位置
mysqlbinlog --start-date="2024-01-15 09:55:00" --stop-date="2024-01-15 10:05:00" mysql-bin.000003 > /tmp/analyze.sql

# 根據位置號恢復
mysqlbinlog --stop-position=368312 mysql-bin.000003 | mysql -u root -p
mysqlbinlog --start-position=368315 mysql-bin.000003 | mysql -u root -p

優勢：避免時鐘偏差問題，適合高併發交易系統的精準恢復。

四、最佳實踐總結

1. 分層備份: 每日全量 + 實時 binlog 增量，RPO 控制在分鐘級
2. 異地存儲: 備份檔案同步至異地儲存或雲端 (S3/OSS)
3. 定期演練: 每季進行一次完整恢復演練，驗證備份可用性
4. 監控告警: 監控備份任務耗時、檔案大小、binlog 磁碟佔用
5. 權限最小化: 備份帳號僅授予 SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER 權限

掌握 mysqldump 完整工具鏈，是構建 MySQL 高可用架構的基石。建議根據業務 RPO/RTO 要求，靈活組合全量、增量、延遲從庫等多層防護策略。