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

MySQL 熱備份 (主從複寫) 手工搭建全流程：MyISAM/InnoDB 混合表環境

編輯：MySQL 綜合教學

環境假設

主庫 A (Master)：IP 10.20.1.21，運行業務，含 MyISAM + InnoDB 混合表
從庫 B (Slave)：IP 10.20.1.22，全新機器，作為熱備
目標庫：`stv`
忽略庫：`mysql`、`test` (系統庫不同步)

核心原理

MySQL 主從複寫基於 **Binlog (主庫) → Relay Log (從庫) → SQL Thread 重放** 三階段。InnoDB 支援熱備 (不鎖表)，MyISAM 需全局讀鎖獲取一致性快照。

步驟 1：主庫 A 一致性基線備份 (關鍵)

# 1. 停止寫入 (業務層面) 或加全局讀鎖 (MyISAM 必須)
mysql -u root -p -e "FLUSH TABLES WITH READ LOCK;"

# 2. 另開終端導出數據 (含結構+數據)
mysqldump -u root -p --single-transaction --master-data=2 --databases stv > /backup/stv_$(date +%F).sql
# --single-transaction: InnoDB 熱備不鎖表
# --master-data=2: 註釋記錄 Binlog 位置 (CHANGE MASTER TO ...)

# 3. 釋放鎖
mysql -u root -p -e "UNLOCK TABLES;"

# 4. 複製備份文件到從庫 B (scp/rsync)
scp /backup/stv_*.sql root@10.20.1.22:/backup/

步驟 2：主庫 A 配置複寫用戶與 Binlog

# 1. 建立複寫專用賬號 (僅允許從庫 IP)
CREATE USER 'repl_user'@'10.20.1.22' IDENTIFIED BY 'StrongReplPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.20.1.22';
FLUSH PRIVILEGES;

# 2. 修改 my.cnf [mysqld] 區塊
[mysqld]
server-id = 1                    # 唯一，正整數
log-bin = mysql-bin              # 啟用 Binlog
binlog_format = ROW              # ROW 格式最安全，避免非確定性函數問題
expire_logs_days = 7             # 自動清理舊 Binlog
binlog-ignore-db = mysql,test    # 忽略系統庫
binlog-do-db = stv               # 僅同步業務庫 (可選，建議從庫端過濾)

# 3. 重啟 MySQL 生效
systemctl restart mysqld

步驟 3：從庫 B 初始化與配置

# 1. 建立空庫
mysqladmin -u root -p create stv

# 2. 導入基線數據
mysql -u root -p stv < /backup/stv_2023-01-15.sql

# 3. 修改 my.cnf [mysqld] 區塊
[mysqld]
server-id = 2                    # 唯一，與主庫不同
relay-log = relay-bin            # Relay Log 文件名
log-slave-updates = 1            # 級聯複寫時需開啟
read-only = 1                    # 防誤寫入 (超級用戶除外)
replicate-do-db = stv            # 僅同步業務庫
replicate-ignore-db = mysql,test # 忽略系統庫
master-connect-retry = 60        # 斷線重連間隔 (秒)

# 4. 重啟 MySQL
systemctl restart mysqld

步驟 4：建立主從關聯並啟動複寫

# 在從庫 B 執行 (參考備份文件中的 CHANGE MASTER TO 註釋行)
CHANGE MASTER TO
    MASTER_HOST = '10.20.1.21',
    MASTER_PORT = 3306,
    MASTER_USER = 'repl_user',
    MASTER_PASSWORD = 'StrongReplPass123!',
    MASTER_LOG_FILE = 'mysql-bin.000013',  -- 來自 --master-data=2 記錄
    MASTER_LOG_POS = 15684;

START SLAVE;

步驟 5：驗證複寫狀態

# 主庫
SHOW MASTER STATUS;

# 從庫 (關鍵指標)
SHOW SLAVE STATUS\G

# 必須為 Yes 的兩項：
Slave_IO_Running: Yes      -- IO 線程連接主庫、讀取 Binlog 正常
Slave_SQL_Running: Yes     -- SQL 線程重放 Relay Log 正常

# 其他關鍵指標
Seconds_Behind_Master: 0   -- 延遲秒數，0 為實時
Last_IO_Error:             -- 空為無錯誤
Last_SQL_Error:            -- 空為無錯誤

步驟 6：功能驗證

# 主庫寫入測試
INSERT INTO stv.test_table (name) VALUES ('repl_test_001');

# 從庫查詢驗證 (應秒級可見)
SELECT * FROM stv.test_table WHERE name = 'repl_test_001';

MyISAM 表特殊處理說明

若庫內**純 MyISAM**，可簡化為**冷備份文件拷貝**：

主庫 `FLUSH TABLES WITH READ LOCK;`
直接複製 `datadir/stv/` 目錄到從庫對應位置
從庫啟動、配置 `server-id`、`relay-log`、執行 `CHANGE MASTER TO`、`START SLAVE`

混合引擎環境**必須用 mysqldump + --single-transaction** 確保 InnoDB 一致性，MyISAM 表在導出瞬間會短暫鎖表 (導出該表時)。

常見故障排查

現象	排查方向
Slave_IO_Running: Connecting	網路不通、防火牆、MASTER_HOST/PORT/USER/PASS 錯誤、主庫 Binlog 未啟用
Slave_SQL_Running: No	`Last_SQL_Error` 具體錯誤：主鍵衝突、外鍵失敗、字符集不匹配、表結構差異
Seconds_Behind_Master 偏大	從庫硬體弱、長事務、大批次 DDL、單線程 SQL Thread 瓶頸 (可升級多線程複寫)
Relay Log 損壞/丟失	`STOP SLAVE; RESET SLAVE; CHANGE MASTER TO ...; START SLAVE;` 重新同步

總結

手工搭建 MySQL 主從熱備份，核心在於：**一致性基線備份 (mysqldump --single-transaction --master-data=2) + Binlog 位置精準對齊 + server-id 唯一 + 複寫專用賬號最小權限 + 雙 Yes 驗證**。混合引擎環境以 InnoDB 熱備為準，MyISAM 短暫鎖表可接受。生產環境建議升級 GTID 模式 + 多線程複寫 + 監控告警體系。