实验 详解MHA搭建过程中的遇到的各种问题

一 、MHA搭建过程中问题集合

问题一：

Fri Apr 23 11:48:54 2021 - [warning]  log-bin is not set on slave 192.168.0.189(192.168.0.189:3306). This host cannot be a master.

Fri Apr 23 11:48:54 2021 - [warning]  log-bin is not set on slave 192.168.0.190(192.168.0.190:3306). This host cannot be a master.


解决办法：

从库的各客户端应开启binlog，即log_bin=on，两个从上都开启

[root@centos7-02 ~]# vi /etc/my.cnf

 basedir = /usr/local/mysql

 datadir = /data/mysql

 socket = /tmp/mysql.sock

 server-id=189

 log_bin=on

问题二：

Can't exec "mysqlbinlog": 没有那个文件或目录 at /usr/local/share/perl5/MHA/BinlogManager.pm line 106.

mysqlbinlog version command failed with rc 1:0, please verify PATH, LD_LIBRARY_PATH, and client options

 at /usr/local/bin/apply_diff_relay_logs line 493.

解决办法：

在所有节点上执行如下命令

[root@centos7-01 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog

[root@centos7-01 ~]# ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql

[root@centos7-02 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog

[root@centos7-02 ~]# ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql

[root@centos7-03 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog

[root@centos7-03 ~]# ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql

问题三：

Fri Apr 23 11:48:54 2021 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln226]  Failed to get master_ip_failover_script status with return code 1:0.

Fri Apr 23 11:48:54 2021 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations.  at /usr/local/bin/masterha_check_repl line 48

Thu Aug 23 05:41:59 2018 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.

解决办法：

[root@centos7-04 ~]# vi /etc/masterha/app1.cnf

#master_ip_failover_script= /usr/local/bin/master_ip_failover            //先暂时注释这条选项，后面引入keepalived后和修改该脚本以后再开启该选项



注释：Failover有两种方式：一种是虚拟IP地址，一种是全局配置文件。MHA并没有限定使用哪一种方式，而是让用户自己选择，虚拟IP地址的方式会牵扯到其它的软件,比如keepalive软件，而且还要修改脚本master_ip_failover。

问题四：

Fri Apr 23 11:48:54 2021 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. Can't exec "/usr/local/bin/master_ip_failover": 权限不够 at /usr/local/share/perl5/MHA/ManagerUtil.pm line 68.

Fri Apr 23 11:48:54 2021 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.

解决办法:

chmod +x /usr/local/bin/master_ip_failover

问题五：

[error][/usr/local/share/perl5/MHA/ServerManager.pm, ln492]  Server 192.168.0.190(192.168.0.190:3306) is dead, but must be alive! Check server settings.

解决办法：

[root@centos7-04 ~]# rm -rf /var/log/masterha/app1.log/app1.failover.complete        #删除MHA管理机上的这个文件

问题六：

[error][/usr/local/share/perl5/MHA/ServerManager.pm, ln492]  Server 192.168.0.190(192.168.0.190:3306) is dead, but must be alive! Check server settings.

解决办法：

清除防火墙规则

问题七：

[error][/usr/local/share/perl5/MHA/ServerManager.pm, ln492]  Server 192.168.0.188(192.168.0.188:3306) is dead, but must be alive! Check server settings.

Fri Apr 23 11:48:54 2021 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations.  at /usr/local/share/perl5/MHA/MasterMonitor.pm line 399

解决办法：

重启主库的mysql服务

问题八：

[error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln205] Slaves settings check failed!

解决办法：

登陆从数据库，查看主数据库是不是自己设定的

mysql> show slave status\G

Master_Server_Id: 189

问题九：

[error][/usr/local/share/perl5/MHA/ServerManager.pm, ln781] Multi-master configuration is detected, but two or more masters are either writable (read-only is not set) or dead! Check configurations for details. Master configurations are as below: 

Master 192.168.0.189(192.168.0.189:3306), dead

Master 192.168.0.188(192.168.0.188:3306), replicating from 192.168.0.189(192.168.0.189:3306)

解决办法：

所有从上执行

mysql -uroot -p -e "set global read_only=1"

问题十：

mysqlbinlog: unknown variable 'default-character-set=utf8'

mysqlbinlog version command failed with rc 7:0, please verify PATH, LD_LIBRARY_PATH, and client options

解决办法：

在所有从服务器上注释掉clinet段default-character-set=utf8

更改默认字符集UTF8到utf8

[client]

#default-character-set = utf8

[mysql]

default-character-set = utf8

[mysqld]

character-set-client-handshake = FALSE

character-set-server = utf8

collation-server = utf8mb4_unicode_ci

init_connect='SET NAMES utf8'

问题十一：

root@ubuntu:~# su - mysql -c "/usr/local/bin/masterha_check_repl --conf=/etc/mha/mysql/app.cnf"
Fri Apr 23 11:48:54 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Apr 23 11:48:54 2021 - [info] Reading application default configuration from /etc/mha/mysql/app.cnf..
Fri Apr 23 11:48:54 2021 - [info] Reading server configuration from /etc/mha/mysql/app.cnf..
Fri Apr 23 11:48:54 2021 - [info] MHA::MasterMonitor version 0.58.
Fri Apr 23 11:48:54 2021 - [error][/usr/local/share/perl/5.22.1/MHA/Server.pm, ln180] Got MySQL error when connecting 192.168.1.2(192.168.1.2:3306) :1045:Access denied for user 'mha'@'192.168.1.3' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
Fri Apr 23 11:48:54 2021 - [error][/usr/local/share/perl/5.22.1/MHA/Server.pm, ln180] Got MySQL error when connecting 192.168.1.1(192.168.1.1:3306) :1045:Access denied for user 'mha'@'192.168.1.3' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
Fri Apr 23 11:48:54 2021 - [error][/usr/local/share/perl/5.22.1/MHA/ServerManager.pm, ln301]  at /usr/local/share/perl/5.22.1/MHA/ServerManager.pm line 297.
Fri Apr 23 11:48:54 2021 - [error][/usr/local/share/perl/5.22.1/MHA/ServerManager.pm, ln301]  at /usr/local/share/perl/5.22.1/MHA/ServerManager.pm line 297.
Fri Apr 23 11:48:54 2021 - [error][/usr/local/share/perl/5.22.1/MHA/ServerManager.pm, ln309] Got fatal error, stopping operations
Fri Apr 23 11:48:54 2021 - [error][/usr/local/share/perl/5.22.1/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/local/share/perl/5.22.1/MHA/MasterMonitor.pm line 329.
Fri Apr 23 11:48:54 2021 - [error][/usr/local/share/perl/5.22.1/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Fri Apr 23 11:48:54 2021 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!


解决办法：
mysql> grant all privileges on *.* to 'mha'@'Mysql1' identified by 'manager';
mysql> grant all privileges on *.* to 'mha'@'Mysql2' identified by 'manager';
mysql> grant all privileges on *.* to 'mha'@'Mysql3' identified by 'manager';
mysql> flush privileges;

问题十二：

[root@mysql3 ~]# /usr/local/mysql/scripts/mysql_install_db \
> --basedir=/usr/local/mysql \
> --datadir=/usr/local/mysql/data \
> --user=mysql
bash: /usr/local/mysql/scripts/mysql_install_db: /usr/bin/perl: bad interpreter: No such file or directory


解决办法：
yum -y install autoconf

问题十三：

Fri Apr 23 11:48:54 2021 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln671] Master 192.168.221.20:3306 from which slave 192.168.221.110(192.168.221.110:3306) replicates is not defined in the configuration file!
Fri Apr 23 11:48:54 2021 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/local/share/perl5/MHA/MasterMonitor.pm line 329.
Fri Apr 23 11:48:54 2021 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Fri Apr 23 11:48:54 2021 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

是因为MHA配置文件里面缺少了主库的配置
加入
[server1]
hostname=192.168.142.11
port=3306