mysql的高可用
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