MySQL 數據庫備份與恢復全方案：mysqldump、mysqlhotcopy、二進制日誌、物理備份、策略制定

備份方案對比總覽

一、mysqldump 邏輯備份詳解

核心參數

# 生產環境推薦模板（InnoDB）
mysqldump -u backup_user -p \
  --single-transaction \
  --master-data=2 \
  --routines --triggers --events \
  --hex-blob \
  --quick \
  --max_allowed_packet=1G \
  --net_buffer_length=1M \
  db_name > db_name_$(date +%F).sql

# MyISAM 表優化
mysqldump -u backup_user -p \
  --lock-tables \
  --extended-insert=FALSE \
  --disable-keys \
  db_name > db_name.sql

參數詳解

• --single-transaction：InnoDB 熱備份核心，啟動一致性快照，不阻塞寫入
• --master-data=2：輸出 CHANGE MASTER TO 註釋，記錄備份時刻 Binlog 位置
• --quick：流式讀取，不緩存結果集到內存，大表必備
• --extended-insert=FALSE：單行 INSERT，便於部分恢復、調試，犧牲導入速度
• --disable-keys：導入時禁用索引，載入完再重建，MyISAM 大幅加速
• --hex-blob：二進制字段十六進制導出，避免編碼問題

還原方式

# 方式一：mysql 客戶端（推薦）
mysql -u root -p db_name < backup.sql

# 方式二：source 命令
mysql -u root -p
CREATE DATABASE db_name;
USE db_name;
SOURCE /path/backup.sql;

# 方式三：並行導入加速（需工具支持）
# mydumper/myloader,  或  sed -n '1,10000p' backup.sql | mysql ...

二、mysqlhotcopy（僅 MyISAM、已棄用）

# 備份
mysqlhotcopy -u backup -p pass -h localhost db_name /backup/dir/

# 還原
cp -rf /backup/dir/db_name /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/db_name

注意：MySQL 5.7+ 已移除，8.0 完全不支持。生產環境請使用 XtraBackup。

三、SQL 語法備份（BACKUP TABLE / SELECT INTO OUTFILE）

不推薦生產使用：功能受限、鎖表久、無事務保證、已棄用移除。

四、二進制日誌增量備份與 PITR

啟用 Binlog

[mysqld]
server-id = 1
log-bin = /data/binlog/mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 1G
sync_binlog = 1

增量備份操作

# 刷新日誌，生成新 binlog 文件
FLUSH LOGS;

# 備份舊 binlog 文件（除當前寫入文件外）
cp /data/binlog/mysql-bin.000001 /backup/binlog/

# 從庫備份額外文件
cp /data/binlog/master.info /backup/binlog/
cp /data/binlog/relay-log.info /backup/binlog/

PITR 時間點恢復

# 1. 全量恢復到備份時間點
mysql -u root -p db_name < full_backup.sql

# 2. 重放增量 binlog 到目標時間
mysqlbinlog --start-datetime="2024-01-15 10:00:00" \
            --stop-datetime="2024-01-15 14:30:00" \
            /backup/binlog/mysql-bin.000001 | mysql -u root -p db_name

# 或按位置恢復
mysqlbinlog --start-position=12345 --stop-position=67890 \
            /backup/binlog/mysql-bin.000001 | mysql -u root -p db_name

五、物理文件直接備份（冷備份）

# 1. 鎖表刷盤（保證一致性）
FLUSH TABLES WITH READ LOCK;

# 2. 拷貝數據目錄
cp -rf /var/lib/mysql /backup/mysql_$(date +%F)

# 3. 解鎖
UNLOCK TABLES;

# InnoDB 額外備份 redo log
cp /var/lib/mysql/ib_logfile* /backup/mysql_$(date +%F)/

六、備份策略制定

最佳架構：主從 + 從庫備份

Master (寫入) → Slave1 (讀取+備份) → Slave2 (延遲1h/備份)
                    ↓
              XtraBackup 物理熱備
              mysqldump 邏輯備份
              Binlog 增量備份

七、數據維護與災難恢復

表維護命令

-- MyISAM
CHECK TABLE tbl_name;
REPAIR TABLE tbl_name;
OPTIMIZE TABLE tbl_name;  -- 整理碎片、更新統計
ANALYZE TABLE tbl_name;   -- 更新統計信息

-- InnoDB
ALTER TABLE tbl_name ENGINE=InnoDB;  -- NULL 操作重建表，整理碎片
ANALYZE TABLE tbl_name;

InnoDB 損壞恢復

[mysqld]
innodb_force_recovery = 1  # 1-6 級別，逐級嘗試，4以上只讀
# 1: SRV_FORCE_IGNORE_CORRUPT
# 4: SRV_FORCE_NO_IBUF_MERGE (只讀)

生產環境工具推薦

• Percona XtraBackup：開源、InnoDB 熱備份、流式傳輸、增量、壓縮、並行
• mydumper/myloader：多線程邏輯備份/恢復、比 mysqldump 快 5-10 倍
• MySQL Enterprise Backup：官方商業工具
• 自動化腳本：封裝上述工具 + 上傳對象存儲 + 校驗 + 告警