mysqldump 導入導出與增量備份完整實戰指南

一、mysqldump 常用模式

# 1. 最簡單：單庫導出
mysqldump -u root -p db > dump.sql

# 2. 帶 DROP TABLE（覆蓋導入）
mysqldump --add-drop-table -u root -p db > dump.sql

# 3. 跨主機直接同步（壓縮傳輸）
mysqldump -h src -u u -p'p' --single-transaction db | mysql -h dst -u u -p'p' db

# 4. 僅結構
mysqldump --no-data --databases db1 db2 > struct.sql

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

二、定時備份：cron + 壓縮 + 保留策略

# /etc/cron.d/mysql-backup
30 1 * * * root mysqldump -u backup -p'pass' --single-transaction --routines --triggers --events prod | gzip > /backup/prod_$(date +\%F).sql.gz
# 保留 7 天
0 2 * * * root find /backup -name 'prod_*.sql.gz' -mtime +7 -delete

三、Shell 腳本備份範例

#!/bin/bash
set -euo pipefail
BAK_DIR="/backup/mysql"
DB="prod"
USER="backup"
PASS="xxx"
FILE="$BAK_DIR/${DB}_$(date +%F).sql"
mysqldump -u"$USER" -p"$PASS" --single-transaction --routines --triggers --events "$DB" > "$FILE"
gzip "$FILE"
find "$BAK_DIR" -name "${DB}_*.sql.gz" -mtime +7 -delete

四、增量備份核心：全量 + Binlog

1. 啟用 Binlog：log-bin=mysql-bin、expire_logs_days=7、binlog_format=ROW。
2. 全量備份時切換新 Binlog：
   mysqldump --single-transaction --flush-logs --master-data=2 db | gzip > full_$(date +%F).sql.gz
3. 增量即為產生的 mysql-bin.00000X，定期同步至異地。
4. 恢復：導入全量 → 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

# 按位置精確恢復（先用 mysqlbinlog 導出文本定位 position）
mysqlbinlog --stop-position=368312 mysql-bin.000003 | mysql db
mysqlbinlog --start-position=368315 mysql-bin.000003 | mysql db

六、避坑指南

• --single-transaction 僅對 InnoDB 有效，MyISAM 仍會鎖表。
• 大表導出加 --quick（預設開啟）防 OOM。
• 導入前建議 SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; SET AUTOCOMMIT=0; 加速，導入後恢復。
• Binlog 格式 ROW 最安全，MIXED 折中，STATEMENT 易主從不一致。

掌握「全量+Binlog」雙軌備份，配合 PITR，資料安全無死角。