MySQL disaster recovery by promoting a replica

I was just talking to someone who backs up their MySQL servers once a day with mysqldump, and I said in a catastrophe, you’re going to have to reload from a backup; that’s some amount of downtime, plus up to a day of lost data.

And they said “We can just promote a replica, we’ve done it before. It works fine.”

Granted, in some/many cases, this is fine. There are all sorts of caveats – for example, you either know that your slave has the same data as the master or you don’t care. But it’s fine for some things.

So then I said “what about DROP TABLE?”

And there was a pause. I assume they were realizing that the chance of accidental or malicious destruction of data is much higher than the chance of multiple servers dying at once. This is why replica != backup.

How about you?

Granted, you can use a delayed replica to protect against this particular scenario. But you still need “real” backups, and you still have to think about the worst case – restoring that backup.

See Also

I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and lots of open-source software for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB. More about me.