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

MySQL Backup Best Practices and Case Study: .IE Continuous Restore Process

Marcelo Altmann Senior Support Engineer - Percona Mick Begley Technical Service Manager - IE Domain Registry

Agenda

3

Agenda

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

Who is speaking ?

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

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

Why we need backups for ?

8

Why we need backups for ?

• Slave provisioning
• Build Staging / Dev environments
• Disaster recovery
• Data Corruption
• Malicious SQL
• Software Bugs
• Hardware failure

9

Replication as backup ? Yes

• Master crashes
• Database physical file corruption
• Any physical hardware failure
• CPU
• RAM
• Disk
• Network card

10

Replication as backup ? No

• Application Bug
• Database Hack
• Malicious SQL commands

Types of backups

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

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

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

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

Am I done ?

N O ! ! !

Encryption

19

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

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

Compression

22

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

Where to store ?

24

Where to store ?

• Where are you going to store your backups ?

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 ?

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?

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 ?

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 ?

Attention ! ! !

R E S T O R E ! ! !

34

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

Validate your data

36

Validate your data

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

Backup your binlogs

38

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

Retention period / audits

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

.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

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

• 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

How is our backup setup?

Backup Schedule

• Backups run at times outside of zone pushes
• Backups run at times outside of system batch processing (NRP,

Invoicing)

Restore procedure

50

Thank You Sponsors!

51

SAVE THE DATE!

CALL FOR PAPERS OPENING SOON!

www.perconalive.com

April 23-25, 2018

Santa Clara Convention Center

Questions ?

Marcelo Altmann @altmannmarcelo Mick Begley @mickarooney