Backups and disaster recovery

A developer runs a DELETE with no WHERE on the users table. The interviewer asks: "You have two followers. Are you safe?" A weak answer says yes, because the data is copied. A strong answer says no, and says how far back you can restore and how long it takes.

In the school of lesson 39, the clerk carries every change from the master register to the classroom copies. If the principal tears out a page, the clerk carries that too. A follower is made to be the same as the leader, so it copies a mistake as fast as a good change. It copies a DELETE within the replication lag (lesson 40).

A backup is a copy of the data kept apart. It does not follow the live database, so it stays as it was at one moment. Keep it in another place, so that one failure cannot take both.

A base backup is a full copy of the database files at one moment. To go forward, use the log of changes (lessons 21 and 39). Point-in-time recovery (PITR) has two parts:

1. A base backup.
2. Every change after it, kept in a store as the database makes it.

To restore, copy the base backup to a new server. Then replay the log up to a moment you choose, such as one second before the bad DELETE.

Two numbers describe a recovery plan:

• RPO (recovery point objective): how much data you can afford to lose, measured as time. An RPO of 1 minute means you may lose the last minute of changes.
• RTO (recovery time objective): how long the service may be down while you recover.

Disaster recovery is the plan for losing a whole site, such as a data centre. Keep the backups and the log in a second, faraway site.

SwadKart's orders database is 500 GB.

Assumptions (made up to teach with):

• A base backup is taken every night at 02:00 and stored in another place. The log is stored all day. At most 60 seconds of changes are not yet in the store.
• A link carries 125 MB per second (lesson 15). Replaying the log is 10 times faster than real time.
• The mistake is at 11:05. The lag is 0.4 seconds (lesson 40).

The predict box. The followers copy the delete in about 0.4 seconds. At 11:06 each holds 0 rows. They cannot give the rows back.

Restore time.

• Copy: 500 GB = 500,000 MB. 500,000 / 125 = 4,000 seconds = 67 minutes.
• Replay: 02:00 to 11:05 is 545 minutes of changes. 545 / 10 = 54.5, about 55 minutes.
• RTO of this plan: 67 + 55 = 122 minutes, about 2 hours.

RPO. With the log: about 60 seconds. With only the nightly backup: up to 24 hours.

To get under one hour, change both parts. A base backup every 6 hours means at most 360 minutes of changes: replay takes 360 / 10 = 36 minutes. Assumption: a link four times as fast (500 MB per second) copies in 500,000 / 500 = 1,000 seconds = 17 minutes. The RTO is then 17 + 36 = 53 minutes.

Restore into a new server, not over the live one. Then copy the lost rows back.

• A backup with the log solves mistakes that every follower copies, and the loss of a site. The RPO falls from 24 hours to 60 seconds.
• It costs storage, a second site and a slow restore (122 minutes).
• I would change it when the data can be made again, such as a cache: then I need no backup.

"I have followers, so I have backups." A follower copies the DELETE in 0.4 seconds. In the example each follower held 0 rows one minute after the delete.

"The backup exists, so we can recover." Only a tried restore proves it. The example restore takes about 2 hours.

1. Why can a follower not undo a DELETE? What two things does point-in-time recovery need? What do RPO and RTO mean?
2. A database is 200 GB. A link carries 125 MB per second. A base backup is taken at 03:00. A mistake happens at 15:00. Replay is 8 times faster than real time. How long does the restore take?
3. A team keeps only a nightly base backup at 02:00. A bad deploy damages data at 17:00. How much data can they lose, and what do you change?