MySQL 雙主複製 (Master-Master) 配置全流程：Keepalived 高可用架構實戰

架構拓撲

                    ┌─────────────────┐
                    │   VIP: 192.168.1.200   │  ← Keepalived 虛擬 IP
                    └────────┬────────┘
                             │
              ┌──────────────┴──────────────┐
              ▼                             ▼
    ┌───────────────────┐           ┌───────────────────┐
    │ Master1: 192.168.1.201 │◄───────►│ Master2: 192.168.1.202 │
    │ server_id = 1           │           │ server_id = 2           │
    │ log_bin = mysql-bin     │           │ log_bin = mysql-bin     │
    └───────────────────┘           └───────────────────┘
              ▲                             ▲
              │  雙向異步複製 (雙主)           │
              └─────────────────────────────┘

前置條件

• OS：CentOS 5.4+ / 7.x / 8.x
• MySQL：5.0.89+ / 5.7+ / 8.0+
• Keepalived：1.1.20+
• 兩節點時間同步（ntpdate/chrony）
• 防火牆放行 3306、VRRP 協議（組播 224.0.0.18）

步驟一：MySQL 雙主複製配置

1. 修改 my.cnf 兩節點通用

[mysqld]
server_id = 1          # Master2 改為 2
log_bin = mysql-bin
binlog_format = ROW    # 推薦 ROW 格式，避免非確定函數複製問題
sync_binlog = 1        # 事務提交強制刷盤，保證不丟數據
innodb_flush_log_at_trx_commit = 1
gtid_mode = ON         # 5.6+ 建議啟用 GTID
enforce_gtid_consistency = ON
log_slave_updates = 1  # 關鍵：從庫執行的事務也寫 binlog，支援鏈式複製
auto_increment_offset = 1   # Master1
auto_increment_increment = 2
# Master2: offset=2, increment=2

2. Master1 建立複製賬號並查看 Binlog 位置

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl_pass_123';
mysql> FLUSH PRIVILEGES;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      374 |              |                  |
+------------------+----------+--------------+------------------+

3. Master2 配置指向 Master1

mysql> CHANGE MASTER TO
  MASTER_HOST='192.168.1.201',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_pass_123',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=374,
  MASTER_AUTO_POSITION=1;  -- GTID 模式下使用，忽略 FILE/POS
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
-- 關鍵：Slave_IO_Running=Yes, Slave_SQL_Running=Yes, Seconds_Behind_Master=0

4. Master2 建立複製賬號並查看 Binlog 位置

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl_pass_123';
mysql> SHOW MASTER STATUS;
-- 記錄 File/Position

5. Master1 配置指向 Master2

mysql> CHANGE MASTER TO
  MASTER_HOST='192.168.1.202',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_pass_123',
  MASTER_LOG_FILE='mysql-bin.00000X',
  MASTER_LOG_POS=YYY,
  MASTER_AUTO_POSITION=1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

6. 驗證雙向同步

-- Master1 建表
CREATE TABLE test_sync (id INT PRIMARY KEY, val VARCHAR(20));
INSERT INTO test_sync VALUES (1, 'from_master1');

-- Master2 查詢
SELECT * FROM test_sync;  -- 應顯示 1 行

-- Master2 插入
INSERT INTO test_sync VALUES (2, 'from_master2');

-- Master1 查詢
SELECT * FROM test_sync;  -- 應顯示 2 行

步驟二：Keepalived 高可用配置

Master1: /etc/keepalived/keepalived.conf

global_defs {
  router_id MYSQL_MASTER1
}

vrrp_script chk_mysql {
  script "/etc/keepalived/check_mysql.sh"
  interval 2
  weight -20
  fall 2
  rise 1
}

vrrp_instance VI_1 {
  state MASTER
  interface eth0
  virtual_router_id 51
  priority 100
  advert_int 1
  authentication {
    auth_type PASS
    auth_pass 123456
  }
  virtual_ipaddress {
    192.168.1.200/24 dev eth0
  }
  track_script {
    chk_mysql
  }
}

Master2: /etc/keepalived/keepalived.conf

global_defs {
  router_id MYSQL_MASTER2
}

vrrp_script chk_mysql {
  script "/etc/keepalived/check_mysql.sh"
  interval 2
  weight -20
  fall 2
  rise 1
}

vrrp_instance VI_1 {
  state BACKUP
  interface eth0
  virtual_router_id 51
  priority 90
  advert_int 1
  authentication {
    auth_type PASS
    auth_pass 123456
  }
  virtual_ipaddress {
    192.168.1.200/24 dev eth0
  }
  track_script {
    chk_mysql
  }
}

健康檢查腳本: /etc/keepalived/check_mysql.sh

#!/bin/bash
MYSQL_USER="monitor"
MYSQL_PASS="monitor_pass"
MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"

mysql -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASS -e "SELECT 1;" >/dev/null 2>&1
if [ $? -ne 0 ]; then
  exit 1
fi
exit 0

chmod +x /etc/keepalived/check_mysql.sh
# 建立監控用戶
GRANT SELECT ON *.* TO 'monitor'@'localhost' IDENTIFIED BY 'monitor_pass';

關鍵參數解析

常見問題與排查

生產環境加固建議

1. 半同步複製：rpl_semi_sync_master=ON，至少一個從庫收到 binlog 才提交，RPO=0
2. 只讀保護：備庫 read_only=ON，防止誤寫入（VIP 飄移後自動設置需腳本配合）
3. 延遲從庫：部署 1 小時延遲從庫，防誤操作（DROP TABLE）快速恢復
4. 監控告警：Seconds_Behind_Master > 30、Slave_IO_Running!=Yes、VIP 狀態變更 即時告警
5. 定期校驗：每週 pt-table-checksum 全量一致性檢查