MySQL MHA配置的常見(jiàn)問(wèn)題及解決方法

這篇文章主要講解了“ MHA配置的常見(jiàn)問(wèn)題及解決方法”，文中的講解內容簡(jiǎn)單清晰，易于學(xué)習與理解，下面請大家跟著(zhù)小編的思路慢慢深入，一起來(lái)研究和學(xué)習“MySQL MHA配置的常見(jiàn)問(wèn)題及解決方法”吧！

問(wèn)題一：

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.

解決辦法：

從庫的各客戶(hù)端應開(kāi)啟binlog，即log_bin=on，兩個(gè)從上都開(kāi)啟

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

 basedir = /usr/local/mysql

 datadir = /data/mysql

 socket = /tmp/mysql.sock

 server-id=189

 log_bin=on

問(wèn)題二：

Can't exec "mysqlbinlog": 沒(méi)有那個(gè)文件或目錄 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.

解決辦法：

在所有節點(diǎn)上執行如下命令

[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

問(wèn)題三：

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            //先暫時(shí)注釋這條選項，后面引入keepalived后和修改該腳本以后再開(kāi)啟該選項

注釋?zhuān)篎ailover有兩種方式：一種是虛擬IP地址，一種是全局配置文件。MHA并沒(méi)有限定使用哪一種方式，而是讓用戶(hù)自己選擇，虛擬IP地址的方式會(huì )牽扯到其它的軟件,比如keepalive軟件，而且還要修改腳本master_ip_failover。

問(wèn)題四：

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

問(wèn)題五：

[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管理機上的這個(gè)文件

問(wèn)題六：

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

解決辦法：

清除防火墻規則

問(wèn)題七：

[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服務(wù)

問(wèn)題八：

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

解決辦法：

登陸從數據庫，查看主數據庫是不是自己設定的

mysql> show slave status\G

Master_Server_Id: 189

問(wèn)題九：

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

問(wèn)題十：

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'