MySQL Backup Best Practices and Case Study: .IE Continuous Restore - PowerPoint PPT Presentation

MySQL Backup Best Practices and Case Study: .IE Continuous Restore Process Mick Begley Marcelo Altmann Technical Service Manager - IE Senior Support Engineer - Percona Domain Registry

Agenda

Agenda • Why we need backups for ? • Types of backups • Encryption • Compression • Where to store ? • Restoration • Validation • Binlogs • Retention period 3

Who is speaking ?

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 5

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 6

Why we need backups for ?

Why we need backups for ? • Slave provisioning • Build Staging / Dev environments • Disaster recovery - Data Corruption - Malicious SQL - Software Bugs - Hardware failure 8

Replication as backup ? Yes • Master crashes • Database physical file corruption • Any physical hardware failure - CPU - RAM - Disk - Network card 9

Replication as backup ? No • Application Bug • Database Hack • Malicious SQL commands 10

Types of backups

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 12

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 13

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 14

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 15

Am I done ?

N O ! ! !

Encryption

Encryption • Keep your backups safe from unwanted access • openssl • Percona XtraBackup • --encrypt=ALGORITHM - AES128, AES192, AES256 • --encrypt-key=ENCRYPTION_KEY or --encrypt-key-file=KEYFILE 19

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 20

Compression

Compression • Save storage space • Require less network when streaming • Percona XtraBackup - xtrabackup --backup --compress \ --parallel=4 --compress-threads=4 --target-dir=/data/compressed/ • zip / gzip / bzip 22

Where to store ?

Where to store ? • Where are you going to store your backups ? 24

Where to store ? • Where are you going to store your backups ? - Same Server ? 25

Where to store ? • Where are you going to store your backups ? - Same Server ? 26

Where to store ? • Where are you going to store your backups ? - Same Server ? - Same Data-Center? 27

Where to store ? • Where are you going to store your backups ? - Same Server ? - Same Data-Center? 28

Where to store ? • Where are you going to store your backups ? - Same Server ? - Same Data-Center? - Same Region ? 29

Where to store ? • Where are you going to store your backups ? - Same Server ? - Same Data-Center? - Same Region ? 30

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 ? 31

Attention ! ! !

R E S T O R E ! ! !

R E S T 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 34

Validate your data

Validate your data • Run checksum on your data • Ensures your backup has all the data and the data is consistent • Pt-table-checksum / mysqldbcompare 36

Backup your binlogs

Backup your binlogs • Store a 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 38

Retention period / audits

Retention period / audits • How far back in time you may need your data ? • How are you going to store it? • Be prepared to restore 40

.ie Backup Mick Begley IE Domain Registry mbegley@iedr.ie

Agenda • Who are we? • How is our backup setup? • Schedule • Restore times • Questions

Who are the IEDR ? ● 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

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 44

How is our backup setup? ● Local slave and 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? ● Reset DR site backup slave ● Drop all DBs ● Decrypt Dump from backup, unCompress, Restore Dump ● Stop Server ● Compress DataDirectory , Encrypt DataDirectory ● Send to Backup Server

Backup Schedule ● Backups run at times outside of zone pushes ● Backups run at times outside of system batch processing (NRP, Invoicing)

Restore procedure

Thank You Sponsors! 50

April 23-25, 2018 SAVE THE DATE! Santa Clara Convention Center CALL FOR PAPERS OPENING SOON! www.perconalive.com 51

Questions ? Marcelo Altmann Mick Begley @altmannmarcelo @mickarooney