Heartbeat Options with MySQL and Possibilities

Question

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).

To Ronaldo

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.

Answer 1

The make-or-break element you should be concerned with is Replication Lag. Why ?

If you run SHOW SLAVE STATUS\G and Seconds_Behind_Master on the Slave is significantly high, failing over to such a Slave could be disastrous. Think of it:

If a Slave is 60 seconds behind, there is a 60 second window where

• primary keys you need do not exist
• changed column values do not yet exist
• rows that should exist are not present
• rows that should be deleted are still present

just to name a few possibilities.

SUGGESTION

Right after you've checked that the Master is fully down, you then need to check the Slave by running SHOW SLAVE STATUS\G to look for the following:

• if Slave_IO_Running is not Yes
• if Slave_SQL_Running is Yes
• if Relay_Log_Space stops changing (This indicates that there are no more binlog entries coming over from the Master)
• if Exec_Master_Log_Pos stops changing (This indicates that all relay log entries have been processed)

Once these four criteria have been met, the DB VIP can be assumed by the Slave.

CAVEAT

You may need to look into using MySQL with DRBD

Please read some of my earlier MySQL/DRBD posts:

• Dec 26, 2012 : MySQL Replication and High Availability
• Oct 17, 2012 : Setting up DRBD on an active MySQL server
• Mar 29, 2011 : MySQL high availability, failover and replication with Latency

I suggest DRBD because the Slave is Replicating at the Disk Level synchronously.

A failover would just need DRBD mounting and InnoDB Crash Recovery on MySQL Startup.