MySQL MHA切換失敗一例

先看下引起問題的密碼啥樣, 包含兩個特殊字符[和~.

$ egrep -w 'user|password' /etc/masterha/app1.cnf

password=P[AI3M~5z

user=mha_mgr

用戶mha_mgr的作用, 如下文檔中的說明, 可見其對數據庫實例起到管理的作用.

MySQL administrative database username to the target MySQL server. This should be root because it runs all necessary administrative commands such as STOP SLAVE, CHANGE MASTER, RESET SLAVE.

看看兩種場景下切換失敗的現象.

1. 手動在線切換, 據日志可看到, MHA將密碼中的特殊字符轉義了, 致使用戶mha_mgr連接數據庫失敗.

檢查原有的主從復制狀態, 發現無變化, 即切換失敗, 并沒造成影響.

$ masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --orig_master_is_new_slave

...

Thu Jan 11 10:28:39 2018 - [info] * Phase 2: Rejecting updates Phase..

Thu Jan 11 10:28:39 2018 - [info]

Thu Jan 11 10:28:39 2018 - [info] Executing master ip online change script to disable write on the current master:

Thu Jan 11 10:28:39 2018 - [info]   /usr/local/bin/master_ip_online_change --command=stop --orig_master_host=192.168.4.42 --orig_master_ip=192.168.4.42 --orig_master_port=3306 --orig_master_user='mha_mgr' --orig_master_password='P\[AI3M\~5z' --new_master_host=192.168.4.43 --new_master_ip=192.168.4.43 --new_master_port=3306 --new_master_user='mha_mgr' --new_master_password='P\[AI3M\~5z' --orig_master_ssh_user=mysql --new_master_ssh_user=mysql   --orig_master_is_new_slave

ARGS: $VAR1 = [

          '--command=stop',

          '--orig_master_host=192.168.4.42',

          '--orig_master_ip=192.168.4.42',

          '--orig_master_port=3306',

          '--orig_master_user=mha_mgr',

          '--orig_master_password=P\\[AI3M\\~5z',

          '--new_master_host=192.168.4.43',

          '--new_master_ip=192.168.4.43',

          '--new_master_port=3306',

          '--new_master_user=mha_mgr',

          '--new_master_password=P\\[AI3M\\~5z',

          '--orig_master_ssh_user=mysql',

          '--new_master_ssh_user=mysql',

          '--orig_master_is_new_slave'

        ];

Unknown option: orig_master_ssh_user

Unknown option: new_master_ssh_user

Unknown option: orig_master_is_new_slave

Got Error: DBI connect(';host=192.168.4.43;port=3306;mysql_connect_timeout=4','mha_mgr',...) failed: Access denied for user 'mha_mgr'@'192.168.4.45' (using password: YES) at /usr/local/share/perl5/MHA/DBHelper.pm line 205.

 at /usr/local/bin/master_ip_online_change line 132.

Thu Jan 11 10:28:39 2018 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln177] Got ERROR:  at /usr/local/bin/masterha_master_switch line 53.

2. 自動故障切換(手動關閉主庫, 模擬故障), 日志最后出現了"completed successfully"的字樣, 貌似沒問題, 但詳細查看日志, 發現一處和上面類似的報錯, 見標黃處.

檢查原有的復制狀態, 發現復制關系切換正常, 但是寫虛擬IP不見了, 即不可寫了. 

$ tail -f /var/log/masterha/manager.log

...

Fri Jan 12 10:33:55 2018 - [info] Executing master IP activate script:

Fri Jan 12 10:33:55 2018 - [info]   /usr/local/bin/master_ip_failover --command=start --ssh_user=mysql --orig_master_host=192.168.4.42 --orig_master_ip=192.168.4.42 --orig_master_port=3306 --new_master_host=192.168.4.43 --new_master_ip=192.168.4.43 --new_master_port=3306 --new_master_user='mha_mgr' --new_master_password='P\[AI3M\~5z'

DBI connect(';host=192.168.4.43;port=3306;mysql_connect_timeout=4','mha_mgr',...) failed: Access denied for user 'mha_mgr'@'192.168.4.45' (using password: YES) at /usr/local/share/perl5/MHA/DBHelper.pm line 205.

 at /usr/local/bin/master_ip_failover line 81.

Fri Jan 12 10:33:55 2018 - [error][/usr/local/share/perl5/MHA/MasterFailover.pm, ln1588]  Failed to activate master IP address for 192.168.4.43(192.168.4.43:3306) with return code 10:0

Fri Jan 12 10:33:55 2018 - [warning] Proceeding.

Fri Jan 12 10:33:55 2018 - [info] Setting read_only=0 on 192.168.4.43(192.168.4.43:3306)..

Fri Jan 12 10:33:55 2018 - [info]  ok.

...

Master failover to 192.168.4.43(192.168.4.43:3306) completed successfully.

Fri Jan 12 10:33:57 2018 - [info] Sending mail..

解決方法, 換個比較正常的密碼就行了. 至于修復這個小缺陷, 還要仔細查下代碼.