Mysql高可用

一、前言与核心认知数据是企业核心资产MySQL单节点架构存在单点故障风险会导致业务中断、数据丢失尤其在电商秒杀、金融交易等场景高可用HA是刚性需求。本章核心围绕「MySQL主主复制KeepalivedHAProxy」经典架构实现故障自动转移、负载均衡和读写分离兼顾理论与实操。二、案例前置知识点2.1 什么是MySQL高可用通过冗余设计确保数据库在单节点故障、网络中断、硬件损坏等异常下持续对外提供服务且保证数据一致性核心目标是“零停机、零数据丢失”保障业务连续性。2.2 方案组成核心三件套MySQL主主复制两台MySQL实例互为主从双向同步数据均支持读写提供冗余和扩展能力。Keepalived基于VRRP协议管理虚拟IPVIP监控MySQL状态故障时自动将VIP漂移至存活节点保证服务地址不变。HAProxy反向代理负载均衡器分发流量至MySQL节点支持健康检查、读写分离可选、故障节点自动剔除。2.3 方案优势高可用性Keepalived秒级故障切换HAProxy健康检查确保流量仅路由至正常节点规避单点故障。读写扩展主主架构支持双节点并发写入HAProxy可配置读写分离用备节点分担读压力。灵活扩展可横向扩展HAProxy或MySQL节点支持动态调整负载均衡策略轮询、权重等。运维友好基于开源工具无厂商锁定社区支持丰富适合自建数据库集群。三、案例环境3.1 环境配置表主机操作系统IP地址应用Master1openEuler 24.03192.168.10.101MySQL8Master2openEuler 24.03192.168.10.102MySQL8Keepalived1openEuler 24.03192.168.10.103Keepalived、HAProxyKeepalived2openEuler 24.03192.168.10.104Keepalived、HAProxy3.2 案例需求与实现思路需求通过架构实现MySQL故障自动切换不影响业务正常运行。实现思路在Master1、Master2上安装MySQL数据库配置MySQL互为主从主主复制在两台Keepalived主机上安装并配置HAProxy实现负载均衡在两台Keepalived主机上安装并配置Keepalived实现故障转移模拟Master节点故障测试切换效果。四、案例实施步骤核心实操4.1 安装MySQL数据库Master1、Master2均执行4.1.1 基础环境准备# 安装基础软件包 [rootlocalhost ~]# yum -y install gcc vim wget net-tools lrzsz # 安装MySQL依赖包 [rootlocalhost ~]# dnf install -y libaio numactl openssl ncurses-compat-libs # 创建MySQL运行用户 [rootlocalhost ~]# useradd -M -s /sbin/nologin mysql # 关闭SELinux和防火墙 [rootlocalhost ~]# sed -i s/SELINUXenforcing/SELINUXdisabled/ /etc/selinux/config [rootlocalhost ~]# setenforce 0 [rootlocalhost ~]# systemctl disable firewalld [rootlocalhost ~]# systemctl stop firewalld4.1.2 二进制安装MySQL 8.0.36# 解压安装包 [rootlocalhost ~]# tar xJf mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz # 移动至指定目录并改名 [rootlocalhost ~]# mv mysql-8.0.36-linux-glibc2.28-x86_64 /usr/local/mysql # 创建数据存储目录 [rootlocalhost ~]# mkdir /usr/local/mysql/data # 授权目录权限 [rootlocalhost ~]# chown -R mysql.mysql /usr/local/mysql/data # 初始化MySQL生成初始随机密码需保存 [rootlocalhost ~]# cd /usr/local/mysql/bin [rootlocalhost bin]# ./mysqld --initialize --usermysql --basedir/usr/local/mysql --datadir/usr/local/mysql/data注意初始化会生成临时密码如Xr6:Gg*u8?/8后续登录需使用。4.1.3 配置MySQL# 编辑配置文件/etc/my.cnf [rootlocalhost ~]# vim /etc/my.cnf # 配置内容如下 [mysqld] bind-address 0.0.0.0 port 3306 basedir/usr/local/mysql datadir/usr/local/mysql/data max_connections2048 character-set-serverutf8 default-storage-engineINNODB max_allowed_packet16M skip-name-resolve [client] socket/usr/local/mysql/data/mysql.sock ​ # 将MySQL可执行文件加入环境变量 [rootlocalhost ~]# echo export PATH$PATH:/usr/local/mysql/bin /etc/profile [rootlocalhost ~]# . /etc/profile # 生效环境变量4.1.4 配置systemctl启动MySQL# 复制启动脚本并授权 [rootlocalhost ~]# cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld [rootlocalhost ~]# chmod x /etc/rc.d/init.d/mysqld # 编辑mysqld.service服务文件 [rootlocalhost ~]# vim /lib/systemd/system/mysqld.service # 服务配置内容 [Unit] Descriptionmysqld Afternetwork.target [Service] Typeforking ExecStart/etc/rc.d/init.d/mysqld start ExecStop/etc/rc.d/init.d/mysqld stop ExecReload/etc/rc.d/init.d/mysqld restart PrivateTmptrue [Install] WantedBymulti-user.target ​ # 启动并设置开机自启 [rootlocalhost ~]# systemctl daemon-reload [rootlocalhost ~]# systemctl enable mysqld [rootlocalhost ~]# systemctl start mysqld # 验证启动状态查看3306端口 [rootlocalhost ~]# netstat -tunlp |grep 3306 ​ # 重置root密码 [rootlocalhost ~]# mysqladmin -u root -p password pwd123 # 输入初始化时的临时密码4.1.5 创建测试用户用于后续验证# 登录MySQL [rootlocalhost ~]# mysql -u root -p # 输入新密码pwd123 # 创建测试用户并授权允许192.168.10网段访问 mysql CREATE USER test192.168.10.% IDENTIFIED BY 123456; mysql GRANT ALL ON *.* TO test192.168.10.%; # 兼容旧版认证插件避免从库同步异常 mysql ALTER USER test192.168.10.% IDENTIFIED WITH mysql_native_password BY 123456; mysql FLUSH PRIVILEGES; # 刷新权限4.2 配置MySQL主主复制Master1、Master2互为主从4.2.1 修改MySQL配置文件Master1/etc/my.cnf新增/修改log-bin/usr/local/mysql/data/mysql-bin # 启用二进制日志 binlog_format MIXED # 二进制日志格式混合模式 server-id1 # 唯一标识符不可重复Master2/etc/my.cnf新增/修改log-bin/usr/local/mysql/data/mysql-bin binlog_format MIXED server-id2 # 唯一标识符与Master1不同4.2.2 重启MySQL并授权复制用户# 重启MySQL两台均执行 [rootlocalhost ~]# systemctl restart mysqld ​ # 登录MySQL创建复制用户并授权两台均执行 [rootlocalhost ~]# mysql -u root -p mysql CREATE USER myslave% IDENTIFIED BY 123456; mysql GRANT REPLICATION SLAVE ON *.* TO myslave%; # 兼容旧版认证插件 mysql ALTER USER myslave% IDENTIFIED WITH mysql_native_password BY 123456; mysql FLUSH PRIVILEGES; # 查看主库状态记录File和Position值后续配置从库需用到 mysql show master status;注意执行show master status后会显示日志文件名如mysql-bin.000001和偏移量如157需记录下来。4.2.3 配置双向同步Master1同步Master2Master2同步Master1Master1配置同步Master2mysql change master to master_host192.168.10.102, # Master2的IP master_usermyslave, # 复制用户 master_password123456, # 复制用户密码 master_log_filemysql-bin.000001, # Master2的日志文件名刚才记录的 master_log_pos157; # Master2的日志偏移量刚才记录的 # 启动同步线程 mysql start slave; # 验证同步状态确保两个Yes [rootlocalhost ~]# mysql -uroot -p -e show slave status\G | grep YesMaster2配置同步Master1mysql change master to master_host192.168.10.101,master_usermyslave, master_password123456, master_log_filemysql-bin.000001, master_log_pos157; mysql start slave; [rootlocalhost ~]# mysql -uroot -p -e show slave status\G | grep Yes关键两个节点的Slave_IO_Running和Slave_SQL_Running均为Yes说明主主复制配置成功。4.3 安装并配置HAProxyKeepalived1、Keepalived2均执行4.3.1 基础准备关闭SELinux和防火墙[rootlocalhost ~]# sed -i s/SELINUXenforcing/SELINUXdisabled/ /etc/selinux/config [rootlocalhost ~]# setenforce 0 [rootlocalhost ~]# systemctl disable firewalld [rootlocalhost ~]# systemctl stop firewalld4.3.2 安装HAProxy[rootlocalhost ~]# dnf install haproxy4.3.3 配置HAProxy负载均衡MySQL节点# 编辑配置文件 [rootlocalhost ~]# 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 mode tcp # TCP代理模式适配MySQL log global option tcplog option dontlognull retries 3 timeout http-request 5s timeout queue 1m timeout connect 5s timeout client 1m timeout server 1m timeout http-keep-alive 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 # Master1节点 server mysql2 192.168.10.102:3306 check port 3306 maxconn 300 # Master2节点配置说明check port 3306表示通过3306端口检测MySQL节点状态maxconn 300限制每个节点最大并发连接数。4.3.4 启动并测试HAProxy# 检测配置文件正确性 [rootlocalhost ~]# haproxy -c -f /etc/haproxy/haproxy.cfg # 启动HAProxy并设置开机自启 [rootlocalhost ~]# systemctl restart haproxy [rootlocalhost ~]# systemctl enable haproxy ​ # 测试通过HAProxy节点IP登录MySQL以Keepalived1为例 [rootlocalhost ~]# mysql -utest -p123456 -h192.168.10.103 -P3306登录成功即说明HAProxy配置生效可正常转发流量至MySQL节点。4.4 安装并配置KeepalivedKeepalived1、Keepalived2均执行4.4.1 安装Keepalived[rootlocalhost ~]# dnf install keepalived4.4.2 配置Keepalived实现VIP漂移两台Keepalived主机配置略有差异优先级不同均配置为BACKUP模式避免抢占冲突。Keepalived1配置优先级100开启nopreempt避免抢占Keepalived2配置优先级994.4.3启动Keepalived并验证VIP# 启动Keepalived并设置开机自启两台均执行 [rootlocalhost ~]# systemctl restart keepalived [rootlocalhost ~]# systemctl enable keepalived ​ # 查看VIP仅Keepalived1会先持有VIP用ip命令查看 [rootlocalhost ~]# ip a # 若看到ens33网卡有192.168.10.100说明VIP配置成功注意ifconfig命令不显示VIP需用ip a命令查看。4.4.4测试VIP访问MySQL[rootlocalhost ~]# mysql -utest -p123456 -h192.168.10.100 # 登录成功即说明VIP可正常转发流量4.5 故障转移测试核心验证测试1关闭Master1验证VIP访问# 关闭Master1192.168.10.101 [rootMaster1 ~]# systemctl stop mysqld ​ # 测试ping Master1确认已离线 [rootlocalhost ~]# ping 192.168.10.101 ​ # 用VIP访问MySQL应能正常登录流量自动转发至Master2 [rootlocalhost ~]# mysql -utest -p123456 -h192.168.10.100原理HAProxy检测到Master1故障自动将其从负载列表中剔除流量仅转发至Master2。测试2关闭Keepalived1验证VIP漂移# 关闭Keepalived1192.168.10.103 [rootKeepalived1 ~]# systemctl stop keepalived ​ # 查看Keepalived2的IP确认VIP漂移至192.168.10.104 [rootKeepalived2 ~]# ip a ​ # 用VIP访问MySQL仍能正常登录VIP已漂移至Keepalived2 [rootlocalhost ~]# mysql -utest -p123456 -h192.168.10.100原理Keepalived检测到Keepalived1故障VIP自动漂移至优先级次高的Keepalived2保障服务地址不变。五、总结「MySQL主主复制KeepalivedHAProxy」架构的核心协同逻辑主主复制实现两台MySQL节点数据双向同步提供数据冗余和读写扩展HAProxy负责流量分发和节点健康检查剔除故障MySQL节点实现负载均衡Keepalived通过VIP漂移实现HAProxy节点的故障自动切换确保业务访问地址不变。三者协同构建了一套开源、高效、低成本的MySQL高可用架构可实现秒级故障切换、零业务中断适配中小至中大型企业的核心业务需求。