Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Percona Live London 2014 - MySQL Backup Strateg...

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

Slides from my talk about MySQL Backups @ Percona Live London 2014

Marcelo Altmann

November 07, 2014
Tweet

More Decks by Marcelo Altmann

Other Decks in Technology

Transcript

  1. MySQL Backup Strategy @ IEDR Marcelo Altmann Oracle Certified Professional,

    MySQL 5 Database Administrator Oracle Certified Professional, MySQL 5 Developer Percona Live London – November 2014
  2. 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
  3. 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
  4. Backup Introduction – What for ?  Add new slave

    to your replication topology  Reproduce some bug on your labs  Point-in-time recovery  Disaster Recovery
  5. 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 )
  6. 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
  7. 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
  8. 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
  9. Logical Backup  mysqldump  Store backup duration  Backup

    output of SHOW SLAVE STATUS\G  Verify exit status and last line of the dump
  10. Logical Backup – exit status and last line Backup (Logical)

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

    Distribute Restore Backup (Physical) Slave Checksum
  12. Logical Backup – Compress and Encrypt  Change file permissions

     Compress backup to save disk space (GZIP, BZIP2, …)  Encrypt your backups ( openssl )
  13. 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
  14. Restore  Drop everything on MySQL restore instance  Restore

    dump  Check exit status of mysql command
  15. 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
  16. Backup binlog - Point in time recovery  mysqlbinlog –-raw

    –-read-from-remote-server –-stop-never  Checksum master binlog and backup binlog  Compress  Encrypt
  17. 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