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

MySQL 備份與還原完整指南：mysqldump、物理備份、Binlog 增量與災難恢復策略

編輯：MySQL 綜合教學

一、邏輯備份：mysqldump 詳解

核心原理

mysqldump 將表結構導出為 CREATE TABLE，資料導出為 INSERT 語句，本質是 SQL 腳本檔。

常用參數速查

參數	作用
`--single-transaction`	InnoDB 熱備份必備，啟動一致性快照，不鎖表
`--master-data=2`	輸出 CHANGE MASTER TO 註解，記錄 Binlog 位點，利於搭建從庫
`--routines` / `--triggers`	備份存儲過程/觸發器
`--hex-blob`	二進位欄位十六進位導出，避免亂碼
`--extended-insert=FALSE`	單行 INSERT，便於閱讀與部分恢復
`--quick`	大表必加，流式讀取不緩存全結果集
`--compress`	網路傳輸壓縮

經典備份命令範例

# 1. 全庫備份（含常用優化參數）
mysqldump -uroot -p --single-transaction --master-data=2 --routines --triggers --hex-blob --quick --databases db1 db2 > full_backup.sql

# 2. 僅結構
mysqldump -uroot -p --no-data --databases db1 > structure.sql

# 3. 壓縮備份
mysqldump -uroot -p db1 | gzip > db1_$(date +%F).sql.gz

# 4. 遠程直接同步到另一實例
mysqldump -uroot -p db1 | mysql -h remote_host -uroot -p db1

還原方式

# 1. 客戶端管道導入
mysql -uroot -p db1 < backup.sql

# 2. 壓縮檔解壓導入
gunzip < backup.sql.gz | mysql -uroot -p db1

# 3. MySQL 客戶端內部 source 指令
mysql> USE db1;
mysql> SOURCE /path/backup.sql;

二、物理備份：速度最快、適合大數據量

1. 直接複製資料目錄（需鎖表保證一致性）

mysql> FLUSH TABLES WITH READ LOCK;
# 複製 /var/lib/mysql 目錄
mysql> UNLOCK TABLES;

注意：InnoDB 必須連同 ib_logfile*、ibdata* 一起備份。

2. mysqlhotcopy (僅 MyISAM、類 Unix)

mysqlhotcopy -u root -p db_name /backup/dir/

3. Percona XtraBackup (推薦：InnoDB 熱備份、增量、流式、壓縮)

# 完全備份
xtrabackup --backup --target-dir=/backup/full --user=root --password=xxx

# 增量備份
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full

# 準備/還原
xtrabackup --prepare --apply-log-only --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/inc1
xtrabackup --copy-back --target-dir=/backup/full

三、Binlog 增量備份與時間點恢復 (PITR)

# 開啟 Binlog (my.cnf)
server-id=1
log-bin=mysql-bin
log-bin-index=mysql-bin.index

# 切換 Binlog 產生增量檔
mysql> FLUSH LOGS;

# 還原指定 Binlog 區間
mysqlbinlog --start-datetime="2023-01-01 00:00:00" --stop-datetime="2023-01-01 12:00:00" \
mysql-bin.000001 | mysql -uroot -p db1

四、備份策略建議

業務等級	策略
一般系統	首次全量 + 每日增量 + 每週全量
核心交易/高併發	每日全量 + 每小時增量 + Binlog 即時歸檔
極致可用性	主從架構，在從庫備份，零影響主庫

五、資料維護與災難恢復

MyISAM：CHECK/REPAIR/OPTIMIZE/ANALYZE TABLE 或 myisamchk。
InnoDB：ALTER TABLE t ENGINE=InnoDB 整理碎片（Null 操作重建表）。
啟動自動恢復：innodb_force_recovery=4 (僅緊急時用，0-6 級)。

六、Java 程式碼調用備份範例

Process p = Runtime.getRuntime().exec(
    "cmd /c mysqldump -h127.0.0.1 -P3306 -uroot -p123456 customer > d:/backup/customer_$(date +%s).sql");
p.waitFor();
int ret = p.exitValue();
System.out.println("Exit code: " + ret);