基于 MySQL+MHA+Keepalived 搭建高可用主从集群实战

一、方案背景与技术选型1.1 为什么需要 MySQL 高可用在生产环境中数据库是业务系统的核心基石一旦 MySQL 服务出现宕机、主库故障等问题会直接导致业务中断、数据丢失给企业带来不可估量的损失。因此搭建一套高可用High Availability, HA的 MySQL 集群是保障业务连续性、实现故障自动切换、数据零丢失的核心需求。1.2 技术方案选型本次实战采用MySQL 主从复制 MHAMaster High Availability Keepalived的经典高可用架构各组件核心作用如下组件核心作用MySQL 主从复制实现主库数据实时同步到从库保障数据冗余为故障切换提供数据基础MHA开源 MySQL 高可用管理工具负责监控主库状态、自动故障检测与主从切换保障集群可用性Keepalived提供虚拟 IPVIP实现故障切换后业务无感知无需修改应用数据库连接地址1.3 环境规划本次实战采用 3 节点架构服务器规划如下节点角色主机名IP 地址核心服务主库Mastermysql-master192.168.1.100MySQL、MHA Node、Keepalived从库 1Slave1mysql-slave1192.168.1.101MySQL、MHA Node、Keepalived从库 2Slave2 / 管理节点mysql-slave2192.168.1.102MySQL、MHA Node、MHA Manager、Keepalived虚拟 IPVIP-192.168.1.200业务访问入口系统环境CentOS 7.xMySQL 5.7.xMHA 0.58Keepalived 1.3.5二、前期环境准备所有节点执行2.1 基础环境配置关闭防火墙与 SELinux# 关闭防火墙 systemctl stop firewalld systemctl disable firewalld # 临时关闭SELinux setenforce 0 # 永久关闭SELinux sed -i s/SELINUXenforcing/SELINUXdisabled/ /etc/selinux/config配置主机名与 hosts 解析# 各节点分别设置对应主机名 # Master节点 hostnamectl set-hostname mysql-master # Slave1节点 hostnamectl set-hostname mysql-slave1 # Slave2节点 hostnamectl set-hostname mysql-slave2 # 所有节点配置hosts cat /etc/hosts EOF 192.168.1.100 mysql-master 192.168.1.101 mysql-slave1 192.168.1.102 mysql-slave2 EOF配置时间同步# 安装chrony yum install -y chrony # 配置时间服务器以阿里云NTP为例 sed -i s/^server/#server/ /etc/chrony.conf cat /etc/chrony.conf EOF server ntp.aliyun.com iburst EOF # 启动服务 systemctl start chronyd systemctl enable chronyd # 验证时间同步 timedatectl配置 SSH 免密登录MHA 节点间通信必备# 所有节点生成密钥 ssh-keygen -t rsa -P -f ~/.ssh/id_rsa # 所有节点将公钥发送到其他节点包含自身 ssh-copy-id rootmysql-master ssh-copy-id rootmysql-slave1 ssh-copy-id rootmysql-slave2 # 验证免密登录 ssh mysql-slave2 date2.2 安装 MySQL 数据库所有节点执行卸载系统自带 MariaDByum remove -y mariadb*配置 MySQL YUM 源wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm rpm -ivh mysql57-community-release-el7-11.noarch.rpm安装 MySQL 5.7yum install -y mysql-community-server启动 MySQL 并初始化systemctl start mysqld systemctl enable mysqld # 获取初始密码 grep temporary password /var/log/mysqld.log # 登录MySQL修改初始密码 mysql -uroot -p ALTER USER rootlocalhost IDENTIFIED BY MyNewPass123;配置 MySQL 主从复制参数所有节点修改 my.cnfcat /etc/my.cnf EOF [mysqld] # 基础配置 server-id1 # 主库为1从库1为2从库2为3必须唯一 log-binmysql-bin binlog_formatROW # 主从复制优化 relay_logrelay-bin read_only1 # 从库开启只读主库无需配置 log_slave_updates1 # 字符集配置 character-set-serverutf8mb4 collation-serverutf8mb4_general_ci # 慢查询日志 slow_query_log1 long_query_time2 EOF注意主库需删除read_only1配置重启 MySQL 生效systemctl restart mysqld三、配置 MySQL 主从复制3.1 主库配置mysql-master创建主从复制账号-- 登录主库MySQL mysql -uroot -pMyNewPass123 -- 创建复制账号授权所有从库访问 CREATE USER repl% IDENTIFIED BY Repl123456; GRANT REPLICATION SLAVE ON *.* TO repl%; FLUSH PRIVILEGES; -- 查看主库binlog信息记录File和Position SHOW MASTER STATUS\G输出示例*************************** 1. row *************************** File: mysql-bin.000001 Position: 154 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set:3.2 从库配置mysql-slave1、mysql-slave2配置主从同步-- 登录从库MySQL mysql -uroot -pMyNewPass123 -- 配置主库信息替换为实际主库IP、binlog File和Position CHANGE MASTER TO MASTER_HOST192.168.1.100, MASTER_USERrepl, MASTER_PASSWORDRepl123456, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154; -- 启动从库复制 START SLAVE; -- 查看复制状态 SHOW SLAVE STATUS\G验证标准Slave_IO_Running: Yes、Slave_SQL_Running: Yes代表主从同步正常四、安装配置 MHA 高可用4.1 安装 MHA 依赖所有节点执行# 安装依赖包 yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes4.2 安装 MHA Node所有节点执行# 下载MHA Node安装包 wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.noarch.rpm # 安装 rpm -ivh mha4mysql-node-0.58-0.el7.noarch.rpm4.3 安装 MHA Manager仅管理节点 mysql-slave2 执行# 下载MHA Manager安装包 wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.noarch.rpm # 安装 rpm -ivh mha4mysql-manager-0.58-0.el7.noarch.rpm4.4 配置 MHA Manager管理节点 mysql-slave2创建 MHA 工作目录mkdir -p /etc/mha mkdir -p /var/log/mha chown -R mysql:mysql /etc/mha /var/log/mha创建 MHA 配置文件/etc/mha/mha.cnf[server default] # MySQL管理员账号 userroot passwordMyNewPass123 # 主从复制账号 repl_userrepl repl_passwordRepl123456 # 工作目录 manager_workdir/var/log/mha manager_log/var/log/mha/manager.log remote_workdir/var/log/mha # 远程SSH用户 ssh_userroot # 故障切换脚本后续配置Keepalived联动 # master_ip_failover_script/etc/mha/master_ip_failover # 从库提升为主库后执行脚本 # master_ip_online_change_script/etc/mha/master_ip_online_change # 监控间隔秒 ping_interval1 [server1] hostnamemysql-master candidate_master1 # 优先候选主库 port3306 [server2] hostnamemysql-slave1 candidate_master1 port3306 [server3] hostnamemysql-slave2 no_master1 # 不参与主库选举仅作为从库 port3306验证 MHA 配置# 验证SSH免密 masterha_check_ssh --conf/etc/mha/mha.cnf # 验证主从复制 masterha_check_repl --conf/etc/mha/mha.cnf验证成功标准输出All SSH connection tests passed successfully.和MySQL Replication Health is OK.启动 MHA Manager 服务# 后台启动 nohup masterha_manager --conf/etc/mha/mha.cnf /var/log/mha/manager.log # 查看MHA状态 masterha_check_status --conf/etc/mha/mha.cnf正常状态mysql-slave2 (pid: 12345) is running(0:PING_OK)五、安装配置 Keepalived 实现 VIP5.1 安装 Keepalived所有节点执行yum install -y keepalived systemctl enable keepalived5.2 配置 Keepalived主库 mysql-master创建配置文件/etc/keepalived/keepalived.confglobal_defs { router_id mysql-master } vrrp_instance VI_1 { state MASTER interface ens33 # 替换为实际网卡名称 virtual_router_id 51 priority 150 # 主库优先级最高 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.1.200/24 # 虚拟IP } }5.3 配置 Keepalived从库 mysql-slave1、mysql-slave2创建配置文件/etc/keepalived/keepalived.confglobal_defs { router_id mysql-slave1 # 从库2改为mysql-slave2 } vrrp_instance VI_1 { state BACKUP interface ens33 # 替换为实际网卡名称 virtual_router_id 51 priority 100 # 从库优先级低于主库 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.1.200/24 # 虚拟IP } }5.4 启动 Keepalived 服务# 所有节点启动服务 systemctl start keepalived # 查看VIP主库节点可看到VIP ip addr show ens335.5 配置 MHA 与 Keepalived 联动可选实现故障自动切换 VIP创建 VIP 切换脚本/etc/mha/master_ip_failover#!/usr/bin/env perl use strict; use warnings FATAL all; use Getopt::Long; my ( $command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port, $new_master_host, $new_master_ip, $new_master_port ); my $vip 192.168.1.200/24; my $key 0; my $ssh_start_vip /usr/sbin/ip addr add $vip dev ens33; my $ssh_stop_vip /usr/sbin/ip addr del $vip dev ens33; GetOptions( commands \$command, ssh_users \$ssh_user, orig_master_hosts \$orig_master_host, orig_master_ips \$orig_master_ip, orig_master_porti \$orig_master_port, new_master_hosts \$new_master_host, new_master_ips \$new_master_ip, new_master_porti \$new_master_port, ); exit main(); sub main { if ( $command eq stop || $command eq stopssh ) { my $exit_code 1; eval { print Disabling the VIP on old master: $orig_master_host \n; stop_vip(); $exit_code 0; }; if ($) { warn Got Error: $\n; exit $exit_code; } exit $exit_code; } elsif ( $command eq start ) { my $exit_code 1; eval { print Enabling the VIP - $vip on the new master - $new_master_host \n; start_vip(); $exit_code 0; }; if ($) { warn $; exit $exit_code; } exit $exit_code; } elsif ( $command eq status ) { print Checking the Status of the script.. OK \n; exit 0; } else { usage(); exit 1; } } sub start_vip() { ssh $ssh_user\$new_master_host \ $ssh_start_vip \; } sub stop_vip() { return 0 unless ($ssh_user); ssh $ssh_user\$orig_master_host \ $ssh_stop_vip \; } sub usage { print Usage: master_ip_failover --commandstart|stop|stopssh|status --ssh_useruser --orig_master_hosthost --orig_master_ipip --orig_master_portport --new_master_hosthost --new_master_ipip --new_master_portport\n; }赋予脚本执行权限chmod x /etc/mha/master_ip_failover chown mysql:mysql /etc/mha/master_ip_failover修改 MHA 配置文件启用切换脚本[server default] master_ip_failover_script/etc/mha/master_ip_failover重启 MHA Manager 服务masterha_stop --conf/etc/mha/mha.cnf nohup masterha_manager --conf/etc/mha/mha.cnf /var/log/mha/manager.log 六、高可用集群验证6.1 主从同步验证主库创建测试数据CREATE DATABASE test_db; USE test_db; CREATE TABLE test_table (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20)); INSERT INTO test_table (name) VALUES (test1), (test2);从库查询数据验证同步正常SELECT * FROM test_db.test_table;6.2 故障切换验证模拟主库宕机# 主库节点执行停止MySQL服务 systemctl stop mysqld查看 MHA 日志tail -f /var/log/mha/manager.log日志会显示检测到主库故障 - 选举新主库 - 执行主从切换 - 同步 VIP 到新主库验证 VIP 漂移# 原主库节点VIP消失 ip addr show ens33 # 新主库节点VIP成功漂移 ip addr show ens33验证业务访问通过 VIP 192.168.1.200 访问数据库验证数据正常、业务无感知七、运维注意事项与优化7.1 日常运维要点定期监控集群状态# 查看MHA状态 masterha_check_status --conf/etc/mha/mha.cnf # 查看主从复制状态 mysql -uroot -p -e SHOW SLAVE STATUS\G # 查看Keepalived状态 systemctl status keepalived主库故障恢复后处理原主库修复后需重新加入集群作为从库避免脑裂-- 原主库登录MySQL重置主从 RESET MASTER; CHANGE MASTER TO MASTER_HOST新主库IP, MASTER_USERrepl, MASTER_PASSWORDRepl123456, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS154; START SLAVE;定期备份数据配置定时任务每日全量备份 增量备份保障数据安全。7.2 性能优化建议主从复制优化开启半同步复制避免主从数据不一致调整innodb_flush_log_at_trx_commit1、sync_binlog1保障数据安全从库开启并行复制提升同步效率MHA 优化调整ping_interval参数缩短故障检测时间配置邮件告警故障时及时通知运维人员Keepalived 优化配置多网卡绑定避免单点故障调整优先级实现主库恢复后自动切回八、总结本文详细介绍了基于 MySQLMHAKeepalived 搭建高可用主从集群的完整流程从环境准备、主从配置、MHA 部署到 Keepalived 联动覆盖了生产环境中 MySQL 高可用的核心需求。该架构具备以下优势高可用性主库故障自动切换RTO30 秒业务无感知数据可靠性主从实时同步保障数据零丢失可扩展性支持横向扩展从库提升读性能运维便捷性MHA 自动化管理降低运维成本-