MHA常見報錯以及解決方法

error 1
運行masterha_check_repl --conf=/etc/masterha/app1.cnf報錯

Testing mysql connection and privileges..sh: mysql: command not found
mysql command failed with rc 127:0!
at /usr/bin/apply_diff_relay_logs line 375

解決方案：ln -s /usr/local/mysql/bin/mysql /usr/bin

error 2
運行masterha_check_repl --conf=/etc/masterha/app1.cnf報錯

Can't exec "mysqlbinlog": No such file or directory at /usr/local/perl5/MHA/BinlogManager.pm line 99.

解決方案：在node節點上執行 which mysqlbinlog，比如我的結果就是
[localhost~]$ which mysqlbinlog
/usr/local/mysql/bin/mysqlbinlog

ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog

error 3
運行master_check_ssh --conf=/etc/masterha/aap1.cnf報錯

connection via SSH fromroot@192.168.17.199toroot@192.168.17.200 ...
permission denied (publickey,gssapi-keyex,gssapi-with-mic,password)
[error] [/usr/local/share/perl5/MHA/SSHcheck.pm,ln163]

解決方案：一般是公鑰有問題，需要刪除 /root/.ssh/known_hosts里面的相關ip內容 重新生成一下就ok了

error 4
運行master_check_ssh --conf=/etc/masterha/aap1.cnf報錯

Sun Nov 20 20:10:59 2016 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysqllog/3306 --output_file=/masterha/app1/save_binary_logs_test --manager_version=0.55 --start_file=mysql-bin.000001
Sun Nov 20 20:10:59 2016 - [info] Connecting to root@172.18.3.180(172.18.3.180)..
Failed to save binary log: Binlog not found from /data/mysqllog/3306! If you got this error at MHA Manager, please set "master_binlog_dir=/path/to/binlog_directory_of_the_master" correctly in the MHA Manager's configuration file and try again.
at /usr/bin/save_binary_logs line 117.
eval {...} called at /usr/bin/save_binary_logs line 66
main::main() called at /usr/bin/save_binary_logs line 62
Sun Nov 20 20:10:59 2016 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln154] Master setting check failed!
Sun Nov 20 20:10:59 2016 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln367] Master configuration failed.
Sun Nov 20 20:10:59 2016 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln386] Error happend on checking configurations. at /usr/bin/masterha_check_repl line 48.
Sun Nov 20 20:10:59 2016 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln482] Error happened on monitoring servers.
Sun Nov 20 20:10:59 2016 - [info] Got exit code 1 (Not master dead).

解決方案：
/etc/masterha/aap1.cnf中的datadir路徑應該是mysql中bin-log的位置

error 5

運行masterha_check_repl --conf=/etc/masterha/app1.cnf報錯

Mon Nov 21 11:11:40 2016 - [info] MHA::MasterRotate version 0.55.
Mon Nov 21 11:11:40 2016 - [info] Starting online master switch..
Mon Nov 21 11:11:40 2016 - [info]
Mon Nov 21 11:11:40 2016 - [info] * Phase 1: Configuration Check Phase..
Mon Nov 21 11:11:40 2016 - [info]
Mon Nov 21 11:11:40 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Nov 21 11:11:40 2016 - [info] Reading application default configurations from /etc/masterha/app1.cnf..
Mon Nov 21 11:11:40 2016 - [info] Reading server configurations from /etc/masterha/app1.cnf..
Mon Nov 21 11:11:40 2016 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln604] There are 2 non-slave servers! MHA manages at most one non-slave server. Check configurations.
Mon Nov 21 11:11:40 2016 - [error][/usr/share/perl5/vendor_perl/MHA/ManagerUtil.pm, ln178] Got ERROR: at /usr/share/perl5/vendor_perl/MHA/MasterRotate.pm line 85.

解決方案：
手動將修復后的master做成新的master的從服務器

warning 1
各從庫應設置relay_log_purge=0
###否則收到以下告警信息 ##mysql -e 'set global relay_log_purge=0' 動態修改該參數，因為隨時slave會提升為master
[warning] relay_log_purge=0 is not set on slave (172.18.3.140:3306).

warning 2
各從庫設置read_only=1
###否則收到以下告警信息 ## mysql -e 'set global read_only=1' 動態修改該參數，因為隨時slave會提升為master。
[info] read_only=1 is not set on slave (172.18.3.107:3306

[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'