Percona Live London 2014 - MySQL Backup Strategy @ IE Domain Registry

Slide 1

Slide 1 text

MySQL Backup Strategy @ IEDR Marcelo Altmann Oracle Certified Professional, MySQL 5 Database Administrator Oracle Certified Professional, MySQL 5 Developer Percona Live London – November 2014

Slide 2

Slide 2 text

Who am I ?  MySQL Database Administrator @ IE Domain Registry (IEDR)  Student  Systems for Internet @ FEEVALE University  Oracle Certified Professional  MySQL 5 Database Administrator  MySQL 5 Developer  marceloaltmann.com  @altmannmarcelo

Slide 3

Slide 3 text

What IEDR Does ?  Manages the official country code top level domain for Ireland - .ie  Maintain the database of .ie registered domain names  MySQL 5.6  Mainly InnoDB  ~80% reads

Slide 4

Slide 4 text

Backup Introduction – What for ?  Add new slave to your replication topology  Reproduce some bug on your labs  Point-in-time recovery  Disaster Recovery

Slide 5

Slide 5 text

Backup Introduction - Replication as backup  Replication is a backup, but not for all scenarios:  When is replication a backup ?  Physical file corruption  Any Master Server component fail ( RAM, Disk, Network, CPU )

Slide 6

Slide 6 text

Backup Introduction – Replication as backup  Including OS errors

Slide 7

Slide 7 text

Backup Introduction – When Replication doesn’t works as a backup?  Application bugs  Someone hacks into your database server  Wrong admin commands like DELETE / UPDATE / DROP

Slide 8

Slide 8 text

Backup - Logical  Logical  Save information represented as logical structure ( CREATE DATABASE, CREATE TABLE, INSERT )  Can be used to restore all databases, single database, single table  Slower than Physical  It's taken while MySQL is running  Done via mysqldump, mydumper, SELECT ... INTO OUTFILE

Slide 9

Slide 9 text

Backup - Physical  Raw copies of directory and files of database contents (copy of MySQL datadir)  No selective restore  Faster than Logical  Done via OS copy commands, mysqlbackup, mysqlhotcopy, percona xtrabackup

Slide 10

Slide 10 text

Backup life cycle Backup (Logical) Verify Encrypt Distribute Restore Backup (Physical) Slave Checksum

Slide 11

Slide 11 text

Backup - Infrastructure

Slide 12

Slide 12 text

Backup - Infrastructure

Slide 13

Slide 13 text

Logical Backup Backup (Logical) Verify Encrypt Distribute Restore Backup (Physical) Slave Checksum

Slide 14

Slide 14 text

Logical Backup  mysqldump  Store backup duration  Backup output of SHOW SLAVE STATUS\G  Verify exit status and last line of the dump

Slide 15

Slide 15 text

Logical Backup – exit status and last line Backup (Logical) Verify Encrypt Distribute Restore Backup (Physical) Slave Checksum

Slide 16

Slide 16 text

Logical Backup – exit status and last line

Slide 17

Slide 17 text

Logical Backup – exit status and last line

Slide 18

Slide 18 text

Logical Backup – Compress and Encrypt Backup (Logical) Verify Encrypt Distribute Restore Backup (Physical) Slave Checksum

Slide 19

Slide 19 text

Logical Backup – Compress and Encrypt  Change file permissions  Compress backup to save disk space (GZIP, BZIP2, …)  Encrypt your backups ( openssl )

Slide 20

Slide 20 text

Logical Backup – Compress and Encrypt

Slide 21

Slide 21 text

Distribute – Same Server Backup (Logical) Verify Encrypt Distribute Restore Backup (Physical) Slave Checksum

Slide 22

Slide 22 text

Distribute – Same Server

Slide 23

Slide 23 text

Distribute – Same DC

Slide 24

Slide 24 text

Distribute – Same Region

Slide 25

Slide 25 text

Distribute

Slide 26

Slide 26 text

Distribute  Send copy of your backups to different datacenter  Pay attention on the distance between datacenters – Natural Disasters!  scp / rsync  Keep copy for 1 week

Slide 27

Slide 27 text

Restore Backup (Logical) Verify Encrypt Distribute Restore Backup (Physical) Slave Checksum

Slide 28

Slide 28 text

Restore  Drop everything on MySQL restore instance  Restore dump  Check exit status of mysql command

Slide 29

Slide 29 text

Physical Backup – Fast Recovery Backup (Logical) Verify Encrypt Distribute Restore Backup (Physical) Slave Checksum

Slide 30

Slide 30 text

Physical Backup – Fast Recovery  Stop MySQL  Compress datadir  Encrypt file

Slide 31

Slide 31 text

Slave Backup (Logical) Verify Encrypt Distribute Restore Backup (Physical) Slave Checksum

Slide 32

Slide 32 text

Slave

Slide 33

Slide 33 text

Slave  Configure restore server as slave  Use --master-data  Use --relay-log-info-repository=TABLE  Use Relay_Master_Log_File and Exec_Master_Log_Pos from SHOW SLAVE STATUS  Use GTID

Slide 34

Slide 34 text

Checksum Backup (Logical) Verify Encrypt Distribute Restore Backup (Physical) Slave Checksum

Slide 35

Slide 35 text

Checksum  Verify data integrity between master and slaves  pt-table-checksum  Monitor time

Slide 36

Slide 36 text

Backup binlog - Point in time recovery  mysqlbinlog –-raw –-read-from-remote-server –-stop-never  Checksum master binlog and backup binlog  Compress  Encrypt

Slide 37

Slide 37 text

Audit Copy  Allows you to restore your database to any point in time  Grouped by month  First Logical and Physical backup of each month  All binary logs

Slide 38

Slide 38 text

Questions? @altmannmarcelo marceloaltmann.com