MySQL Replication

Hi Phil,

We RSYNC a share that contains JSS Backups from our master JSS to our DR site nightly.

In the event of needing the DR, wend restore & amend DNS.

Simple enough for us.

our JSS is in a VMware cluster, so we have the VMware admins make a backup of the VM every night. The JSS dumps the sql db to the VM's disk every night too, so that's really all we need- for a disaster we'd switch to the DR environment's VM, switch the alias in DNS to point to that JSS, and then restore the previous day's SQL dump if any of the live tables come up with issues.

Today in Boston, there was an unexpected and extended power outage in a really busy commercial and residential area (twice in around a year now) that made me think about my master - slave mysql setup. I was going to promote my slave to be the master, and when I thought it through and all the things I could and could not personally control during the failover (such as changing DNS names) it seemed like it wasn't worth me risking an out of sync slave to be promoted to master, and then doing the whole setting up a new slave where the master perviously was with my.cnf changes, setting up new replication access, and other dozen things that could go painfully wrong in this manual process. It also seems safer, if albeit more time consuming, to use a known good mysql backup and restoring it rather than using the master-slave promotion and the errors that brings into play.

Does anyone have a tried and true method to promote a mysql slave to master and demote master to slave? And in the end, how do you know that your data integrity is good? The more I think about it, the less I'm convinced that in a HA needs environment, that the master-slave option is the 1st layer of defense. In a complete disaster, it's maybe the only option and nice to have, but when both servers are still up, would it be better just to plan some downtime to make the more reliable data center location the master the old fashioned way?

Thanks for all your input.

We already had backups of the databases being dumped out twice a day and then backed up to tape every night. To take it a step further I setup a master-slave mysql replica (which is in another datacenter) just in case we run up against an extended outage in our primary datacenter and need to get up and running. I understand the caveats of having something slightly out of sync, but being able to shorten the RTO by having a hot database server is important to me.

My next step is to create a PowerShell script that goes in and changes the Database.xml file to point to the slave database server in the event of a failure.

What would be nice is if JAMF added the ability to define 2 database servers per context. A primary and a secondary and if the primary doesn't answer after a pre-determined amount of time then automatically start using the secondary. I've seen this done in other web apps.

We had JAMF come in to set up MySQL replication. Lots of steps, but the leave behind doucument was very well written. Good idea to have both boxes prepared ahead of time before the JAMF engineer shows up, to avoid losing several hours of time running installers. ;) Once it's set up, the leave behind document covers failing over and failing back. If you need this set up, it's worth every penny to have JAMF expanded services do it.

Imagine doing this to four environments...worthwhile exercise for failover.

@donmontalvo Do you have this leave behind doc?
Asking because we have replication setup in our environment and apparently it is not working at the moment. I was unaware as I took up the Casper duties and had no prior experience with this setup. Thanks in advance.....

Was looking for this too?

+3 on the doc, if possible :)

Sorry for the very late response. At the time we didn't have a MySQL DBA, so Jamf was engaged. @justinsako was the Jamf engineer who did the work. Unfortunately that kind of documentation is not something that can be shared, since it is owned by the company that paid for the engagement.

Since then I've moved on to a much larger company, and we have a dedicated MySQL DBA @jorgeislaspineda who handles replication, as well as MySQL High Availability (failover).

@jorgeislaspineda's work has me awestruck, since I only ever dealt with High Availability back when we were following Apple's white paper for setting up HA using Xserve severs running 10.4, and using QLogic fiber switches.

Never thought something like MySQL High Availability was possible. It is, and most certainly worth exploring.