萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 備份與恢復四大策略深度解析:從冷備份到主從複制

四大備份策略對比

策略工具/方式備份類型適用規模優點缺點
1. 直接拷貝檔案cp/rsync + FLUSH TABLES WITH READ LOCK全量物理備份小型、停機可接受最快、最簡單需鎖表停寫、無增量、跨版本風險
2. mysqlhotcopyPerl 腳本 (LOCK TABLES + FLUSH + cp/scp)全量物理備份小型 MyISAM 專用速度極快、支援單表僅限 MyISAM、需本機執行、已棄用
3. mysqldump邏輯備份 (SQL 陳述式)全量 + 增量 (配合 binlog)中型、異構遷移跨版本/引擎相容、可壓縮、可篩選大資料集慢、鎖表久、恢復慢
4. 主從複制Replication (Binlog + Relay Log)即時增量/熱備份大型、高可用架構零停機、異地災備、讀寫分離架構複雜、需監控延遲、非邏輯備份

策略一:直接拷貝檔案(不推薦生產)

-- 1. 鎖表刷盤
FLUSH TABLES WITH READ LOCK;
-- 2. 拷貝 data 目錄
cp -r /var/lib/mysql /backup/mysql_$(date +%F)
-- 3. 解鎖
UNLOCK TABLES;

恢復:停服務 → 覆蓋 data 目錄 → 啟動服務。

策略二:mysqlhotcopy(僅 MyISAM、已淘汰)

# 安裝 Perl DBD::mysql
perl Makefile.PL -mysql_config=/usr/bin/mysql_config
make && make install

# 每日定時備份腳本
0 3 * * * /root/mysqlbackup.sh >/dev/null 2>&1

注意:MySQL 5.7+ 已移除 mysqlhotcopy,請改用 mysqldump 或 xtrabackup。

策略三:mysqldump 邏輯備份(主流選擇)

全量備份

mysqldump -u root -p --single-transaction --routines --triggers --events \
  --master-data=2 --hex-blob db_name > backup_$(date +%F).sql
  • --single-transaction:InnoDB 熱備份不鎖表
  • --master-data=2:輸出 CHANGE MASTER 註解,利於建立從庫

增量備份(配合 Binlog)

  1. 啟用 Binlog:log_bin = mysql-bin
  2. 全量備份後記錄 SHOW MASTER STATUS; 的 File/Position
  3. 增量:mysqlbinlog --start-position=XXX mysql-bin.000001 > inc.sql

策略四:主從複制(高可用核心)

核心架構

  • Master:寫入 Binlog,Binlog Dump Thread 推送給 Slave
  • Slave:I/O Thread 拉取 Binlog 寫入 Relay Log → SQL Thread 重放

關鍵配置

[mysqld]
# Master
server-id = 1
log_bin = mysql-bin
binlog_format = ROW

# Slave
server-id = 2
relay_log = relay-bin
read_only = 1

建立複制步驟精簡版

  1. Master 建立複制帳號:GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'pass';
  2. Master FLUSH TABLES WITH READ LOCK → 記錄 Binlog 位置 → mysqldump --master-data=2 → UNLOCK TABLES
  3. 將 dump 載入 Slave,設定 CHANGE MASTER TO MASTER_HOST=..., MASTER_LOG_FILE=..., MASTER_LOG_POS=...;
  4. START SLAVE; → SHOW SLAVE STATUS\G 確認 Slave_IO_Running=Yes, Slave_SQL_Running=Yes

生產環境建議組合

  • 核心業務:主從複制 (HA) + 定時 mysqldump (邏輯備份/異地) + xtrabackup (物理快速恢復)
  • 監控指標:Seconds_Behind_Master、Binlog 增長率、磁碟空間、備份成功率/耗時
  • 演練:每季演練全量/增量恢復、主從切換、PITR (時間點恢復)
copyright © 萬盛學電腦網 all rights reserved