1. 環境準備
- 2臺MySQL服務器（node1: 192.168.1.101，node2: 192.168.1.102）
- 2臺HAProxy + Keepalived服務器（haproxy1: 192.168.1.103，haproxy2: 192.168.1.104）
- 虛擬IP（VIP: 192.168.1.100）
2. MySQL雙主復制配置
2.1 安裝MySQL (所有節點)
# CentOS
yum install -y mysql-server
systemctl start mysqld
systemctl enable mysqld
# Ubuntu
apt install -y mysql-server
systemctl start mysql
systemctl enable mysql
2.2 配置主主復制
Node1配置 (/etc/my.cnf):
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=ROW
auto_increment_increment=2
auto_increment_offset=1
Node2配置 (/etc/my.cnf):
[mysqld]
server-id=2
log-bin=mysql-bin
binlog-format=ROW
auto_increment_increment=2
auto_increment_offset=2
2.3 配置復制賬戶
-- 在Node1執行
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
-- 在Node2執行
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
2.4 啟動復制
-- 在Node2執行
CHANGE MASTER TO
? MASTER_HOST='192.168.1.101',
? MASTER_USER='repl',
? MASTER_PASSWORD='password',
? MASTER_LOG_FILE='mysql-bin.000001',
? MASTER_LOG_POS=154;
START SLAVE;
-- 在Node1執行
CHANGE MASTER TO
? MASTER_HOST='192.168.1.102',
? MASTER_USER='repl',
? MASTER_PASSWORD='password',
? MASTER_LOG_FILE='mysql-bin.000001',
? MASTER_LOG_POS=154;
START SLAVE;
3. HAProxy配置
3.1 安裝HAProxy (兩臺負載均衡器)
yum install -y haproxy? # CentOS
apt install -y haproxy? # Ubuntu
3.2 配置文件 (/etc/haproxy/haproxy.cfg)
global
??? log /dev/log local0
??? maxconn 4000
??? user haproxy
??? group haproxy
defaults
??? mode tcp
??? timeout connect 5s
??? timeout client 30s
??? timeout server 30s
listen mysql-cluster
??? bind *:3306
??? mode tcp
??? balance roundrobin
??? option tcp-check
??? server mysql1 192.168.1.101:3306 check inter 2000 rise 2 fall 3
??? server mysql2 192.168.1.102:3306 check inter 2000 rise 2 fall 3
3.3 啟動服務
systemctl restart haproxy
systemctl enable haproxy
4. Keepalived配置
4.1 安裝Keepalived
yum install -y keepalived? # CentOS
apt install -y keepalived? # Ubuntu
4.2 配置文件 (/etc/keepalived/keepalived.conf)
haproxy1配置：
vrrp_script chk_haproxy {
??? script "killall -0 haproxy"
??? interval 2
??? weight 2
}
vrrp_instance VI_1 {
??? state MASTER
??? interface eth0
??? virtual_router_id 51
??? priority 100
??? advert_int 1
??? authentication {
??????? auth_type PASS
??????? auth_pass 1111
??? }
??? virtual_ipaddress {
??????? 192.168.1.100/24
??? }
??? track_script {
??????? chk_haproxy
??? }
}
haproxy2配置：
vrrp_instance VI_1 {
??? state BACKUP
??? interface eth0
??? virtual_router_id 51
??? priority 90
??? ...
}
4.3 啟動服務
systemctl restart keepalived
systemctl enable keepalived
5. 故障轉移測試
5.1 雙主復制測試
-- 在Node1創建測試數據
CREATE DATABASE ha_test;
USE ha_test;
CREATE TABLE test_table (id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(20));
INSERT INTO test_table (data) VALUES ('node1_data');
-- 在Node2查詢數據
SELECT * FROM ha_test.test_table;
5.2 HAProxy負載均衡測
mysql -h 192.168.1.100 -u root -p -e "SHOW VARIABLES LIKE 'server_id'"
# 應交替顯示server_id=1和server_id=2
5.3 Keepalived故障轉移測試
- 在haproxy1停止服務：
systemctl stop haproxy
- 觀察虛擬IP漂移：
ip addr show eth0
)
