Most organizations do not realize they have a problem with database backups until they need to restore the data and find it’s not there or not in the form that they were expecting.
The designated administrator managing the database environments must be prepared for situations where any failure may cause an impact to the availability, integrity, or usability of a database or application. Reacting to these failures is a key component of the administrator’s responsibilities and their ability to react correctly depends on whether they have a well-planned strategy for database backups and recovery.
Pixar’s “Toy Story 2” famously almost never happened due a command line mis-run causing the movie to be deleted and an in-effective backup strategy in place. That movie went on to take in nearly $500 million dollars worldwide in box office… money that, without the fact that one team member made their own personal backup, may have never been made.
ClusterControl provides you with sophisticated backup and failover features using a point-and-click interface to easily restore your data if something goes wrong and can be your DBA-sidekick when it comes to building an effective backup strategy. There are many aspects to consider though when building such a strategy.
Here at Severalnines we have database experts who have written much about the topic and in this blog we will collect the top resources to help you build your own database backup strategy for MySQL and MariaDB databases more specifically.
If you are running a MySQL or MariaDB environment our best resource for you is the free whitepaper “The DevOps Guide to Database Backups for MySQL and MariaDB.” The guide covers 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 provides best practices that can be applied to high availability topologies in order to make database backups reliable, secure and consistent.
In addition to the whitepaper there are two webinars focused on backups that you can watch on-demand. “MySQL Tutorial – Backup Tips for MySQL, MariaDB & Galera Cluster” and “Become a MySQL DBA – Deciding on a Relevant Backup Solution.” Each of these webinars offer tips and best practices on building a backup plan and summarize much of the content that is available throughout our website.
Here are our most popular and relevant blogs on the topic…
Overview of Backup and Restores
In the blog “Become a MySQL DBA – Backup and Restore” we provide a high-level overview of backups and restores when managing a MySQL environment. Included in the blog is an overview of different backup methodologies, overview of logical and physical backups, and some best practices and guidelines you can follow.
The Impact of MySQL Storage Engines on Backups
In the blog “The Choice of MySQL Storage Engine and its Impact on Backup Procedures” we discuss how the selection of different types of storage engines (like MyISAM, InnoDB, etc) can have an impact on your backup strategy.
Building a Backup Strategy and Plan
In our blog “mysqldump or Percona XtraBackup? Backup Strategies for MySQL Galera Cluster” we discuss the different options available to you when making your backup and restore plan with special focus on doing it in a way that does not affect performance.
Making Sure You Perform a Good Backup
In our blog “How to Perform Efficient Backups for MySQL and MariaDB” we discuss a number of ways to backup MySQL and MariaDB, each of which comes with pros and cons.
Using ClusterControl for Backups
In the blog “ClusterControl Tips & Tricks – Best Practices for Database Backups” we should how to effectively manage your backup plan using ClusterControl. With ClusterControl you can schedule logical or physical backups with failover handling and easily restore backups to bootstrap nodes or systems.
There are several more blogs that have been written over the years that can also aid you in ensuring your backups are performed successfully and efficiently. Here’s a list of them…
Full Restore of a MySQL or MariaDB Galera Cluster from Backup
Performing regular backups of your database cluster is imperative for high availability and disaster recovery. This blog post provides a series of best practices on how to fully restore a MySQL or MariaDB Galera Cluster from backup.
What’s New in ClusterControl 1.4 – Backup Management
This blog post covers the new backup features available in ClusterControl version 1.4.
ClusterControl Tips & Tricks: Customizing your Database Backups
ClusterControl follows some best practices to perform backups using mysqldump or Percona xtrabackup. Although these work for the majority of database workloads, you might still want to customize your backups. This blog shows you how.
Architecting for Failure – Disaster Recovery of MySQL/MariaDB Galera Cluster
Whether you use unbreakable private data centers or public cloud platforms, Disaster Recovery (DR) is indeed a key issue. This is not about copying your data to a backup site and being able to restore it, this is about business continuity and how fast you can recover services when disaster strikes.
Using BitTorrent Sync to Transfer Database Backups Offsite
BitTorrent Sync is a simple replication application providing encrypted bidirectional file transfers that can run behind NAT and is specifically designed to handle large files. By leveraging the simplicity of Bittorrent Sync, we can transfer backup files away from our cluster, enhancing the backups availability and reducing the cost of broken backup, where you can regularly verify your backups off-site.
How to Clone Your Database
If you are managing a production database, chances are high that you’ve had to clone your database to a different server than the production server. The basic method of creating a clone is to restore a database from a recent backup onto a different database server. Other methods include replicating from a source database while it is up, in which case it is important the original database be unaffected by any cloning procedure.
Not Using MySQL? Here are some resources we have to help with other database technologies…
Become a MongoDB DBA: MongoDB Backups
This is our fifth post in the “Become a MongoDB DBA” blog series – how do you make a good backup strategy for MongoDB, what tools are available and what you should watch out for.
Become a MongoDB DBA: Recovering your Data
This is our sixth post in the “Become a MongoDB DBA” blog series – how do you recover MongoDB using a backup.
Become a PostgreSQL DBA – Logical & Physical PostgreSQL Backups
Taking backups is one of the most important tasks of a DBA – it is crucial to the availability and integrity of the data. Part of our Become a PostgreSQL DBA series, this blog post covers some of the backup methods you can use with PostgreSQL.