MySQL 雙機熱備 (Replication) 實戰配置完整指南

核心原理

MySQL Replication 透過 二進制日誌 實現：主庫寫入 Binlog，從庫透過 I/O 執行緒讀取主庫 Binlog 寫入本地 Relay Log，再由 SQL 執行緒重放實現資料同步。MySQL 4.0+ 內建完整支援，無需手動處理日誌檔案。

權限需求

從庫連線帳號需具備兩項專用權限：

• REPLICATION SLAVE：讀取主庫 Binlog (核心權限)。
• REPLICATION CLIENT：查詢主從狀態 (SHOW MASTER/SLAVE STATUS)。

資料重載 (LOAD TABLE/DATA FROM MASTER) 額外需 RELOAD、SUPER、SELECT 權限。

主庫配置

1. 建立同步帳號

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* 
TO 'backup'@'192.168.1.2' IDENTIFIED BY 'password'
WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0;

2. 啟用 Binlog 並指定同步資料庫

編輯 /etc/my.cnf ([mysqld] 區段)：

[mysqld]
server-id = 1                    # 唯一識別碼，主從必須不同
log-bin                          # 啟用二進制日誌
binlog-do-db = pa                # 僅同步 pa 資料庫 (可多行指定多個)
max_binlog_size = 104857600      # 單檔最大 100MB，自動輪替

從庫配置

編輯 /etc/my.cnf：

[mysqld]
server-id = 2                    # 唯一，不可與主庫相同
master-host = 192.168.1.1        # 主庫 IP
master-user = backup             # 同步帳號
master-password = 12345          # 同步密碼
master-port = 3306
master-connect-retry = 60        # 重連間隔 (秒)
replicate-do-db = pa             # 僅同步 pa 資料庫

常用管理指令

雙向熱備 (雙主)

在兩台伺服器上分別配置對方為 Master，並設定不同的 server-id、auto_increment_offset、auto_increment_increment 避免主鍵衝突。MySQL 5.0+ 正式支援。

故障排查提示

• SHOW SLAVE STATUS\G 檢查 Slave_IO_Running、Slave_SQL_Running 是否為 Yes。
• Seconds_Behind_Master 非 0 表延遲，需檢查網路/負載。
• 同步中斷常見原因：主鍵衝突、資料型別不符、網路不穩、Binlog 損壞。