Defenseload

MySQL MHA Support for multi master configuration

MySQL MHA Support for multi master configuration

 After publishing MySQL MHA in the end of July, I received a few requests for supporting multi-master configuration. So I spent time for extending MHA for supporting multi-master, and now its published at a separated GitHub branch (a new development tree). Ill take some time for people to evaluate the new feature, and after verifying its stable enough, Ill merge it to the main branch and will release as a new version (tarball release).

The below is procedures to install MHA Manager multi-master tree.

$ git clone git://github.com/yoshinorim/MySQL-MasterHA-Manager.git 
$ cd MySQL-MasterHA-Manager 
$ git checkout -b multimaster origin/multimaster 
$ perl Makefile.PL 
$ make 
$ sudo make install

If you have not installed Module::Install Perl module and have difficulties to install it, you can simply copy the module from MHA Manager tarball package like below.

$ cd MySQL-MasterHA-Manager(from github) 
$ cp -rp /path/to/MHA-Manager-Tarball/inc ./ 
$ perl Makefile.PL 
...

Here are some notes to make MHA work with multi-master.



* Only one primary master (writable) is allowed. MySQL global variable "read-only=1" must be set on other MySQL masters.

* By default, all managed servers should be in two-tier replication channel. If you want to manage three or more tier replication channel, "multi-tier-slave=1" parameter must be set in the MHA configuration file.




Example replication settings are as follows.




1) Simple two-tier multi-master configuration

 M1(host1,RW) <----> M2(host2,read-only) 
 | 
 +-----+--------+ 
S1(host3,R) S2(host4,R) 
 
=> After failover 
 
 M2(host2,RW) 
 | 
 +-----+--------+ 
S1(host3,R) S2(host4,R) 
 
Configuration example: 
 
[server1] 
hostname=host1 
candidate_master=1 
 
[server2] 
hostname=host2 
candidate_master=1 
 
[server3] 
hostname=host3 
 
[server4] 
hostname=host4

On host2, SET GLOBAL read_only=1 must be set.







2) Three tier multi-master configuration

 M1(host1,RW) <-----------------> M2(host2,read-only) 
 | | 
 +-----+--------+ + 
S1(host3,R) S2(host4,R) S3(host5,R) 
 
 
=> After failover 
 
 M2(host2,RW) 
 | 
 +--------------+--------------------------+ 
S1(host3,R) S2(host4,R) S3(host5,R) 
 
 
Configuration example: 
[server default] 
multi_tier_slave=1 
 
[server1] 
hostname=host1 
candidate_master=1 
 
[server2] 
hostname=host2 
candidate_master=1 
 
[server3] 
hostname=host3 
 
[server4] 
hostname=host4 
 
[server5] 
hostname=host5

In this case, host5 is a third-tier slave, so MHA does not manage host5(MHA does not execute CHANGE MASTER on host5 when the primary master host1 fails). When current master host1 is down, host2 will be new master, so host5 can keep replication from host2 without doing anything.



If you want to try MHA, tutorials will be good start.

Available link for download