MySQL MHA Switchover Failure One example

First look at the password that caused the problem, including two special characters [and ~.

$ Egrep-w ' User|password '/etc/masterha/app1.cnf

password=p[ai3m~5z

User=mha_mgr

The role of user mha_mgr, as described in the following document, shows its role in managing the database instance.

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

Look at the phenomenon of switching failures in both scenarios.

1. Manual online switch, according to the log can be seen, MHA the password of the special characters escaped, causing the user mha_mgr connection to the database failed.

Check the original master-slave replication status, found no change, that is, the switch failed, and did not affect.

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

...

Thu Jan 10:28:39 2018-[INFO] * Phase 2:rejecting updates Phase.

Thu Jan 10:28:39 2018-[INFO]

Thu Jan 10:28:39 2018-[INFO] executing master IP online change script to disable write in the current master:

Thu Jan 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: $VAR 1 = [

'--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 the 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 10:28:39 2018-[ERROR][/USR/LOCAL/SHARE/PERL5/MHA/MANAGERUTIL.PM, ln177] Got error:at/usr/local/bin/masterh A_master_switch Line 53.

2. Automatic failover (manual shutdown of the main library, analog failure), the log finally appeared "completed successfully" the word, seemingly no problem, but the detailed view of the log, found a similar error, see the Mark Yellow place.

Check the original replication status, found that the replication relationship switch is normal, but the write virtual IP is missing, that is not writable.

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

...

Fri Jan 10:33:55 2018-[INFO] Executing master IP Activate script:

Fri Jan 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 10:33:55 2018-[ERROR][/USR/LOCAL/SHARE/PERL5/MHA/MASTERFAILOVER.PM, ln1588] Failed to activate Master IP add Ress for 192.168.4.43 (192.168.4.43:3306) with return code 10:0

Fri Jan 10:33:55 2018-[WARNING] proceeding.

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

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

...

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

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

Workaround, a more normal password will be OK. As for fixing this minor flaw, check the code carefully.

MySQL MHA Switchover Failure One example