gtid集群搭建好后，检测通过不了，哪位大师帮我分析一下原因

Question

老师，问题我201数据库是可以访问的呀，下面是201的状态，为什么通过脚本检查显示不可用？
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.200
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000009
Read_Master_Log_Pos: 194
Relay_Log_File: relay-bin.000003
Relay_Log_Pos: 409
Relay_Master_Log_File: master-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0
200主库配置如下
[mysqld]
server_id=200
log_bin=/var/lib/mysql/master-bin
gtid_mode=on
enforce-gtid-consistency=on
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
relay_log=/var/lib/mysql/relay-bin
master_info_repository=table
relay_log_info_repository=table
201从库配置如下
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server_id=201
read_only=on
gtid_mode=on
enforce-gtid-consistency=on
log_bin=master-bin2
skip-slave-start = 1
relay_log=/var/lib/mysql/relay-bin
202 my.cnf配置如下
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server_id=202
gtid_mode=on
enforce_gtid_consistency=on
relay_log=/var/lib/mysql/relay-bin
master_info_repository=table
log_bin=master-bin3
read_only=on
master_ip_failover 文件如下

my $vip = ‘192.168.0.220/24’;
my $key = ‘1’;
my sshstartvip="sudo/sbin/ifconfigenp0s3:ssh_start_vip = "sudo /sbin/ifconfig enp0s3:sshs​tartv​ip="sudo/sbin/ifconfigenp0s3:key $vip";
my sshstopvip="sudo/sbin/ifconfigenp0s3:ssh_stop_vip = "sudo /sbin/ifconfig enp0s3:sshs​topv​ip="sudo/sbin/ifconfigenp0s3:key down";
my $ssh_Bcast_arp= “sudo /sbin/arping -I bond0 -c 3 -A 192.168.0.220”;
检测信息
[root@rep3 bin]# masterha_check_repl --conf=/etc/mha/mha_mysql.cnf
Tue Jan 28 23:52:04 2020 - [info] MHA::MasterMonitor version 0.57.
Tue Jan 28 23:52:05 2020 - [info] GTID failover mode = 1
Tue Jan 28 23:52:05 2020 - [info] Dead Servers:
Tue Jan 28 23:52:05 2020 - [info] 192.168.0.201(192.168.0.201:3306)
Tue Jan 28 23:52:05 2020 - [info] Alive Servers:
Tue Jan 28 23:52:05 2020 - [info] 192.168.0.200(192.168.0.200:3306)
Tue Jan 28 23:52:05 2020 - [info] 192.168.0.202(192.168.0.202:3306)
Tue Jan 28 23:52:05 2020 - [info] Alive Slaves:
Tue Jan 28 23:52:05 2020 - [info] 192.168.0.202(192.168.0.202:3306) Version=5.7.29-log (oldest major version between slaves) log-bin:enabled
Tue Jan 28 23:52:05 2020 - [info] GTID ON
Tue Jan 28 23:52:05 2020 - [info] Replicating from 192.168.0.200(192.168.0.200:3306)
Tue Jan 28 23:52:05 2020 - [info] Not candidate for the new Master (no_master is set)
Tue Jan 28 23:52:05 2020 - [info] Current Alive Master: 192.168.0.200(192.168.0.200:3306)
Tue Jan 28 23:52:05 2020 - [info] Checking slave configurations…
Tue Jan 28 23:52:05 2020 - [info] Checking replication filtering settings…
Tue Jan 28 23:52:05 2020 - [info] binlog_do_db= , binlog_ignore_db=
Tue Jan 28 23:52:05 2020 - [info] Replication filtering check ok.
Tue Jan 28 23:52:05 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln364] None of slaves can be master. Check failover configuration file or log-bin settings in my.cnf
Tue Jan 28 23:52:05 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/bin/masterha_check_repl line 48.
Tue Jan 28 23:52:05 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Tue Jan 28 23:52:05 2020 - [info] Got exit code 1 (Not master dead).

Answer 1

sqlercn 2020-01-29 16:06:46

你在MHA的监控服务器上用mha用户访问一下201看是否可以访问？

• 提问者 宝慕林2132329 #1

  监控服务器202可以访问主库200，但不能访问201。如果不方便沟通，能否加一下您的微信，我微信15308408320
  用户信息如下
  mysql> select host,user from mysql.user; 
  +-------------+---------------+
  | host        | user          |
  +-------------+---------------+
  | 192.168.0.% | maxscale      |
  | 192.168.0.% | mha           |
  | 192.168.0.% | rep           |
  | 192.168.0.% | scalemon      |
  | localhost   | mysql.session |
  | localhost   | mysql.sys     |
  | localhost   | root          |
  [root@rep3 bin]# mysql -umha -p -h192.168.0.200
  Enter password: 
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 12946
  Server version: 5.7.29-log MySQL Community Server (GPL)
  Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
  [root@rep3 bin]# mysql -umha -p -h192.168.0.201
  Enter password: 
  ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.201' (113)

• sqlercn 回复 提问者 宝慕林2132329 #2

  先要解决监控服务器不能访问201的问题。你可以先检查一下201上是否开启了防火墙，从监控服务器上telnet 一下201的3306端口检查端口是否可以访问。你可以通过课程的Q群和我联系。

• 提问者 宝慕林2132329 #3

  是防火墙的原因，但把防火墙关闭后出现另外的错误。能不帮我分析一下原因或给一个切换的脚本文件给我参考
  Wed Jan 29 14:50:14 2020 - [info]  ok.
  Wed Jan 29 14:50:14 2020 - [info] Checking master_ip_failover_script status:
  Wed Jan 29 14:50:14 2020 - [info]   /usr/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.0.200 --orig_master_ip=192.168.0.200 --orig_master_port=3306 
  : No such file or directory
  Wed Jan 29 14:50:14 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln229]  Failed to get master_ip_failover_script status with return code 127:0.
  Wed Jan 29 14:50:14 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/bin/masterha_check_repl line 48.
  Wed Jan 29 14:50:14 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
  Wed Jan 29 14:50:14 2020 - [info] Got exit code 1 (Not master dead).
  MySQL Replication Health is NOT OK!

点开查看后面1条评论