萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL MHA 高可用集群搭建实战:从环境准备到故障切换全流程复盘

架构拓扑

  • Monitor:192.168.30.210(管理节点)
  • DB1:192.168.30.211(初始 Master)
  • DB2:192.168.30.212(备选 Master,候选主)
  • DB3:192.168.30.213(只读 Slave)
  • DB4:192.168.30.214(只读 Slave)

版本:CentOS 6.7 x64,MySQL 5.5.45,MHA 0.52。

前置条件清单

  1. 四台 DB 均已安装 MySQL,server_id 唯一。
  2. 主从复制已跑通(GTID 或位点),Slave_IO/SQL_Running=Yes。
  3. 全节点互信 SSH(root 免密登录)。
  4. 防火墙放行 3306、22、MHA 管理端口。

SSH 互信批量脚本

# 在每台机器执行
ssh-keygen -t rsa -P '' -f ~/.ssh/id_rsa
for ip in 192.168.30.{210..214}; do
    ssh-copy-id $ip
done

MySQL 复制账号与 MHA 管理账号

-- 主库/备库均执行
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.30.%' IDENTIFIED BY '123';

-- 所有 DB 授权 MHA 管理用户
GRANT ALL ON *.* TO 'mha'@'192.168.30.%' IDENTIFIED BY '123456';

MHA 安装

# Monitor 节点装 manager + node
yum localinstall -y mha4mysql-node-0.52-0.noarch.rpm
yum localinstall -y mha4mysql-manager-0.52-0.noarch.rpm

# DB1-DB4 仅装 node
yum localinstall -y mha4mysql-node-0.52-0.noarch.rpm

核心配置文件 /etc/masterha_default.cnf

[server default]
user=mha
password=123456
manager_workdir=/mha
manager_log=/mha/manager.log
remote_workdir=/mha
ssh_user=root
repl_user=slave
repl_password=123
ping_interval=1

[server1]
hostname=db1
master_binlog_dir=/data/mysql
candidate_master=1

[server2]
hostname=db2
master_binlog_dir=/data/mysql
candidate_master=1

[server3]
hostname=db3
master_binlog_dir=/data/mysql
no_master=1

[server4]
hostname=db4
master_binlog_dir=/data/mysql
no_master=1

坑点复盘与解决

坑 1:SSH 互信未全通

masterha_check_ssh --conf=/etc/masterha_default.cnf
# 报错: Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
# 原因: 个别节点 ssh-copy-id 遗漏,补全即可。

坑 2:Perl 库路径不匹配

Can't locate MHA/BinlogManager.pm in @INC ...
# 原因: MHA Perl 模块安装在 /usr/lib/perl5,但运行环境扫描 /usr/lib64/perl5
# 修复: ln -s /usr/lib/perl5/vendor_perl/MHA /usr/lib64/perl5/vendor_perl/

坑 3:mysqlbinlog 命令不在 PATH

Can't exec "mysqlbinlog": No such file or directory
# 修复: ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog  (全节点执行)

坑 4:mysql 客户端命令不在 PATH

sh: mysql: command not found
# 修复: ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql  (全节点执行)

健康检查通过

masterha_check_repl --conf=/etc/masterha_default.cnf
# MySQL Replication Health is OK.

启动 Manager

mkdir -p /mha
masterha_manager --conf=/etc/masterha_default.cnf &
tail -f /mha/manager.log

故障切换实测

杀掉 DB2 (当前 Master) MySQL 进程,观察日志:

Sat Aug 27 11:15:40 2016 - [info] Master failover to db1(192.168.30.211:3306) completed successfully.

----- Failover Report -----
masterha_default: MySQL Master failover db2 to db1 succeeded
Master db2 is down!
Selected db1 as a new master.
db1: OK: Applying all logs succeeded.
db4: OK: Applying all logs succeeded. Slave started, replicating from db1.
db3: OK: Applying all logs succeeded. Slave started, replicating from db1.
db1: Resetting slave info succeeded.

验证新主从关系

# DB3/DB4
SHOW SLAVE STATUS\G
# Master_Host: 192.168.30.211 (DB1)
# Slave_IO/SQL_Running: Yes

# DB1 (新主)
SHOW SLAVE STATUS\G
# Slave_IO/SQL_Running: No (已解除从属)

生产化补全建议

  • 配置 master_ip_failover_script:VIP 漂移、DNS 切换、云厂商 LB 权重修改。
  • 配置 shutdown_script:彻底关闭旧主防止脑裂(IPMI/PDU 掉电、security group 封禁)。
  • 配置 secondary_check_script:多路径检测主库存活(如从应用服务器、备用网络 ping/连接)。
  • 监控告警:manager.log 关键字告警、Seconds_Behind_Master 阈值告警、MHA 进程存活监控。
  • 定期演练:月度故障注入演练,验证 RTO/RPO。
copyright © 萬盛學電腦網 all rights reserved