MySQL 數據庫備份與恢復全方案：mysqldump、mysqlhotcopy、二進制日志、文件直拷深度解析

備份工具與適用場景總覽

1. mysqldump —— 最通用的邏輯備份

核心參數精選

• --single-transaction：InnoDB 熱備份關鍵，啟動事務快照，不阻塞寫入。
• --master-data=1：輸出 CHANGE MASTER TO 語句，記錄 binlog 位置，構建從庫必備。
• --flush-logs：備份前滾動 binlog，便於增量恢復切點。
• --extended-insert=FALSE：生成單行 INSERT，便於異構導入（如 PostgreSQL），犧牲導入速度換兼容性。
• --hex-blob：二進制列（BLOB/BINARY）以十六進制導出，避免編碼問題。
• --routines --triggers：導出存儲過程、觸發器、事件。
• --quick：流式讀取大表，避免內存暴漲。

典型命令模板

# MyISAM 完全備份
mysqldump -u user -p --default-character-set=utf8 --opt --extended-insert=false --triggers -R --hex-blob --lock-all-tables db_name > db_name.sql

# InnoDB 熱備份（推薦生產）
mysqldump -u user -p --default-character-set=utf8 --opt --extended-insert=false --triggers -R --hex-blob --single-transaction db_name > db_name.sql

# 在線構建從庫基礎備份
mysqldump -u user -p --default-character-set=utf8 --opt --master-data=1 --single-transaction --flush-logs db_name > db_name.sql

恢復

# 方法一：客戶端管道
mysql -u user -p db_name < db_name.sql

# 方法二：客戶端內部 SOURCE
SOURCE /absolute/path/db_name.sql;

2. mysqlhotcopy —— MyISAM 極速物理備份

Perl 腳本，僅限類 Unix/NetWare。原理：LOCK TABLES -> FLUSH TABLES -> cp/scp。

# 備份單庫
mysqlhotcopy -h localhost -u user -p pwd db_name /backup/dir

# 正則匹配多庫
mysqlhotcopy -h localhost -u user -p pwd db_name./regex/ /backup/dir

恢復： 直接拷貝回目錄 datadir，修正屬主：chown -R mysql:mysql /datadir/db_name。

3. SQL 語法備份（了解即可，不推薦常規使用）

• BACKUP TABLE tbl TO '/path/'; 僅拷貝 .frm/.MYD，不含索引，恢復需重建索引慢。
• SELECT * INTO OUTFILE '/path/tbl.txt' FROM tbl; 導出文本，配合 LOAD DATA INFILE 導入，需 FILE 權限。

4. 二進制日志 —— 增量備份與時間點恢復核心

啟用

[mysqld]
server-id=1
log-bin=binlog
log-bin-index=binlog.index

重啟後生成 binlog.000001 等。

增量備份操作

FLUSH LOGS;  -- 切換新 binlog，舊文件即為增量增量集
cp binlog.000001 /backup/

查看與恢復

# 查看
mysqlbinlog /backup/binlog.000001

# 恢復（管道直入）
mysqlbinlog /backup/binlog.000001 | mysql -u user -p db_name

5. 直接拷貝數據文件 —— 物理冷/熱備份

# 熱備份前加鎖保證一致性
FLUSH TABLES WITH READ LOCK;
# 拷貝 datadir 及 InnoDB 日志 (ib_logfile*)
cp -r /var/lib/mysql /backup/mysql_`date +%F`
UNLOCK TABLES;

恢復：停庫 -> 覆蓋 datadir -> 啟庫。InnoDB 需同時備份 ib_logfile* 以支持崩潰恢復。

6. 備份策略建議

• 中等業務： 首次全量，每日增量（binlog），每周全量。
• 核心高併發： 每日全量，每小時增量。採用主從復制，在從庫備份，零業務影響。

7. 維護與災難恢復

啟用 InnoDB 自動恢復：innodb_force_recovery = 4（僅啟動時讀取日志修復，不可寫入）。