The DevOps guide to database backups for MySQL and MariaDB

This white paper discusses the two most popular backup utilities available for MySQL and MariaDB, namely mysqldump and Percona XtraBackup. It further covers topics such as how database features like binary logging and replication can be leveraged in backup strategies. And it provides  best practices that can be applied to high availability topologies in order to make database backups reliable, secure and consistent.

What we’ll cover in this white paper

• 1. Introduction
• 2. Impact of Storage Engine on Backup Procedure
  • 2.1. MyISAM/Aria
  • 2.2. InnoDB/XtraDB
    • 2.2.1. Transactions
    • 2.2.2. Crash Recovery
    • 2.2.3. MVCC
  • 2.3. MEMORY
  • 2.4. MyISAM, InnoDB and MEMORY Comparison
• 3. Backup Tools
  • 3.1. mysqldump
    • 3.1.1. How does it work?
      • 3.1.1.1. Non-transactional Tables
      • 3.1.1.2. Transactional Tables
      • 3.1.1.3. Flush Binary Logs
    • 3.1.2. Advantages
    • 3.1.3. Disadvantages
  • 3.2. Percona Xtrabackup
    • 3.2.1. How it works?
    • 3.2.2. Advantages
    • 3.2.3. Disadvantages
  • 3.3. Binary Log
    • 3.3.1. How it works?
    • 3.3.2. Advantages
    • 3.3.3. Disadvantages
    • 3.3.4. Restoring with Binary Logs
      • 3.3.4.1. Full Restore
      • 3.3.4.2. Partial Restore
• 4. Performing Backup Efficiently
  • 4.1. Backup Credentials
  • 4.2. Storage Engine
  • 4.3. Dataset Size
  • 4.4. Recovery Objective
  • 4.5. High Availability Setup
  • 4.6. Delta Size (changes between two backup points)
  • 4.7. Backup Size
  • 4.8. Encryption
  • 4.9. Dedicated Backup Server
• 5. Backup Management
  • 5.1. Backup Scheduling
  • 5.2. Backup Verification and Integrity
    • 5.2.1. mysqlcheck
    • 5.2.2. mysqldbcompare
    • 5.2.3. pt-table-checksum
  • 5.3. Backup Availability
    • 5.3.1. Onsite Storage
    • 5.3.2. Offsite Storage
    • 5.3.3. Hybrid Storage
  • 5.4. Backup Housekeeping
  • 5.5. Backup Failover
• 6. ClusterControl as Backup Manager
• 7. Conclusion