Why your backup strategy is probably wrong?

"If you have a hammer, all you see is nails." also known as the Law of the Instrument describes the cognitive bias that involves over-reliance on a familiar tool. To protect MySQL data, a DBA must have a toolbox full of the proper utensils. To know only one approach is not enough.

In this talk, we will cover the following topics:
- Why your backup strategy is probably wrong?
- The Schröendinger Backup
- Backups: Cold, hot... and warm?
- The right answer is not always the logical one.
- Replication is not the solution.
- Point-in-time recovery.
- Don't try this at prod: Test your backups.

Transcript

1. Why your backup strategy is probably wrong?
2. © 2022 Percona | Confidential Hello! I am Pep Pla Father of three kids and three cats. In my spare time I’m a Consultant at Percona. You can find me at @peppla 2
3. Strategy: the skill of making or carrying out plans to achieve a goal.
4. © 2022 Percona All backup strategies are wrong • Backups are not a goal. • A goal is to protect your data. • A goal is to ensure your business survival. • A goal is legal compliance. 4
5. © 2022 Percona Recovery Strategy • Your should focus on recovery. • You should have a Recovery Strategy. Recover: To become successful or normal again after being damaged or having problems. Restore: Return something or someone to an earlier good condition. 5
6. © 2022 Percona Infinite Possible Incidents 6
7. © 2022 Percona | Confidential Absolute protection is impossible 7
8. © 2022 Percona Recovery Strategy • Protection from a limited set of incidents ◦ Higher probability. ◦ Higher risk. ◦ Lower protection cost vs risk. • Design Backup-Restore/Availability Methodologies to ensure recovery from these incidents. 8 8
9. © 2022 Percona RPO & RTO 9 9 recovery incident transactions recovery incident transactions detection recovery incident transactions detection remediation recovery incident transactions detection remediation
10. © 2022 Percona RPO & RTO What does “Business” believes? • RPO & RTO are worst case scenario • If everything goes wrong we will be down X hours at most. • If everything goes wrong we will lose a maximum of Y transactions. What SRE and DBA know? • RPO & RTO are best case scenario • If everything goes fine we will be down only X hours. • If everything goes fine we will lose only Y transactions.
11. © 2022 Percona The Schröendinger Backup A backup can suffer of quantum superposition: • It was completed successfully. • You can’t restore it (in a relevant time). Why a successful backup can be irrecoverable? • Too long to restore. • Bugs in backup/storage. • Wrong backup. • Infrastructure not available. • ...
12. © 2022 Percona How to know if the cat is dead or alive? Open the box! Perform restore and recovery tests. As frequent as possible.
13. © 2022 Percona Random Full Page Writes Backups: Cold, Hot and Warm The database consists of two types of files: • dbfiles: contain all data. random access. read write. long term. • logfiles: contain some data. sequential access. write. short term. write A write A’ write B write C write B’ write C’ Sequential Row Data Writes
14. © 2022 Percona • Writes frozen • We copy everything write B’ write C’ Logfiles Cold Backup Datafiles write A write A’ write B write C
15. © 2022 Percona • Copy of files: cp, tar, rsync. • Filesystem snapshot (LVM) • Storage snapshot. Cold Backup
16. © 2022 Percona • Copy datafiles ignoring changes • Copy the required redo log entries • We have all the data required write B’ write C’ Logfiles Hot Backup 16 Datafiles write A write A’ write B write C
17. © 2022 Percona • Percona Xtrabackup • MySQL Enterprise backup • Clone Plugin Hot Backup 17
18. © 2022 Percona Warm backup • Cold backup without closing the database… • … or closing it for a shorter period of time. – Snapshots without shutdown • Recovery needed and make sure snapshot is consistent. – Physical copy of unchanged while open. Sync modified while closed. • Multiple rsync. Last one with the database closed. 18
19. © 2022 Percona The logical song Generate SQL code to rebuild the database. • mysqldump • mysqlpump • mydumper • MySQL Shell dump utilities 19
20. © 2022 Percona Logical backups • Rebuilding the database can take a long time. • Good for partial recovery. • You have a human readable copy of data. 20
21. © 2022 Percona Point in time recovery • We use binary logs to recover until a certain point of time. • We need 3 things: – Valid backup. – Position at the time of the backup. – Binary logs. • Reduces data loss. • Time consuming to apply the binary logs. 21
22. © 2022 Percona Replication is not the solution • Replication is not a backup. • Protects from a reduced set of incidents. • False sense of protection: – Delayed replicas… how much is enough? 22
23. © 2022 Percona Don’t try this at prod! • Test and document your recover procedures. • Disasters always happen in the worst moment: – Time to decide. – No time to do research/learn. – “Business” needs to be involved in the decision making process. 23
24. © 2022 Percona | Confidential Thanks! Any questions? You can find me at: • @peppla • pep.pla@percona.com