MySQL 高可用
架构
| 组件 | 作用 | 核心优势 |
|---|---|---|
| MySQL 主主复制 | 两台 MySQL 互为主从，双向同步数据 | 数据冗余、无单点、支持读写分离 |
| HAProxy | 数据库负载均衡、健康检查、连接管理 | 读写分发、自动剔除异常节点 |
| Keepalived | 提供 VIP、故障漂移、HAProxy 高可用 | 秒级切换、对业务透明 |
整体访问链路
客户端 → VIP(Keepalived) → HAProxy → MySQL Master1/Master2环境
| 节点 | IP | 角色 |
|---|---|---|
| Master1 | 192.168.1.101 | MySQL 主、HAProxy、Keepalived 主 |
| Master2 | 192.168.1.102 | MySQL 主、HAProxy、Keepalived 备 |
| VIP | 192.168.1.200 | 对外统一入口 |
前置要求
- 关闭防火墙 / 开放 3306、8080、VRRP 端口
- 关闭 SELinux 或设为 Permissive
- 时间同步（NTP）
- 两台 MySQL 版本一致
MySQL 双主复制
1. Mater1 my.cnf 配置
[mysqld] server-id = 101 log-bin = mysql-bin binlog-format = ROW auto-increment-increment= 2 auto-increment-offset = 1 relay-log = mysql-relay-bin log-slave-updates = 1 sync-binlog = 1 innodb_flush_log_at_trx_commit = 12. Master2 my.cnf
[mysqld] server-id = 102 log-bin = mysql-bin binlog-format = ROW auto-increment-increment= 2 auto-increment-offset = 2 relay-log = mysql-relay-bin log-slave-updates = 1 sync-binlog = 1 innodb_flush_log_at_trx_commit = 13. 创建复制账号（两台都执行）
CREATEUSER'repl'@'192.168.1.%'IDENTIFIEDBY'Repl@123';GRANTREPLICATIONSLAVEON*.*TO'repl'@'192.168.1.%';FLUSHPRIVILEGES;4. 建立双向复制
节点 1 执行
show master status;记录 File & Position节点 2 执行：
change mastertomaster_host='192.168.1.101',master_user='repl',master_password='Repl@123',master_log_file='mysql-bin.000001',master_log_pos=154;startslave;showmasterstatus;反向配置节点 1 指向节点 2，完成主主
HAProxy 配置
1. 安装
yum-yinstallhaproxy2. haproxy.cfg
vim /etc/haproxy/haproxy.cfg
global log 127.0.0.1 local2 chroot /var/lib/haproxy pidfile /var/run/haproxy.pid user haproxy group haproxy daemon maxconn 4000 defaults # MySQL使用四层代理 mode tcp log global option tcplog retries 3 timeout connect 5s timeout client 50s timeout server 50s listen mysql-read-write # 监听 bind 0.0.0.0:3306 mode tcp balance roundrobin option mysql-check user haproxy_check # 配置MySQL服务 server mysql1 192.168.1.101:3306 check weight 1 server mysql2 192.168.1.102:3306 check weight 1 listen stats bind 0.0.0.0:8080 mode http stats enable stats uri /haproxy-stats stats auth admin:admin3. MySQL 健康检查用户
CREATEUSER'haproxy_check'@'%'IDENTIFIEDBY'';FLUSHPRIVILEGES;4. 启动
systemctl start haproxy systemctlenablehaproxyKeepalived 配置
1. 安装
yuminstall-ykeepalived2. MySQL 检测脚本
#!/bin/bashMYSQL_USER="haproxy_check"MYSQL_HOST="localhost"MYSQL_PORT="3306"mysql -u$MYSQL_USER-h$MYSQL_HOST-P$MYSQL_PORT-e"SELECT 1">/dev/null2>&1if[$?-ne0];thensystemctl stop haproxyexit1fiexit0chmod+x /usr/local/bin/check_mysql.sh3. Mater1 keepalived.conf
global_defs { router_id mysql_ha_master } vrrp_script check_mysql { script "/usr/local/bin/check_mysql.sh" interval 2 weight -20 fall 3 rise 2 } vrrp_instance VI_1 { state MASTER interface eth0 virtual_router_id 51 priority 150 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.1.200/24 } track_script { check_mysql } }4. Master2 keepalived.conf
global_defs { router_id mysql_ha_backup } vrrp_script check_mysql { script "/usr/local/bin/check_mysql.sh" interval 2 weight -20 fall 3 rise 2 } vrrp_instance VI_1 { state BACKUP interface eth0 virtual_router_id 51 priority 100 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.1.200/24 } track_script { check_mysql } }5. 启动
systemctl start keepalived systemctlenablekeepalived