MySQL 高可用架構實戰：Keepalived + HAProxy 實現雙主熱備與讀寫分離負載均衡

MySQL 高可用架構實戰：Keepalived + HAProxy 雙主互備

一、架構目標

• 兩台物理節點：192.168.5.11、192.168.5.12
• 虛擬 IP (VIP)：192.168.5.111 對外提供服務
• Keepalived：VRRP 協議實現 VIP 漂移、節點健康檢查
• HAProxy：TCP 層（3306）輪詢負載均衡、後端健康檢查
• MySQL：雙向復制（雙主互備），保證數據強一致

二、基礎環境準備

# 關閉防火牆（生產請配置規則）
service iptables stop; chkconfig iptables off

# 目錄結構
mkdir -p /opt/keepalived /opt/haproxy /opt/scripts /opt/log
# 軟件包放 /opt/soft/
# keepalived-1.2.7.tar.gz, haproxy-1.4.20.tar.gz

三、MySQL 基礎安裝與測試數據

yum install -y mysql-server
service mysqld start

# 兩節點分別建表、插入不同數據、建同帳號
mysql> USE test;
mysql> CREATE TABLE mywait(name CHAR(9), phone CHAR(14));
-- node1
mysql> INSERT INTO mywait VALUES('wait', '15000000000');
-- node2
mysql> INSERT INTO mywait VALUES('chen', '15611111111');
mysql> GRANT ALL ON test.* TO 'diaosi'@'%' IDENTIFIED BY '123456';
mysql> FLUSH PRIVILEGES;

四、Keepalived 編譯安裝與配置

yum install -y libnl-devel
tar xf keepalived-1.2.7.tar.gz
cd keepalived-1.2.7
./configure --prefix=/opt/keepalived --with-kernel-dir=/usr/src/kernels/$(uname -r)
make && make install

# 複製啟動腳本與配置
cp /opt/keepalived/sbin/keepalived /usr/sbin/
cp /opt/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
cp /opt/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
mkdir /etc/keepalived
cp /opt/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
chkconfig keepalived on

主節點 keepalived.conf 關鍵段

global_defs { notification_email { } }

vrrp_script mysql_chk {
  script "/opt/scripts/mysql_chke.sh"
  interval 2
  weight 2
}

vrrp_instance VI_1 {
  state MASTER
  interface eth0
  virtual_router_id 11
  priority 150
  advert_int 1
  authentication { auth_type PASS; auth_pass 111; }
  virtual_ipaddress { 192.168.5.111/24 }
  track_script { mysql_chk }
  notify_master /opt/scripts/start_haproxy.sh
  notify_fault  /opt/scripts/stop_keepalived.sh
  notify_stop   /opt/scripts/stop_haproxy.sh
}

備節點：state BACKUP、priority 100，其餘相同。

健康檢查腳本 /opt/scripts/mysql_chke.sh

#!/bin/bash
a=$(ps -C mysqld --no-header | wc -l)
if [ $a -eq 0 ]; then
  sleep 3
  /sbin/service keepalived stop
  echo "$(date) stop keepalived" >> /opt/log/stop_keepalived.log
fi

五、HAProxy 編譯安裝與配置

tar xf haproxy-1.4.20.tar.gz
cd haproxy-1.4.20
make TARGET=linux26 PREFIX=/opt/haproxy install
mkdir -p /opt/haproxy/conf /opt/haproxy/logs
touch /opt/haproxy/conf/haproxy.cfg

haproxy.cfg 核心配置

global
  log 127.0.0.1 local0 info
  maxconn 4096
  uid 0; gid 0; daemon; nbproc 1

defaults
  mode tcp
  option redispatch
  retries 3
  timeout connect 5000
  timeout client 50000
  timeout server 50000
  timeout check 2000

listen proxy
  bind 192.168.5.111:3306
  mode tcp
  balance roundrobin
  log 127.0.0.1 local0 info
  server db1 192.168.5.11:3306 check inter 1200 rise 2 fall 3 weight 1
  server db2 192.168.5.12:3306 check inter 1200 rise 2 fall 3 weight 1

listen haproxy_stats
  bind 192.168.5.111:8888
  mode http
  option httplog
  stats uri /status
  stats realm Haproxy Manager
  stats auth admin:admin

MySQL 綁定物理 IP 避免端口衝突

# node1 /etc/my.cnf
[mysqld]
bind-address=192.168.5.11

# node2
bind-address=192.168.5.12
service mysqld restart

Keepalived 通知腳本啟動/停止 HAProxy

# start_haproxy.sh
#!/bin/bash
sleep 5
if ip addr | grep -q 192.168.5.111; then
  /opt/haproxy/sbin/haproxy -f /opt/haproxy/conf/haproxy.cfg
  echo "$(date) haproxy started" >> /opt/log/start_haproxy.log
fi

# stop_haproxy.sh
#!/bin/bash
kill -9 $(pidof haproxy)
echo "$(date) haproxy stopped" >> /opt/log/stop_haproxy.log

六、MySQL 雙主復制配置

# 兩節點同樣執行
mysql> CREATE DATABASE db1;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'diaosi1'@'%' IDENTIFIED BY '123456';
mysql> FLUSH PRIVILEGES;

# node1 my.cnf
server_id=1
log_bin=mysqlbinlog
log_bin_index=mysqlbinlog-index
log_slave_updates=1
relay_log=relay-log
replicate_do_db=db1

# node2 my.cnf
server_id=2
log_bin=mysqlbinlog
log_bin_index=mysqlbinlog-index
log_slave_updates=1
relay_log=relay-log
replicate_do_db=db1

service mysqld restart

建立雙向復制關係

# node1 → node2
mysql> CHANGE MASTER TO MASTER_HOST='192.168.5.12', MASTER_PORT=3306,
  MASTER_USER='diaosi1', MASTER_PASSWORD='123456',
  MASTER_LOG_FILE='mysqlbinlog.000001', MASTER_LOG_POS=98;
mysql> START SLAVE;

# node2 → node1 (同理，換 IP 和日志座標)
mysql> SHOW SLAVE STATUS\G
# 關鍵：Slave_IO_Running=Yes, Slave_SQL_Running=Yes

七、全鏈路驗證

1. 客戶端連 VIP：mysql -udiaosi -p123456 -h 192.168.5.111 -e "SELECT * FROM test.mywait;" → 結果在 node1/node2 間輪詢
2. 殺掉 node1 mysqld → Keepalived 檢測失敗 → VIP 漂移到 node2 → HAProxy 重啟 → 客戶端無感切換（2-3 秒中斷）
3. 恢復 node1 → VIP 漂回 → 雙主復制自動追趕數據
4. 寫入 db1 表 → 另一節點秒級同步

八、已知問題與優化方向

• ps -C mysqld 偽進程問題：服務掛掉但殘留進程導致檢查失效，建議改用 mysqladmin ping 或 TCP 端口探活
• HAProxy 支援權重、最少連接、一致性哈希、Cookie 親和等多種調度算法
• 生產環境建議：MHA / Orchestrator / GTID 復制 / ProxySQL 替代手工搭建