Downloaded 14 times
Uploaded byMarcelo Altmann
MySQL Backup Best Practices and Case Study- .ie Continuous Restore Process
The document outlines best practices for MySQL backups, detailing the types of backups (logical, physical, incremental, and differential), the importance of encryption and compression, and strategies for proper storage. It emphasizes the necessity of testing backup restorations to ensure reliability and addresses the management of binlogs and data retention. Additionally, a case study is presented by the IE Domain Registry team, highlighting their backup setup and restoration processes.
MySQL Backup Best Practices and Case Study- .ie Continuous Restore Process
- 1. MySQL Backup BestPractices and Case Study: .IE Continuous Restore Process Marcelo Altmann Senior Support Engineer - Percona Mick Begley Technical Service Manager - IE Domain Registry
- 2.
- 3. 3 Agenda • Why weneed backups for ? • Types of backups • Encryption • Compression • Where to store ? • Restoration • Validation • Binlogs • Retention period
- 4.
- 5. 5 Who is Speaking? • Marcelo Altmann - Senior Support Engineer @ Percona • MySQL DBA @ IE Domain Registry - Certifications • Oracle Certified Professional, MySQL 5.6 Database Administrator • Oracle Certified Professional, MySQL 5.6 Developer • Oracle Certified Professional, MySQL 5 Database Administrator • Oracle Certified Professional, MySQL 5 Developer • Oracle Certified Associate, MySQL 5.0/5.1/5.5 - Oracle ACE Associate - blog.marceloaltmann.com - @altmannmarcelo
- 6. 6 Who is Speaking? • Mick Begley - Technical Services Manager @ IE Domain Registry CLG • Head of IT @ First Derivatives • Service Integration Manager @ HP - Certifications • IT and Psychology • Qualified ITIL Service Manager • PMI Certified Project Manager
- 7. Why we needbackups for ?
- 8. 8 Why we needbackups for ? • Slave provisioning • Build Staging / Dev environments • Disaster recovery - Data Corruption - Malicious SQL - Software Bugs - Hardware failure
- 9. 9 Replication as backup? Yes • Master crashes • Database physical file corruption • Any physical hardware failure - CPU - RAM - Disk - Network card
- 10. 10 Replication as backup? No • Application Bug • Database Hack • Malicious SQL commands
- 11.
- 12. 12 Types of backups- Logical • Structure and data are saved as logical structure • CREATE DATABASE / TABLE • INSERT INTO • Can easily be used for selective restore (Only one database/table) • Good when physical file is fragmented / corrupted • Taken while MySQL is running • Slower than physical • Tools: mysqldump, mydumper, mysqlpump
- 13. 13 Types of backups- Physical • Raw copy of your databases and tables • Can be used for selective restore (Only one database/table) • Fast for either Dump and restore • Can be taken while MySQL is running • Bad for table corruption • Tools: Percona XtraBackup, MySQL Enterprise Backup, snapshots, rsync, cp
- 14. 14 Types of backups- Differential or Incremental • Differential - Full copy of the database - Each differential backup has all the changes since last full backup • Monday: FULL • Tuesday: Incremental since Monday • Wednesday: Incremental since Monday • Thursday: Incremental since Monday
- 15. 15 Types of backups- Differential or Incremental • Incremental - Full copy of the database - Each incremental backup has all the changes since last backup • Monday: FULL • Tuesday: Incremental since Monday • Wednesday: Incremental since Tuesday • Thursday: Incremental since Wednesday
- 16.
- 17. N O !! !
- 18.
- 19. 19 Encryption • Keep yourbackups safe from unwanted access • openssl • Percona XtraBackup • --encrypt=ALGORITHM - AES128, AES192, AES256 • --encrypt-key=ENCRYPTION_KEY or --encrypt-key-file=KEYFILE
- 20. 20 Encryption • Encrypt - xtrabackup--backup --encrypt=AES256 --encrypt-key-file=/data/backups/keyfile --target-dir=/data/backups • --encrypt-threads (used with --parallel ) • Decrypt - xtrabackup --decrypt=AES256 --encrypt-key-file=/data/backups/keyfile --target-dir=/data/backups
- 21.
- 22. 22 Compression • Save storagespace • Require less network when streaming • Percona XtraBackup -xtrabackup --backup --compress --parallel=4 --compress-threads=4 --target-dir=/data/compressed/ •zip / gzip / bzip
- 23.
- 24. 24 Where to store? • Where are you going to store your backups ?
- 25. 25 Where to store? • Where are you going to store your backups ? - Same Server ?
- 26. 26 Where to store? • Where are you going to store your backups ? - Same Server ?
- 27. 27 Where to store? • Where are you going to store your backups ? - Same Server ? - Same Data-Center?
- 28. 28 Where to store? • Where are you going to store your backups ? - Same Server ? - Same Data-Center?
- 29. 29 Where to store? • Where are you going to store your backups ? - Same Server ? - Same Data-Center? - Same Region ?
- 30. 30 Where to store? • Where are you going to store your backups ? - Same Server ? - Same Data-Center? - Same Region ?
- 31. 31 Where to store? • Where are you going to store your backups ? - Same Server ? - Same Data-Center? - Same Region ? •What type of disaster my data must survive ?
- 32.
- 33. R E ST O R E ! ! !
- 34. 34 R E ST O R E ! ! ! •Most important thing when taking backups •If you don’t test your backup, you simple don’t have a backup. •Restore on a fresh server •Keep track of restoration times •Re-configure as a slave •Test PITR
- 35.
- 36. 36 Validate your data •Runchecksum on your data •Ensures your backup has all the data and the data is consistent •Pt-table-checksum / mysqldbcompare
- 37.
- 38. 38 Backup your binlogs •Storea safe copy of your binlogs •Allows you to do point-in-time recovery even if you lose your master •Mysqlbinlog - mysqlbinlog --read-from-remote-server --host=host_name --raw --stop-never binlog_file •MaxScale Binlog Server
- 39. Retention period /audits
- 40. 40 Retention period /audits •How far back in time you may need your data ? •How are you going to store it? •Be prepared to restore
- 41. .ie Backup Mick BegleyIE Domain Registry mbegley@iedr.ie
- 42. Agenda • Who arewe? • How is our backup setup? • Schedule • Restore times • Questions
- 43. Who are theIEDR ? ● The IEDR is the registry for .ie Internet Domain names and maintains the database of .ie registered Internet names. ● Managing since 2000 ● Team of 20 ● Console, API application across Three Datacenters ● 2016 stats (219,858 total registrations, 34,615 new reg, 89% renewal rate) ● Today 231,826 Domains
- 44. 44 What is DNS? ●Domain Name Servers (DNS) are the Internet's equivalent of a phone book. They maintain a directory of domain names and translate them to Internet Protocol (IP) addresses. ● Makes it easier to move around the Internet (dont have to remember IP addresses) ● Database of high importance ● Database pushes out zone file across the world ● Over 70 nodes
- 45. ● Local slaveand DR site backup slave ● Stop replication to slave ● Dump slave ● Restart replication ● Verify Dump, Compress Dump, Encrypt Dump ● Send to Backup Server How is our backup setup?
- 46. ● Reset DRsite backup slave ● Drop all DBs ● Decrypt Dump from backup, unCompress, Restore Dump ● Stop Server ● Compress DataDirectory , Encrypt DataDirectory ● Send to Backup Server How is our backup setup?
- 48. Backup Schedule ● Backupsrun at times outside of zone pushes ● Backups run at times outside of system batch processing (NRP, Invoicing)
- 49.
- 50.
- 51. 51 SAVE THE DATE! CALLFOR PAPERS OPENING SOON! www.perconalive.com April 23-25, 2018 Santa Clara Convention Center
- 52. Questions ? Marcelo Altmann @altmannmarcelo MickBegley @mickarooney