read_only should be set before CHANGE MASTER

When performing a controlled failover in a MySQL pair environment, if the 
CHANGE MASTER works, but the SLAVE SLAVE fails (a connection issue), the new 
slave still has read_only=FALSE.   

By simply fixing the SLAVE STATUS results in "Multi-master configuration is 
detected" and "MySQL Replication Health is NOT OK!" situation.

By simply setting read_only=TRUE first (which really seems a better data 
consistency approach), even with the failover error, a manual correction of 
replication will ensure a consistent replication topology.

masterha_master_switch --master_state=alive --conf=/etc/masterha_default.cnf 
--new_master_host=xx.xx.xx.xx --orig_master_is_new_slave

Resetting slave X and starting replication from the new master X..
Executed CHANGE MASTER.
[error][/usr/lib/perl5/vendor_perl/MHA/Server.pm, ln816] Slave could not be 
started on X Check slave status.
Switching master to X done, but switching slaves partially failed.

I had to fix the SLAVE connection, and also set global read_only=TRUE before 
mysql masterha_check_repl worked as expected.

Original issue reported on code.google.com by ronald.b...@gmail.com on 3 Jan 2013 at 6:23

SET GLOBAL read_only=1 on a new slave (orig master) can be executed from an external script "master_ip_online_change_script". (see http://code.google.com/p/mysql-master-ha/wiki/Parameters#master_ip_online_change _script for details) The script is invoked before CHANGE MASTER. When doing online master switch, restricting write access to the orig master is needed, and the way to do that depends on applications (i.e. moving virtual ip, updating catalog database, etc). MHA allows developers to customize in master_ip_online_change_script. MHA tarball has sample code of master_ip_online_change_script at samples/scripts/master_ip_online_change. In this sample script, SET GLOBAL read_only=1 is executed on the orig master ($orig_master_handler->enable_read_only();). Sample code is written in Perl, but is not language dependent.

tinygg / mysql-mha-setup

read_only should be set before CHANGE MASTER #51