雙主同步讓兩台 MySQL 互為主從,任一寫入即同步至另一台,實現寫入高可用。本文基於 Ubuntu 12.04 + MySQL 5.1+ 完整記錄配置步驟、同步帳號建立、雙向 Change Master、驗證與延遲調優。
db_name-- On Server A
GRANT ALL PRIVILEGES ON db_name.* TO 'dbuser'@'192.168.1.3' IDENTIFIED BY 'dbpassword';
FLUSH PRIVILEGES;
-- On Server B
GRANT ALL PRIVILEGES ON db_name.* TO 'dbuser'@'192.168.1.2' IDENTIFIED BY 'dbpassword';
FLUSH PRIVILEGES;
[mysqld]
default-character-set=utf8
log-bin=mysql-bin
relay-log=relay-bin
relay-log-index=relay-bin-index
server-id=1 # A=1, B=2 必須不同
master-host=192.168.1.3 # 指向對方 IP
master-user=dbuser
master-password=dbpassword
master-port=3306
master-connect-retry=30
binlog-do-db=db_name
replicate-do-db=db_name
replicate-ignore-table=db_name.ignore_table
auto-increment-increment=2 # 兩台總數
auto-increment-offset=1 # A=1, B=2 錯開自增值,避免主鍵衝突
FLUSH TABLES WITH READ LOCK; mysqldump ...; UNLOCK TABLES;STOP SLAVE; LOAD DATA FROM MASTER; START SLAVE; (5.1 語法,5.6+ 用 CHANGE MASTER TO ...)SHOW SLAVE STATUS \G;
-- 關鍵指標
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
雙邊均為 Yes 即配置成功。
slave-net-timeout=30,檢查網路/磁碟/長 SQLauto_increment_increment/offset 必須錯開提醒:MySQL 5.6+ 建議使用 GTID 模式 (MASTER_AUTO_POSITION=1),配置更簡單且容錯性更強。