Running with Peacemaker

[GoogleCodeExporter]

Hi.

I have a setup where I have one master database and multiple slaves.
Our application can talk to the master database for writes only and to the 
slaves for reads.
The slaves are behind a mysql proxy so the read only load is divided among 
them. 

What I need to do is to create a setup where whenever the master MySQL server 
goes down, it's virtual IP will be assigned to the new master (a former slave).
I was thinking about using a script and just let mysql-master-ha assign a new 
IP to the new master.
But this may fail miserably if the reason for the old master server to go down 
was e.g. someone pulling out the network cable. In that case I may suddenly 
have two servers with the same IP.
Therefore I need to use clustering software like peacemaker together with 
mysql-master-ha.

Is it possible to do that? 
The document on 
http://code.google.com/p/mysql-master-ha/wiki/Using_With_Clustering_Software 
describes how to do that with a simple two node scenario but that wouldn't work 
for me...

Can myster-ha maybe run a command to tell pacemaker to move over the virtual IP 
whenever it decides to switch the master database ?
That way the peacemaker would make sure there will never be two servers with 
the same virtual IP and at the same time mysql-master-ha would have control 
over promotion of slaves to master?

Original issue reported on code.google.com by m.je...@gmail.com on 20 Sep 2012 at 12:32

[GoogleCodeExporter]

And of course I meant Pacemaker ;)

Original comment by m.je...@gmail.com on 20 Sep 2012 at 12:36

[GoogleCodeExporter]

If you code virtual IP failover logic inside a custom script and call it from 
MHA by "master_ip_failover_script" parameter 
(http://code.google.com/p/mysql-master-ha/wiki/Parameters#master_ip_failover_scr
ipt), you can do virual IP failover without using clustering software. I hear 
some users/customers have written such logic. 

> The document on 
http://code.google.com/p/mysql-master-ha/wiki/Using_With_Clustering_Software 
describes how to do that with a simple two node scenario but that wouldn't work 
for me...

I think that works in your case as well. By managing master and slave1 via 
Pacemaker, when master goes down you can do failover to slave1. MHA has 
parameters so that it always try to failover to slave1 (See 
http://code.google.com/p/mysql-master-ha/wiki/Parameters#candidate_master and 
http://code.google.com/p/mysql-master-ha/wiki/Parameters#no_master). If both 
master and slave1 go down at the same time, failover won't work, but in such 
serious cases automated failover should not happen.

Original comment by Yoshinor...@gmail.com on 21 Sep 2012 at 1:42

[GoogleCodeExporter]

If I don't use clustering software I may end up with two servers having the 
same IP address. 
This can happen if someone pulls out the network cable from the master (by 
accident), MHA will promote a different master and assign it the same IP.
With clustering the old master will detect that there is a new server in the 
cluster and it will never reuse the virtual IP.

The thing is I have more than just one slave and I don't want to fail over to a 
specific slave but to a slave which has the latest relay log events and this 
can't be defined with priorities but has to be determined dynamically.
With the setup described in  
http://code.google.com/p/mysql-master-ha/wiki/Using_With_Clustering_Software is 
states specifically which server would be a preferred master and which will be 
ignored. 
I want this to work with masterha_manager which will decide which slave to fail 
over to... 

Original comment by m.je...@gmail.com on 24 Sep 2012 at 10:03

[GoogleCodeExporter]

> This can happen if someone pulls out the network cable from the master (by 
accident), MHA will promote a different master and assign it the same IP.

In such scenario, I highly recommend to force shutdown the original master, or 
doing nothing (do not start failover). Commands to force shutdown depend on 
H/W. Some samples are under samples/scripts/ from MHA Manager tarball. By 
shutting down the original master, you can avoid having two same IP address at 
least.

I understand your request to promote the latest slave, not a specific slave. In 
that case, implementing forcing shutdown logic and activating IP address logic 
in MHA failover script 
(http://code.google.com/p/mysql-master-ha/wiki/Parameters#master_ip_failover_scr
ipt) makes more sense to me. As described above, forcing shutdown logic highly 
depends on H/W. Activating IP address logic can be borrowed from clustering 
software. 

Original comment by Yoshinor...@gmail.com on 25 Sep 2012 at 10:37