Back to Whitepapers

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.

Download now

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

1. Introduction

A key operational aspect of database management is to ensure that backups are performed, so that a database can be restored if disaster strikes. Data loss can happen in a number of circumstances: system crash, hardware failure, power failure, human error (accidental DELETE or DROP) or even natural disaster (flood, earthquake, fire). Some of these are almost impossible to prevent from happening. The DBA or System Administrator is usually the responsible party to ensure that the data is protected, consistent and reliable. Backups are an important part of a recovery strategy for your data.

There are a number of ways to backup your database, but it is important to review the RTO and RPO before deciding on a backup strategy. RTO (Recovery Time Objective) is how long you have to recover your data, as it affects the length of your outage. RPO (Recovery Point Objective) is the allowable data loss - how much data can you afford to lose? A tighter RTO and RPO means you will need to spend more money on your infrastructure.

This whitepaper gives an overview of the two most popular backup utilities available for MySQL and MariaDB, namely mysqldump and Percona XtraBackup. We’ll also see how database features like binary logging and replication can be leveraged in our backup strategy. We will discuss some best practices that can be applied to high availability topologies in order to make our backups reliable, secure and consistent.

Want to read the rest?

Download the full whitepaper for free
Download whitepaper