MySQL 雙主熱備（Master-Master）配置完整步驟詳解

MySQL 雙主熱備（Master-Master）配置完整步驟詳解

雙主熱備架構下，兩台 MySQL 互為主從，實現讀寫分離與高可用。本文記錄生產環境配置全過程，含權限授予、配置檔差異化、啟動驗證與常見錯誤處理。

前置條件

• 兩台伺服器網路互通（示例 IP：192.168.21.39、192.168.21.106）
• MySQL 版本一致，防火牆開放 3306 埠
• 資料目錄、Socket 路徑可不同

步驟一：建立同步複寫帳號

在兩台伺服器分別執行：

-- 庫1 (192.168.21.39) 授權給庫2
GRANT REPLICATION SLAVE ON *.* TO 'repdcs'@'192.168.21.106' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'repdcs'@'192.168.21.106' IDENTIFIED BY '123456';

-- 庫2 (192.168.21.106) 授權給庫1
GRANT REPLICATION SLAVE ON *.* TO 'repdcs'@'192.168.21.39' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'repdcs'@'192.168.21.39' IDENTIFIED BY '123456';

FLUSH PRIVILEGES;

步驟二：配置 my.cnf（差異化參數）

庫1 配置（server-id=2）

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=2
log-bin=mysqlbin
binlog_format=ROW
innodb_flush_log_at_trx_commit=1
sync_binlog=1
master-host=192.168.21.39
master-user=repdcs
master-pass=123456
master-connect-retry=60
replicate-do-db=dcs
slave-net-timeout=60

庫2 配置（server-id=1）

[mysqld]
datadir=/home/data/mysql
socket=/home/data/mysql/mysql.sock
server-id=1
log-bin=mysqlbin
binlog_format=ROW
innodb_flush_log_at_trx_commit=1
sync_binlog=1
master-host=192.168.21.106
master-user=repdcs
master-pass=123456
master-connect-retry=60
replicate-do-db=dcs
slave-net-timeout=60

關鍵差異：server-id 唯一、master-host 指向對方 IP、datadir/socket 依實際路徑設定。建議啟用 binlog_format=ROW 避免非確定性 SQL 複寫問題。

步驟三：重啟並驗證

service mysqld restart

-- 檢查從庫狀態
SHOW SLAVE STATUS\G

-- 關鍵指標應為 Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

常見錯誤與處理

錯誤：Last_Error: Error 'Can't create database 'dcs'; database exists'

原因：從庫已存在同名資料庫，複寫建庫語句衝突。

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE SQL_THREAD;
FLUSH PRIVILEGES;

核心參數速查表

避坑指南

• 雙主不建議同時雙寫：極易產生主鍵衝突、資料不一致。建議「單寫單備」或引入中間件（ProxySQL、MyCat）路由寫請求
• 啟用 auto_increment_offset + auto_increment_increment 避免主鍵衝突（若必須雙寫）
• 定期執行 pt-table-checksum 校驗主從一致性
• 監控 Seconds_Behind_Master、Slave_IO_Running、Slave_SQL_Running 三大指標