從零搭建 MySQL MMM 高可用架構完整實戰

從零搭建 MySQL MMM 高可用架構完整實戰

MySQL Multi-Master Replication Manager (MMM) 可實現雙主多從的自動故障切換與讀寫分離。本文記錄在雲平台上用 5 台機器（1 管理 + 2 主 + 2 從）搭建的完整過程，含複製配置、用戶授權、數據同步、MMM 安裝配置、啟動驗證及兩大坑點解決。

拓撲規劃

虛擬 IP (VIP)：

一、配置 MySQL 複製 (雙主雙從)

1. 安裝 MySQL 5.1.52 (yum)

2. 修改 /etc/my.cnf [mysqld] 區段

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
default-storage-engine = innodb
replicate-ignore-db = mysql
binlog-ignore-db = mysql
server-id = 1  # 每台不同：1,2,3,4
log-bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-bin.relay
relay_log_index = /var/log/mysql/mysql-bin.relay.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1

關鍵：/var/log/mysql 目錄需建立並 chown -R mysql:mysql、權限 755，否則報 Errcode: 13。

3. 重啟驗證

service mysqld restart
mysql -e "SHOW MASTER STATUS;"
ls /var/log/mysql/mysql-bin.*

二、建立複製所需三用戶

GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.84.%' IDENTIFIED BY 'monitor';
GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.85.%' IDENTIFIED BY 'agent';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.85.%' IDENTIFIED BY 'replication';

三、同步主從數據

1. 主庫 FLUSH TABLES WITH READ LOCK; → SHOW MASTER STATUS; 記錄 File/Position → mysqldump --all-databases > dump.sql → UNLOCK TABLES;
2. scp 傳送 dump.sql 到其它節點 → mysql < dump.sql 導入。
3. 從庫執行 CHANGE MASTER TO ... 指向主庫 File/Position → START SLAVE; → SHOW SLAVE STATUS\G 確認 Slave_IO_Running: Yes、Slave_SQL_Running: Yes。
4. 反向配置：db2 做主、db1 做從，重複上述步驟形成雙主。

四、安裝 MMM (yum)

# 管理節點
yum -y install mysql-mmm-monitor perl-Time-HiRes

# 數據節點 (db1-db4)
yum -y install mysql-mmm-agent

五、配置 MMM

1. mmm_common.conf (所有節點同步)

active_master_role writer


  cluster_interface eth0
  pid_path /var/run/mysql-mmm/mmm_agentd.pid
  bin_path /usr/libexec/mysql-mmm/
  replication_user replication
  replication_password replication
  agent_user mmm_agent
  agent_password agent


 ip 192.168.85.167 mode master peer db2 
 ip 192.168.85.169 mode master peer db1 
 ip 192.168.85.171 mode slave 
 ip 192.168.85.168 mode slave 

 hosts db1, db2 ips 192.168.85.200 mode exclusive 
 hosts db2, db3, db4 ips 192.168.85.201, 192.168.85.202, 192.168.85.203 mode balanced 

2. mmm_agent.conf (各數據節點修改 this)

include mmm_common.conf
this db1   # db2 改 this db2，以此類推

3. mmm_mon.conf (管理節點)

include mmm_common.conf


  ip 192.168.84.174
  pid_path /var/run/mysql-mmm/mmm_mond.pid
  bin_path /usr/libexec/mysql-mmm
  status_path /var/lib/mysql-mmm/mmm_mond.status
  ping_ips 192.168.85.167, 192.168.85.169, 192.168.85.171, 192.168.85.168
  auto_set_online 60



  monitor_user mmm_monitor
  monitor_password monitor


debug 0

六、啟動並驗證

# 數據節點
chkconfig mysql-mmm-agent on
service mysql-mmm-agent start

# 管理節點
chkconfig mysql-mmm-monitor on
service mysql-mmm-monitor start

# 查看狀態
mmm_control show

預期輸出：

db1(192.168.85.167) master/ONLINE. Roles: writer(192.168.85.200)
db2(192.168.85.169) master/ONLINE. Roles: reader(192.168.85.202)
db3(192.168.85.171) slave/ONLINE. Roles: reader(192.168.85.203)
db4(192.168.85.168) slave/ONLINE. Roles: reader(192.168.85.201)

七、遇到的兩個坑及解決

1. 監控啟動失敗：Can't connect to monitor daemon
   開啟 debug 1 重啟 → 日誌顯示缺 perl-Time-HiRes → yum -y install perl-Time-HiRes 解決。
2. Agent 不可達：Warning: agent on host dbX is not reachable
   telnet IP 9989 提示 No route to host → 關閉各節點防火牆 (service iptables stop 或 firewall-cmd) → 重啟監控恢復正常。
   生產環境請精準開放 9989、3306 等埠而非關閉防火牆。

參考文檔

• 官方指南：https://mysql-mmm.org/mmm2:guide
• 手冊：https://mysql-mmm.org/mysql-mmm.html

MMM 已停止維護，生產新項目建議考慮 MHA、Orchestrator、Galera Cluster 或 ProxySQL + GTID 方案。