MySQL 資料庫備份與還原完整策略：mysqldump、物理備份、binlog 恢復、異地容災

備份策略核心原則

• 定期、自動化、驗證可恢復、異地保存、加密儲存。
• 備份類型：完全備份（全量）+ 增量備份（binlog）。
• 恢復目標：RPO（資料遺失容忍度）、RTO（恢復時間目標）。

方法一：mysqldump 邏輯備份（跨版本、跨平台、可攜）

常用指令

# 完全備份（單執行緒）
mysqldump --single-transaction --master-data=2 --flush-logs --routines --triggers --events -u root -p db_name > db_$(date +%F).sql

# 壓縮
mysqldump ... | gzip > db_$(date +%F).sql.gz

# 分表備份（大庫並行）
mysqldump --single-transaction -u root -p db_name tbl1 tbl2 > db_partial.sql

關鍵參數說明

遠程直接遷移

# 來源 → 目標（需目標已建庫）
mysqldump --single-transaction -h src_host -u u -p db | mysql -h dst_host -u u -p db

方法二：物理備份（速度快、適合大庫、版本綁定）

冷備份（停機）

systemctl stop mysqld
tar czf /backup/mysql_$(date +%F).tar.gz /var/lib/mysql
systemctl start mysqld

熱備份：Percona XtraBackup（推薦 InnoDB）

# 完全備份
xtrabackup --backup --target-dir=/backup/full_$(date +%F) --user=root --password

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

# 準備（apply log）
xtrabackup --prepare --target-dir=/backup/full_20240101
xtrabackup --prepare --target-dir=/backup/full_20240101 --incremental-dir=/backup/inc_20240102

# 還原
systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full_20240101
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

方法三：binlog 增量恢復（配合完全備份達成 PITR）

1. 確認 log_bin=ON、binlog_format=ROW、expire_logs_days 足夠長。
2. 完全備份時記錄 binlog 位置（--master-data=2 或 SHOW MASTER STATUS）。 3. 災難發生：
a. 還原完全備份。
b. 套用 binlog：
mysqlbinlog --start-position=12345 --stop-position=67890 binlog.000001 | mysql -u root -p db
或指定時間：
mysqlbinlog --start-datetime="2024-01-15 10:00:00" --stop-datetime="2024-01-15 14:30:00" binlog.* | mysql -u root -p db

單表恢復技巧

• 從完整 SQL 檔提取單表片段（sed -n '/CREATE TABLE `tbl`/,/^UNLOCK TABLES/p'）。
• 或另建臨時庫還原完整備份，再 mysqldump 導出單表、導入目標庫。
• 使用 mysqlbinlog --database=db --table=tbl 過濾 binlog（ROW 格式支援較好）。

自動化與監控

# /etc/cron.d/mysql-backup
0 2 * * * root /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

mysql_backup.sh 內含：備份、壓縮、上傳至 S3/NAS、校驗和、保留策略、失敗告警（郵件/Slack/企業微信）。

異地容災清單

• 備份檔至少在兩個可用區/資料中心。
• 定期（月/季）演練全量恢復、PITR 恢復、單表恢復。
• 記錄演練耗時、問題、改進措施。
• 核心庫 RPO < 5 分鐘（binlog 同步）、RTO < 30 分鐘（自動化腳本）。

1. 上一篇文章： ASP 連接 MySQL 完整教學：MyODBC 驅動、DSN 設定與安全連線模式
2. 下一篇文章： PHP 輕量級 MySQL 備份還原工具 MyDB：分卷備份、Web 介面、無需 Shell 權限

MySQL 資料庫相關知識

SQLite Database Browser 2.0 圖文操作指南

工具簡介 SQLite Database Browser 2

PHP 實戰：分數錄入、總統測驗與會員編輯

專案背景 本章實作三個 PHP 腳本，分別服務「學分保存方案

MySQL 唯一索引配合 ON DUPLICATE KEY UPDATE 實現「不存在插入、存在更新」

業務常見需求：寫入資料時，若主鍵/唯一鍵衝突則更新，否則插入

XtraBackup 物理熱備份腳本實戰：全量+增量策略、備份恢復流程、定時任務自動化

前言 Percona XtraBackup 是 InnoDB