MySQL 有關(guān)MHA搭建與切換的幾個(gè)錯(cuò)誤log匯總
更新時(shí)間：2020年12月22日 11:11:53 作者：東山絮柳仔
這篇文章主要介紹了MySQL 有關(guān)MHA搭建與切換的幾個(gè)錯(cuò)誤log匯總，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下
1：masterha_check_repl 副本集方面報(bào)錯(cuò) replicates is not defined in the configuration file!
具體信息如下：
# /usr/local/bin/masterha_check_repl --conf=/etc/mha/app1.cnf Thu Nov 21 15:33:15 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu Nov 21 15:33:15 2018 - [info] Reading application default configuration from /etc/mha/app1.cnf.. Thu Nov 21 15:33:15 2018 - [info] Reading server configuration from /etc/mha/app1.cnf.. Thu Nov 21 15:33:15 2018 - [info] MHA::MasterMonitor version 0.56. Thu Nov 21 15:33:16 2018- [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln671] Master 179.179.19.179:3306 from which slave 179.179.19.180(179.179.19.180:3306) replicates is not defined in the configuration file! Thu Nov 21 15:33:16 2018 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. at /usr/local/share/perl5/MHA/MasterMonitor.pm line 326. Thu Nov 21 15:33:16 2018 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers. Thu Nov 21 15:33:16 2018 - [info] Got exit code 1 (Not master dead). MySQL Replication Health is NOT OK!
分析：MHA 漂移過(guò)后，我們知道配置信息中 主節(jié)點(diǎn)的信息就不在了，我們需要及時(shí)維護(hù)，否則/usr/local/bin/masterha_check_repl --conf=/etc/mha/XXX.cnf 檢查副本集狀態(tài)報(bào)錯(cuò)。
2. masterha_master_switch 在線(xiàn)切換方面 報(bào)錯(cuò) We should not start online master switch when one of connections are running long updates on the current master
具體信息如下：
# /usr/local/bin/masterha_master_switch --master_state=alive --conf=/etc/mha/app1.cnf
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 179.179.19.184(179.179.19.184:3306)? (YES/no): y
Tue Nov 19 17:19:09 2018 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Tue Nov 19 17:19:09 2018 - [info] ok.
Tue Nov 19 17:19:09 2018 - [info] Checking MHA is not monitoring or doing failover..
Tue Nov 19 17:19:09 2018 - [info] Checking replication health on 179.179.19.185..
Tue Nov 19 17:19:09 2018 - [info] ok.
Tue Nov 19 17:19:09 2018 - [error][/usr/local/share/perl5/MHA/MasterRotate.pm, ln161] We should not start online master switch when one of connections are running long updates on the current master(179.179.19.184(179.179.19.184:3306)). Currently 1 update thread(s) are running.
Details:
{'Time' => '12815','db' => undef,'Id' => '1','User' => 'event_scheduler','State' => 'Waiting on empty queue','Command' => 'Daemon','Info' => undef,'Host' => 'localhost'}
Tue Nov 19 17:19:09 2018 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln177] Got ERROR: at /usr/local/bin/masterha_master_switch line 53.
分析：set global event_scheduler=off; 主從都要關(guān)閉
3. masterha_master_switch 在線(xiàn)切換方面 報(bào)錯(cuò) Got Error: DBI .....failed: Access denied for user
# /usr/local/bin/masterha_master_switch --master_state=alive --conf=/etc/mha/app1.cnf
Starting master switch from 179.179.19.185(179.179.19:3306) to 179.179.19.184(179.179.19.184:3306)? (yes/NO): yes
Tue Nov 19 18:52:04 2018 - [info] Checking whether 179.179.19.184(179.179.19.184:3306) is ok for the new master..
Tue Nov 19 18:52:04 2018 - [info] ok.
Tue Nov 19 18:52:04 2018 - [info] ** Phase 1: Configuration Check Phase completed.
Tue Nov 19 18:52:04 2018 - [info]
Tue Nov 19 18:52:04 2018 - [info] * Phase 2: Rejecting updates Phase..
Tue Nov 19 18:52:04 2018 - [info]
Tue Nov 19 18:52:04 2018 - [info] Executing master ip online change script to disable write on the current master:
Tue Nov 19 18:52:04 2018 - [info] /usr/local/bin/master_ip_online_change_appuanalysis --command=stop --orig_master_host=179.179.19.185 --orig_master_ip=179.179.19.185 --orig_master_port=3306--orig_master_user='weixinLX391P_xldbmha' --orig_master_password='weixinLX391P_xldbmha\)qlk' --new_master_host=179.179.19.184 --new_master_ip=179.179.19.184 --new_master_port=55988 --new_master_user='us_mha' --new_master_password='weixinLX391P_xldbmha\)qlk' --orig_master_ssh_user=root --new_master_ssh_user=root
Got Error: DBI connect(';host=179.179.19.184;port=3306;mysql_connect_timeout=4','weixinLX391P_xldbmha',...) failed: Access denied for user 'weixinLX391P_xldbmha'@'179.179.19.166' (using password: YES) at /usr/local/share/perl5/MHA/DBHelper.pm line 205.
at /usr/local/bin/master_ip_online_change_app1 line 119.
Tue Nov 19 18:52:04 2018 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln177] Got ERROR: at /usr/local/bin/masterha_master_switch line 53.
分析：賬號(hào)密碼有需要轉(zhuǎn)移字符的。app1.cnf 文件中user賬號(hào)相應(yīng)的密碼 password 不能有待轉(zhuǎn)移的字符，例如本例中的')'，但是 賬號(hào) repl_user 相應(yīng)的密碼repl_password 沒(méi)有此限制。
4.如果使用的是xtrabackup，注意從節(jié)點(diǎn)會(huì)把event還原上去，可能會(huì)造成數(shù)據(jù)不一致，同步失敗的問(wèn)題。
如果主節(jié)點(diǎn)有event，需要手動(dòng)關(guān)閉從節(jié)點(diǎn)的event。例如，主節(jié)點(diǎn)有歸檔刪除數(shù)據(jù)的event，從節(jié)點(diǎn)需要關(guān)閉，否則報(bào)錯(cuò)。類(lèi)似如下錯(cuò)誤：
Could not execute Delete_rows event on table ????DB.*****table; Can't find record in '*****', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos XXXXXXX
5 .GTID 模式轉(zhuǎn)換為傳統(tǒng)模式后，MHA 機(jī)制下數(shù)據(jù)庫(kù)主從檢查報(bào)錯(cuò)。
檢查的命令：
/usr/local/bin/masterha_check_repl --conf=/etc/mha/qqweixinordb.cnf
主要的報(bào)錯(cuò)信息
Can't exec "mysqlbinlog": No such file or directory 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. Fri Aug 28 04:38:22 2019 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln205] Slaves settings check failed! Fri Aug 28 04:38:22 2019 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln413] Slave configuration failed. Fri Aug 28 04:38:22 2019 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. at /usr/local/bin/masterha_check_repl line 48. Fri Aug 28 04:38:22 2019 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers. Fri Aug 28 04:38:22 2019 - [info] Got exit code 1 (Not master dead). MySQL Replication Health is NOT OK!
解決方案--在每個(gè)DB節(jié)點(diǎn)執(zhí)行以下命令
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql
再次檢查，報(bào)錯(cuò)信息消失，OK。
6.root 賬號(hào)密碼過(guò)期
以root賬號(hào)設(shè)置的ssh免密登陸，而ssh有過(guò)期限制，則mha ssh檢查時(shí)報(bào)錯(cuò):
/usr/local/bin/masterha_check_ssh --conf=/etc/mha/qqorder.cnf Thu Nov 5 10:09:09 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu Nov 5 10:09:09 2018 - [info] Reading application default configuration from /etc/mha/pms20epime.cnf.. Thu Nov 5 10:09:09 2018 - [info] Reading server configuration from /etc/mha/pms20epime.cnf.. Thu Nov 5 10:09:09 2018 - [info] Starting SSH connection tests.. Thu Nov 5 10:09:09 2018 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln63] Thu Nov 5 10:09:09 2018 - [debug] Connecting via SSH from root@172.181.191.191(172.181.191.191:22) to root@172.181.191.192(172.181.191.192:22).. WARNING: Your password has expired. Password change required but no TTY available. Thu Nov 5 10:09:09 2018 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln111] SSH connection from root@172.181.191.191(172.181.191.191:22) to root@172.181.191.192(172.181.191.192:22) failed! Thu Nov 5 10:09:10 2018 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln63] Thu Nov 5 10:09:09 2018 - [debug] Connecting via SSH from root@172.181.191.192(172.181.191.192:22) to root@172.181.191.191(172.181.191.191:22).. WARNING: Your password has expired. Password change required but no TTY available. Thu Nov 5 10:09:10 2018 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln111] SSH connection from root@172.181.191.192(172.181.191.192:22) to root@172.181.191.191(172.181.191.191:22) failed! SSH Configuration Check Failed! at /usr/local/bin/masterha_check_ssh line 44.
另外一種表現(xiàn)，就是第二次執(zhí)行賬號(hào)切換時(shí)報(bào)錯(cuò) sudo su -
解決方案在root賬號(hào)下，執(zhí)行以下命令:
chage -M 99999 root
以上就是MySQL 有關(guān)MHA搭建與切換的幾個(gè)錯(cuò)誤log匯總的詳細(xì)內(nèi)容，更多關(guān)于MySQL MHA搭建與切換的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
MySQL切分函數(shù)substring()的具體使用
這篇文章主要介紹了MySQL切分函數(shù)substring()的具體使用，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02
MySQL DISTINCT 的基本實(shí)現(xiàn)原理詳解
這篇文章主要介紹了MySQL DISTINCT 的基本實(shí)現(xiàn)原理詳解,文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2019-07-07
my.cnf是mysql啟動(dòng)時(shí)加載的配置文件,一般會(huì)放在mysql的安裝目錄中,用戶(hù)也可以放在其他目錄加載,下面這篇文章主要給大家介紹了關(guān)于MySQL之my.cnf配置文件的相關(guān)資料,需要的朋友可以參考下2022-09-09
這篇文章主要和大家分享一下數(shù)據(jù)庫(kù)的創(chuàng)建和銷(xiāo)毀語(yǔ)法以及詳細(xì)講解MySQL表的創(chuàng)建代碼和約束的使用，文中的示例代碼講解詳細(xì)，感興趣的可以了解一下2022-07-07
mysql中將null值轉(zhuǎn)換為0的語(yǔ)句
mysql中將null值轉(zhuǎn)換為0的語(yǔ)句，在mysql數(shù)據(jù)庫(kù)開(kāi)發(fā)中，如果后期添加了字段那么這些值為空值null，我們?cè)谑褂谜咝枰獙ull轉(zhuǎn)換為0方便后期的控制就需要下面的代碼了。2011-02-02
快速解決mysql57服務(wù)突然不見(jiàn)了的問(wèn)題
下面小編就為大家?guī)?lái)一篇快速解決mysql57服務(wù)突然不見(jiàn)了的問(wèn)題。小編覺(jué)得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2017-05-05
這篇文章主要介紹了MySQL中的insert ignore into使用方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-08-08
熱拷貝支持服務(wù)運(yùn)行中進(jìn)行備份，速度快，性能好，但它的劣勢(shì)在于只能備份MyIsam的表，無(wú)法備份InnoDB的表，對(duì)熱拷貝感興趣的朋友可以參考一下這篇文章2015-08-08
MySQL創(chuàng)建和刪除數(shù)據(jù)表的命令及語(yǔ)法詳解
這篇文章主要介紹了MySQL創(chuàng)建和刪除數(shù)據(jù)表的命令及語(yǔ)法,是MySQL入門(mén)學(xué)習(xí)中的基礎(chǔ)知識(shí),需要的朋友可以參考下2015-11-11
MySQL數(shù)據(jù)庫(kù)怎么正確查詢(xún)字符串長(zhǎng)度
MySQL中字符串長(zhǎng)度一般指數(shù)據(jù)庫(kù)表中一個(gè)字段或列中存儲(chǔ)的字符串的最大長(zhǎng)度,有時(shí)我們需要測(cè)量字符串長(zhǎng)度,來(lái)保證表結(jié)構(gòu)及數(shù)據(jù)庫(kù)性能穩(wěn)定,下面這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫(kù)怎么正確查詢(xún)字符串長(zhǎng)度的相關(guān)資料,需要的朋友可以參考下2023-06-06