MySQL高可用架构实战：主主复制+Keepalived+HAProxy

技能目标理解 MySQL 高可用的核心概念与企业级架构方案掌握 MySQL 主主复制的双向同步原理与部署流程熟练配置 Keepalived 实现虚拟 IPVIP漂移与故障自动切换精通 HAProxy 负载均衡的健康检查、流量分发与读写分离配置完成从环境搭建到故障演练的全流程高可用架构落地前言在数字化业务全面渗透的今天数据已成为企业的核心生产要素数据库的持续可用性直接决定业务连续性。传统单节点 MySQL 架构存在严重的单点故障风险一旦宕机将导致业务中断、数据丢失等灾难性后果尤其在电商秒杀、金融交易等核心场景数据库的高可用High Availability, HA已成为系统设计的刚性需求。本章围绕MySQL 主主复制 Keepalived HAProxy这一企业级高可用方案展开从原理到实操系统讲解架构搭建、配置优化与故障演练帮助学习者构建零停机、零数据丢失的 MySQL 高可用集群为业务系统提供稳定可靠的数据支撑。一、案例概述与核心原理1.1 什么是 MySQL 高可用MySQL 高可用是指通过冗余设计确保数据库服务在单节点故障、网络中断或硬件损坏等异常场景下仍能持续对外提供服务同时保证数据一致性。其核心目标是实现 **“零停机、零数据丢失”** 的业务连续性。1.2 方案核心组件本方案由三大核心组件协同构成表格组件核心作用MySQL 主主复制两台 MySQL 实例互为主从双向同步数据支持双节点读写提供数据冗余与扩展能力Keepalived基于 VRRP 协议管理虚拟 IPVIP实时监控 MySQL/HAProxy 状态故障时自动将 VIP 漂移至存活节点确保服务入口不变HAProxy高性能反向代理与负载均衡器将流量分发至后端 MySQL 节点支持健康检查、读写分离可选与故障节点自动剔除1.3 架构优势高可用性Keepalived 实现秒级故障切换HAProxy 健康检查确保流量仅路由至正常节点彻底避免单点故障读写扩展主主架构支持双节点并发写入HAProxy 可配置读写分离利用备节点分担读压力提升写入性能灵活扩展可横向扩展 HAProxy 或 MySQL 节点动态调整负载均衡策略轮询、权重、最小连接数等开源生态基于成熟开源工具搭建无厂商锁定社区支持完善适配各类企业级数据库集群场景1.4 案例环境规划表格主机角色操作系统IP 地址部署服务Master1openEuler 24.03192.168.10.101MySQL 8.0主主节点 1Master2openEuler 24.03192.168.10.102MySQL 8.0主主节点 2Keepalived1openEuler 24.03192.168.10.103Keepalived HAProxyKeepalived2openEuler 24.03192.168.10.104Keepalived HAProxy虚拟 IPVIP-192.168.10.100业务统一访问入口二、案例实施步骤2.1 环境准备所有节点执行1基础环境配置bash运行# 关闭SELinux与防火墙 [rootlocalhost ~]# sed -i s/SELINUXenforcing/SELINUXdisabled/ /etc/selinux/config [rootlocalhost ~]# setenforce 0 [rootlocalhost ~]# systemctl stop firewalld systemctl disable firewalld # 安装基础依赖工具 [rootlocalhost ~]# dnf install -y gcc vim wget net-tools lrzsz2.2 部署 MySQL 主主复制Master1/Master2 节点1二进制安装 MySQL 8.0bash运行# 解压安装包 [rootmaster1 ~]# tar -xJf mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz [rootmaster1 ~]# mv mysql-8.0.36-linux-glibc2.28-x86_64 /usr/local/mysql # 创建数据目录与专用运行用户 [rootmaster1 ~]# mkdir -p /usr/local/mysql/data [rootmaster1 ~]# useradd -M -s /sbin/nologin mysql [rootmaster1 ~]# chown -R mysql:mysql /usr/local/mysql # 初始化数据库记录生成的临时root密码 [rootmaster1 ~]# /usr/local/mysql/bin/mysqld --initialize --usermysql --basedir/usr/local/mysql --datadir/usr/local/mysql/data2配置 MySQL 主主复制参数Master1 节点配置/etc/my.cnfini[mysqld] basedir/usr/local/mysql datadir/usr/local/mysql/data socket/usr/local/mysql/data/mysql.sock # 主主复制核心配置 server-id1 # 唯一服务器ID不可与其他节点重复 log-bin/usr/local/mysql/data/mysql-bin # 开启二进制日志 binlog_formatMIXED # 混合日志格式兼顾性能与一致性 relay-log/usr/local/mysql/data/relay-bin # 开启中继日志 auto_increment_offset1 # 自增ID起始值主主复制避免冲突 auto_increment_increment2 # 自增ID步长Master2 节点配置/etc/my.cnfini[mysqld] basedir/usr/local/mysql datadir/usr/local/mysql/data socket/usr/local/mysql/data/mysql.sock server-id2 # 唯一ID与Master1区分 log-bin/usr/local/mysql/data/mysql-bin binlog_formatMIXED relay-log/usr/local/mysql/data/relay-bin auto_increment_offset2 # 自增ID起始值与Master1错开 auto_increment_increment23配置 systemd 服务与启动bash运行# 配置环境变量 [rootmaster1 ~]# echo export PATH$PATH:/usr/local/mysql/bin /etc/profile [rootmaster1 ~]# source /etc/profile # 配置systemd服务 [rootmaster1 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld [rootmaster1 ~]# chmod x /etc/rc.d/init.d/mysqld [rootmaster1 ~]# vim /lib/systemd/system/mysqld.serviceini[Unit] DescriptionMySQL Server Afternetwork.target [Service] Typeforking ExecStart/etc/rc.d/init.d/mysqld start ExecReload/etc/rc.d/init.d/mysqld restart ExecStop/etc/rc.d/init.d/mysqld stop PrivateTmptrue [Install] WantedBymulti-user.targetbash运行# 启动服务并设置开机自启 [rootmaster1 ~]# systemctl daemon-reload [rootmaster1 ~]# systemctl start mysqld systemctl enable mysqld # 重置root密码 [rootmaster1 ~]# mysql -uroot -p # 输入初始化临时密码 mysql ALTER USER rootlocalhost IDENTIFIED BY Root123456; mysql FLUSH PRIVILEGES;4创建复制专用账号双节点执行sqlmysql CREATE USER repl% IDENTIFIED BY Repl123456; mysql GRANT REPLICATION SLAVE ON *.* TO repl%; mysql ALTER USER repl% IDENTIFIED WITH mysql_native_password BY Repl123456; mysql FLUSH PRIVILEGES;5配置主从同步双向配置Master1 查看主库状态sqlmysql SHOW MASTER STATUS\G记录File如mysql-bin.000001和Position如157。Master1 配置指向 Master2sqlmysql CHANGE MASTER TO MASTER_HOST192.168.10.102, MASTER_USERrepl, MASTER_PASSWORDRepl123456, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS157; mysql START SLAVE;Master2 配置指向 Master1同理使用 Master1 的日志文件与位置sqlmysql CHANGE MASTER TO MASTER_HOST192.168.10.101, MASTER_USERrepl, MASTER_PASSWORDRepl123456, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS157; mysql START SLAVE;6验证主主同步sqlmysql SHOW SLAVE STATUS\G确保Slave_IO_RunningYes、Slave_SQL_RunningYes双向同步正常。2.3 部署 HAProxy 负载均衡Keepalived1/Keepalived2 节点1安装 HAProxybash运行[roothaproxy1 ~]# dnf install -y haproxy2配置 HAProxy/etc/haproxy/haproxy.cfginiglobal log 127.0.0.1 local2 chroot /var/lib/haproxy pidfile /var/run/haproxy.pid user haproxy group haproxy daemon maxconn 4000 # 最大并发连接数 defaults mode tcp # TCP代理模式适配MySQL log global option dontlognull retries 3 timeout http-request 5s timeout queue 1m timeout connect 5s timeout client 1m timeout server 1m timeout keepalive 5s timeout check 5s maxconn 3000 # MySQL监听配置 listen mysql bind 0.0.0.0:3306 # 监听所有网卡的3306端口 balance leastconn # 最小连接数负载均衡算法 server mysql1 192.168.10.101:3306 check port 3306 maxconn 300 server mysql2 192.168.10.102:3306 check port 3306 maxconn 300check port 3306开启健康检查自动剔除故障节点。3启动 HAProxy 服务bash运行[roothaproxy1 ~]# haproxy -c -f /etc/haproxy/haproxy.cfg # 检查配置 [roothaproxy1 ~]# systemctl restart haproxy systemctl enable haproxy4验证 HAProxybash运行[roothaproxy1 ~]# mysql -utest -p123456 -h192.168.10.103 -P3306通过 HAProxy 代理端口正常登录 MySQL说明负载均衡配置生效。2.4 部署 Keepalived 虚拟 IP 漂移Keepalived1/Keepalived2 节点1安装 Keepalivedbash运行[rootkeepalived1 ~]# dnf install -y keepalived2配置 Keepalived/etc/keepalived/keepalived.confKeepalived1主节点配置iniglobal_defs { router_id r1 # 唯一路由ID } vrrp_script chk_haproxy { script /etc/keepalived/chk.sh # 健康检查脚本 interval 2 # 每2秒检查一次 } vrrp_instance VI_1 { state MASTER # 主节点状态 interface ens33 # 绑定网卡根据实际网卡修改 virtual_router_id 51 # 虚拟路由ID双节点必须一致 priority 100 # 优先级主节点高于备节点 advert_int 1 # 通告间隔1秒 nopreempt # 非抢占模式避免主节点恢复后频繁切换 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.10.100/32 # 虚拟IP } track_script { chk_haproxy # 关联健康检查脚本 } notify_backup /etc/init.d/haproxy restart notify_fault /etc/init.d/haproxy stop }Keepalived2备节点配置iniglobal_defs { router_id r2 } vrrp_script chk_haproxy { script /etc/keepalived/chk.sh interval 2 } vrrp_instance VI_1 { state BACKUP # 备节点状态 interface ens33 virtual_router_id 51 priority 99 # 优先级低于主节点 advert_int 1 nopreempt authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.10.100/32 } track_script { chk_haproxy } notify_backup /etc/init.d/haproxy restart notify_fault /etc/init.d/haproxy stop }3创建健康检查脚本/etc/keepalived/chk.shbash运行#!/bin/bash # 检查HAProxy是否正常运行 if [ $(ps -C haproxy --no-header | wc -l) -eq 0 ]; then systemctl stop keepalived fibash运行[rootkeepalived1 ~]# chmod x /etc/keepalived/chk.sh4启动 Keepalived 服务bash运行[rootkeepalived1 ~]# systemctl restart keepalived systemctl enable keepalived5验证 VIPbash运行[rootkeepalived1 ~]# ip a可看到虚拟 IP192.168.10.100绑定在 Keepalived1 节点业务通过该 VIP 访问 MySQL。三、故障转移与高可用验证3.1 模拟 Master1 节点故障关闭 Master1 的 MySQL 服务bash运行[rootmaster1 ~]# systemctl stop mysqldHAProxy 健康检查自动剔除故障节点流量仅路由至 Master2Keepalived1 检测到 HAProxy 异常自动停止 KeepalivedVIP 漂移至 Keepalived2通过 VIP 访问 MySQL服务正常无业务中断。3.2 模拟 Keepalived1 节点故障关闭 Keepalived1 服务器bash运行[rootkeepalived1 ~]# shutdown -h nowKeepalived2 检测到主节点失联自动接管 VIP业务通过 VIP 访问 MySQL服务持续可用实现秒级故障切换。总结本章完整落地了MySQL 主主复制 Keepalived HAProxy企业级高可用架构核心要点总结如下主主复制核心双向同步 自增 ID 错开避免双节点写入冲突确保数据一致性Keepalived 核心基于 VRRP 协议实现 VIP 漂移秒级故障切换保障服务入口不变HAProxy 核心智能负载均衡 健康检查自动剔除故障节点提升系统吞吐与容灾能力架构协同价值三者联动构建了高可用、高性能、可扩展的数据库服务架构兼顾故障自动恢复、流量优化与资源高效利用为业务提供稳定可靠的数据支撑。通过本章学习可掌握企业级 MySQL 高可用架构的完整搭建与运维技能从容应对各类生产环境故障场景。