This blog was originally published in January 2021 and updated in November of 2023.
As businesses and applications increasingly rely on MySQL databases to manage their critical data, ensuring data reliability and availability becomes paramount. In this age of digital information, robust backup and recovery strategies are the pillars on which the stability of applications stands.
In this blog, we will review all of the potential MySQL backup and restore strategies, the cornerstones of any application. When it comes to making the best choices for your specific setup, such as your topology and MySQL version, there are several options to explore, each requiring us to consider pertinent questions for informed decision-making.
Why Do MySQL Backups Matter?
MySQL backups play a pivotal role in safeguarding the integrity of your data, providing defense against various unforeseen calamities, hardware malfunctions, data loss, corruption, and inadvertent deletions. Without reliable backups, the consequences of data loss can be severe. Businesses risk operational disruptions, financial losses, damage to their reputation, and even compliance violations. Understanding the importance of MySQL backups and how they can reduce these risks will help organizations guarantee data consistency, business continuity, and the assurance that their data is safe and recoverable when needed.
Learn more: Discover six of the most common causes of poor database performance in our free eBook.
What is the Recovery Time Objective?
The Recovery Time Objective (RTO) refers to the amount of time that may pass during a disruption before it exceeds the maximum allowable threshold specified in the Business Continuity Plan.
The key question related to RTO is, “How quickly must the data on this system be restored?”
What is the Recovery Point Objective?
The Recovery Point Objective (RPO) is the duration of time and service level within which a business process must be stored after a disaster in order to avoid unacceptable consequences associated with a break in continuity.
The key question related to RPO is, “How much data can we lose?”
What are the Different Types of MySQL Backups?
There are two main different MySQL backup types: physical and logical. Below we will provide more insights into both of these backup types as well as some other strategies.
- Physical (Percona XtraBackup, RDS/LVM Snapshots, MySQL Enterprise Backup), and also you can use cp or rsync command lines to copy the datadir as long as mysql is down/stopped.
- Logical (mysqldump, mydumper, mysqlpump, mysql shell only for mysql 8)
Additionally, it’s recommended to create copies of binlog files. This practice serves a crucial purpose: it enables us to recover data up to the point of the last transaction.
Logical Backup:
This is a dump from logical database structure (CREATE DATABASE, CREATE TABLE statements) and content (INSERT statements). This is recommended to be used against smaller amounts of data. The disadvantage of this method is slower (backup and restore) if you compare it with physical backups. Using mydumper you can backup and restore a single database or a single table if it’s needed, and this is useful to copy some data to a different environment to run tests. Also, mydumper can take a consistent (as long as all the tables are InnoDB engine) backup and provides accurate master and slave log positions.
The output is larger than for physical backup, particularly when saved in text format, but it can be compressed on the fly depending on the software you are using. Mydumper can compress and mysqldump needs to add a pipe to redirect the output to gzip, for example.
Logical backups are used to address data corruption or the need to restore a subset of tables.
Physical (Raw) Backup:
In short, this consists of exact copies of database directories and files. This can be a copy for all or a part from MySQL datadir directory. This kind of backup is most used to restore or create a new replica node easily and quickly and is used to address host failure. It’s recommended to restore using the same MySQL version. I recommend using Percona XtraBackup because it can include any related files such as configuration files like cnf config files.
Snapshot Backups:
Some file system implementations enable “snapshots” to be taken. These provide logical copies of the file system at a given point in time, without requiring a physical copy of the entire file system. MySQL itself does not provide the capability for taking file system snapshots but it is available using third-party solutions such as LVM or ZFS.
The disadvantage is that sometimes physical backups do not compress much, because data is usually in a binary format and sometimes the table is already compressed.
Binary Log Backups:
Binlog backups specifically address RPO. Binary log files contain records of each SQL query executed that made changes.
From MySQL 5.6 on, you can use mysqlbinlog to stream binary logs from a remote server. You can combine binlog backups with Percona XtraBackup or mydumper backup to allow restoration up to the end of the most-recently-backed-up binary log.
Incremental / Differential Backups:
An incremental backup is a backup of everything that has changed since the last backup (a binary log backup is a special case of an incremental backup). This is a very good option if the dataset size is huge, as you can take a full backup at the beginning of the week and run incremental backups per day. Also, the backup size is smaller than the full backup.
The main risks associated with incremental backups are:
– A single corrupt incremental backup may invalidate all the others
– Incremental backups typically negatively affect the RTO
For a differential backup, it copies the differences from your last backup, and the advantage is that a lot of data does not change from one backup to the next, so the result can be significantly smaller backups. This saves disk space.
Percona XtraBackup supports both incremental and differential backups.
Why are MySQL Backups Needed?
MySQL backups are needed in case of multiple problems:
- Host Failure: We can get multiple problems from disks stalled or broken disks. Also from cloud services, our DB instance can be broken and it’s non-accessible.
- Corrupted Data: This can happen on a power outage, MySQL wasn’t able to write correctly and close the file, sometimes when MySQL starts again it cannot start due to corrupted data and the crash recovery process cannot fix it.
- Inconsistent Data: When a human makes a mistake, delete/update erroneous data over the primary or replica node.
- DataCenter Failure: power outage or internet provider issues.
- Legislation/Regulation: provide consistent business value and customer satisfaction.
MySQL Backup and Recovery Best Practices
In this section, we’ll explore essential MySQL backup and recovery best practices to safeguard your data and ensure smooth database operations.
Offsite Storage
It’s highly recommended to copy all the backup methods to another place, like the cloud or an external file server, so in case of host failure or data center failure, you have another copy.
Not all the backup files need to be uploaded to the cloud, sometimes the time you need to spend in the download is bigger than the time consumed in the recovery process.
A good approach is to keep 1-7 days locally on the backup server in case a fast recovery is needed, and this depends on your business regulations.
Encryption
Backups have sensitive data, so it’s highly recommended to encrypt, especially for offsite storage. This adds more time when you need to restore a backup but it keeps your data safe.
GPG is a good option to encrypt backups, and if you use this option or some other alternative, don’t forget to get a copy of the keys/passphrase. If you lose it, your backups will be useless.
Discover more about MySQL encryption: Learn how master key rotation works in this blog!
Restore Testing
Depending on your business, it’s highly recommended to test your backups at least once per month. This action validates your backups are not corrupted and it provides critical metrics on recovery time. This process should be automated to get the full backup, restore it, and finally configure this server as a replica from the current primary or another replica. This is good as well to validate that the replication process has no errors.
Many customers are using this methodology to refresh their QA/STG environment to have fresh data from production backups.
In addition to the above, it is recommended to create a manual or automated restore documentation process to keep all the steps together, so in case of disaster, you can follow it without wasting time.
Retention Requirements
Last but not least, it is very important to keep multiple copies of different backup types.
Our best recommendation is:
- One or two physical backups locally on the backup server (as long as space allows it).
- Seven daily and four weekly logical backups locally on the backup server.
- 30 days of binlog backups locally on the backup server.
- For offsite backups (like S3, Google Cloud, etc.), keep monthly backups for one year or more.
For local backups, keep in mind you will need a minimum of 2.5 times the current dataset size as free disk space to save/meet these retention policies. Don’t forget to encrypt all the backup types!
Legal or regulatory requirements may also dictate how long data must be archived.
Verifying MySQL Backups
So you’ve got a great backup procedure, following all the best practices. How do you know the backup succeeded? Did you look at the file size? Did you only check a file was created? Maybe you only looked at the exit code of the tool you used?
Shlomi Noach told me at a previous Percona Live conference “you have not taken a backup until you have verified that backup.” Great advice. Put another way, each backup you take can be considered as Schrödinger’s backup; until you verify it, does it work?
The best practice here is to simply restore a MySQL server using your created backup; however, you created it. The machine handling this restore does not need to be as powerful as the source; a simple VM can manage this task and could be well automated.
You can restore a mysqldump using the mysql client itself:
1 | zcat my_full_backup.sql.gz | mysql |
Using mydumper/myloader:
1 | myloader --directory dump_dir --overwrite-tables --verbose=3 |
Percona XtraBackup:
1 2 3 4 5 6 7 8 9 10 11 | # Prepare the backup xtrabackup --prepare --parallel 4 --use-memory 4G --target-dir /var/backup # Copy backup to original location (ie: /var/lib/mysql), assuming backup taken on same host xtrabackup --copy-back --target-dir /var/backup # Fix file permissions if necessary chown -R mysql:mysql /var/lib/mysql # Start MySQL systemctl start mysql |
Yes, Percona XtraBackup does require more steps, but physical backups will always be the fastest way to backup and fastest way to restore.
Looking for a MySQL backup solution? Discover 10 things you need to know about backup solutions for MySQL before choosing.
Get Started with MySQL Backup and Recovery Solutions from Percona
Percona can help you choose, implement, and optimize the most appropriate MySQL backup and recovery solution for your MySQL ecosystem. If your current solution unexpectedly fails, we can facilitate your recovery with onsite, remote, or emergency support services. We can also help you take steps to prevent another occurrence. Every situation is unique and we will work with you to create the most effective solution for your business.
Learn more about Percona software for MySQL
FAQs
How many backups do we need to keep our data safe?
Your risk tolerance and unique requirements determine the number of backups required. Maintaining a minimum of three backups—one primary and two supplementary copies—is a good idea. The precise figure, however, might change depending on how important historical backups are to your company and how critical your data is.
How do I find out what’s the best retention policy for us?
Evaluating your business needs, compliance requirements, and available storage resources are necessary steps in choosing the best retention policy. Finding the right balance between keeping data for future reference and avoiding overloading your storage is crucial. When defining a suitable retention policy, consult with your team and take industry best practices into consideration.
What are the primary types of MySQL backups, and when should each be used?
The primary types of MySQL backups are logical, physical, and incremental backups. Logical backups are useful for exporting data in a human-readable format. Physical backups capture the binary data files and are suitable for full system recovery. Incremental backups save only changes since the last backup, reducing storage and time requirements. Choose the backup type based on your recovery needs and resource constraints.
How often should I perform MySQL backups, and is there an optimal frequency?
MySQL backup frequency should be in line with your recovery point objective (RPO). Weekly backups may be sufficient for less important data, but daily or even more frequent backups may be necessary for critical systems. To determine the ideal backup frequency, calculate your RPO and weigh it against the system’s performance impact and resource utilization.
What is the recommended strategy for ensuring data consistency during backups?
Use suitable backup techniques, such as locking, transactions, and the consistent snapshot mechanisms offered by MySQL, to guarantee data consistency during backups. By preventing database modifications during the backup procedure, these techniques help guarantee that the data in your backup is consistent with its original state.
What considerations should I keep in mind when selecting a storage solution for my MySQL backups?
When selecting a storage solution for MySQL backups, consider factors such as data retention requirements, scalability, accessibility, and cost. Although they can be more flexible and scalable, cloud-based storage solutions may have additional expenses. On-premises storage provides control but may require careful management. Assess your needs to choose the storage solution that best aligns with your backup strategy and budget.
