I would like to understand if the following scenarios are possible in Heartbeat in Linux.
Setup: Two Database Servers running Mysql in Active/Passive mode in replication mode having Heartbeat setup for HA or failover mechanism. Application connects to DB using VIP that is started at the time of Heartbeat.
Failover VIP to passive site if primary MySQL instance is shut down. Bring down the heartbeat in primary if the role has been given to passive/secondary site in order to avoid split brain.
Setup lies on two different datacentres in a country. Master (Primary Site) and Slave (Secondary Site).
Below is my explanation to Ronaldo for the reason why I have gone for Master/Slave+Heartbeat
The customer had agreed for RPO and RTO of 10 mins. - With Master/Slave I have tested in my test grounds using highest TPS(Transactions Per Second) of about 250 TPS and more as a stress test using DMLs. Since we have a dedicated WAN to the datacentres for this application function, I have a bandwidth of 20 to 35 MBPS. And with the testings I see that it didn't cross more than 5 to 8 MBPS at max TPS of 260 in a range of 3 to 4 hrs. And the DMLs are applied in relay log immediately with Seconds Behind Master lesser than 5.
I didn't consider DRBD to be an option here. As when the DB size grows like 1.5 TB < at a situation in a regular peak hrs and if the Primary site is down. I observe the recovery process of innodb to build up and be available for applications takes 15 to 20 mins. Which goes beyond the bar with Customer SLA mentioned in RPO.
When you compare the case in Mysql Replication even though I have seconds behind master as 10 mins (which is a very rare possibility) . Still we come under the RPO and RTO of the Customer.
Concerns in Mysql + DRBD
- Also DRBD has some constraints to bring up if any logical volume gets corrupted the same situation occurs in secondary site.
- Even in DRBD how do you go for an automatic failover to secondary site?
- The maintanence of Production will again cause more laboratical work.
Eg: If I need to rotate a larger table in Production but not on Slave which can be treated as backup later. Incase of Master/Slave I can go for set session sql_bin_log=0; Which will not get executed in Slave. Whereas in DRBD, I need to mount slave and take backup of that huge table to tape and then put it back in DRBD mode.