The DevOps Guide to Database Backups for MySQL and MariaDB

This whitepaper 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.

Get the whitepaper

Introducton

This whitepaper 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.

Want to read the rest? Just login and download for free

Table of contents

  • 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
ClusterControl
The only management system you’ll ever need to take control of your open source database infrastructure.
Learn more