MySQL MHA配置常见问题

wlyyb521

　　[root@centos7-04 ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf
　　问题一：
　　Thu Aug 23 04:39:01 2018 - [warning]  log-bin is not set on slave 192.168.0.189(192.168.0.189:3306). This host cannot be a master.
　　Thu Aug 23 04:39:01 2018 - [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
　　问题三：
　　Thu Aug 23 05:41:59 2018 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln226]  Failed to get master_ip_failover_script status with return code 1:0.
　　Thu Aug 23 05:41:59 2018 - [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。
　　问题四：
　　Thu Aug 23 18:17:14 2018 - [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.
　　Thu Aug 23 18:17:14 2018 - [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 Aug 24 23:50:37 2018 - [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=utf8mb4
　　更改默认字符集UTF8到utf8mb4
　　[client]
　　#default-character-set = utf8mb4
　　[mysql]
　　default-character-set = utf8mb4
　　[mysqld]
　　character-set-client-handshake = FALSE
　　character-set-server = utf8mb4
　　collation-server = utf8mb4_unicode_ci
　　init_connect='SET NAMES utf8mb4'