As businesses and applications increasingly rely on MySQL databases to manage their critical data, ensuring data reliability and availability becomes critical. In this digital information age, a strong backup and recovery strategy is the backbone of application stability.
In this article, we will review all the commonly used MySQL backup and recovery strategies, which are the cornerstone of any application. There are multiple options available for your specific scenario, each requiring us to consider relevant issues to make an informed decision.
Author: walter-garcia
Source of this article and cover: https://www.percona.com/blog, translated by the Axon open source community.
This article is approximately 2,500 words long and should take approximately 7 minutes to read.
Why are MySQL backups important?
MySQL backup plays a key role in protecting data integrity and preventing various unforeseen disasters, hardware failures, data loss, corruption and accidental deletion. Without reliable backup, the consequences of data loss can be severe. Businesses face the risk of operational disruption, financial losses, reputational damage and even compliance violations. Understanding the importance of MySQL backups and how they reduce these risks will help organizations guarantee data consistency, business continuity, and ensure data is safe and recoverable when needed.
What is RTO?
RTO (Recovery Time Objective) is the maximum length from the fault to the time when the business can be restored. Related questions are:
How long does it take to recover?
What is RPO?
RPO (Recovery Point Objective) is the amount of data loss that the business system can tolerate after a failure occurs. Related questions are:
How much data will be lost?
What are the MySQL backup types?
There are two main types of MySQL backups: physical backup and logical backup. Below we’ll provide more insight into these two backup types, as well as some other strategies.
- Physical (Percona XtraBackup, RDS/LVM snapshots, MySQL Enterprise Backup), you can also use the cp or rsync command line to copy the data directory datadir as long as the MySQL service is shut down.
- Logic (mysqldump, mydumper, mysqlpump, mysql shell only for MySQL 8).
Additionally, it is recommended to create a copy of the binlog file. This practice serves an important purpose: it allows us to restore data to the last transaction.
Logical Backup
This is a dump of the logical database structure (CREATE DATABASE, CREATE TABLE statements) and contents (INSERT statements). It is recommended for smaller amounts of data. The disadvantage of this method is slower speed (backup and recovery) if compared to physical backup. If needed, you can use mydumper to backup and restore a single database or a single table, which is useful for copying some data to a different environment for running tests. Additionally, mydumper can perform consistent (as long as all tables are InnoDB engine) backups and provide accurate master and slave log locations.
The output is larger than a physical backup, especially when saved in text format, but it can be compressed on the fly depending on the software you use. For example, mydumper can compress, while mysqldump requires adding a pipe to redirect the output to a gzip file.
Logical backups are used to address data corruption or the need to restore a subset of tables.
Physical Backup
In short, it consists of an exact copy of the database directories and files. This can be a full or partial copy of the MySQL datadir directory. This type of backup is most commonly used to restore or create new replica nodes easily and quickly, and to resolve host failures. It is recommended to use the same MySQL version for recovery. It is recommended to use Percona XtraBackup as it can contain any relevant files such as configuration files such as cnf configuration files.
Snapshot Backup
Some file system implementations allow “snapshots” to be stored. They provide a logical copy 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 functionality to take file system snapshots, but this can be achieved using third-party solutions such as LVM or ZFS.
The disadvantage is that sometimes physical backups don’t compress much because the data is usually in binary format and sometimes the tables are already compressed.
Binary log backup
Binlog backup is specifically for RPO. The binary log file contains a record of every changed SQL query that was executed.
Starting with MySQL 5.6, you can use mysqlbinlog to stream binary logs from a remote server. Binlog backups can be combined with Percona XtraBackup or mydumper backups to allow recovery to the end of the most recently backed up binary log.
Incremental/differential backup
An incremental backup is a backup of everything that has changed since the last backup (binary log backups are a special case of incremental backups). This is a very good option if the data set size is large, as you can take a full backup at the beginning of the week and run incremental backups every day. Additionally, the backup size is smaller than a full backup.
The main risks associated with incremental backups are:
- A single corrupted incremental backup can invalidate all other backups
- Incremental backups often have a negative impact on RTO
With a differential backup, which copies the differences from the last backup, the advantage is that a large amount of data does not change from one backup to the next, so the result can be a significantly smaller backup. This saves disk space.
Percona XtraBackup supports incremental and differential backups.
Why do you need a MySQL backup?
MySQL backups are needed when a variety of problems occur:
- Host Failure: We can experience a variety of issues due to disk stalls or disk corruption. Likewise, in cloud services, our database instance can become corrupted and become inaccessible.
- Data Corruption: This can happen when there is a power outage and MySQL fails to write to and close the file properly, sometimes when MySQL starts again it fails to start due to data corruption and the crash recovery process cannot fix it.
- Data Inconsistency: When people make mistakes, erroneous data is deleted/updated through the master node or replica node.
- Data center failure: Power outage or Internet provider issue.
- Legislation/Regulations: Deliver consistent business value and customer satisfaction.
MySQL Best Practices for Backup and Recovery
In this section, we’ll explore basic MySQL backup and recovery best practices to protect your data and keep your database running smoothly.
Offsite storage
It is highly recommended that all backup methods be replicated to another location, such as the cloud or an external file server, to ensure there is another copy in the event of a host failure or data center failure.
Not all backup files need to be uploaded to the cloud, and sometimes you need to spend more time downloading than the recovery process.
A good approach is to keep it locally on the backup server for 1-7 days in case a quick recovery is needed, depending on your business regulations.
Encryption
Backups contain sensitive data, so encryption, especially off-site storage, is highly recommended. This adds more time when you need to restore the backup, but keeps your data safe.
GPG is a good choice for encrypting backups, don’t forget to get a copy of the key/password if you use this option or another alternative. Your backup is useless if lost.
Restore Test
Depending on your business, it is highly recommended to test backups at least once a month. This action verifies that your backup is not corrupted and provides key metrics on recovery time. This process should be automated to take a full backup, restore it, and ultimately configure this server as a replica of the current master or another replica. This also helps verify that the copy process is error-free.
Many customers are using this approach to refresh their QA/STG environments to get the latest data from production backups.
In addition to the above, it is recommended to create a manual or automated recovery documentation process to put all the steps together so that in the event of a disaster, you can follow it without wasting time.
Retention Requirement
Last but not least, it is important to keep multiple copies of different backup types.
Our best advice is:
- One or two physical backups local to the backup server (as space allows).
- 7 daily and 4 weekly logical backups locally on the backup server.
- Back up the local 30-day binlog backup of the server.
- For off-site backups (like S3, Google Cloud, etc.), monthly backups are retained for a year or more.
For local backups, keep in mind that you will need at least 2.5 times the current data set size in free disk space to save/meet these retention policies. Don’t forget to encrypt all backup types!
Legal or regulatory requirements may also dictate how long data must be archived.
Verify MySQL backup
So you’ve got a backup process that follows all best practices. So how do you know the backup was successful? Have you looked at the file size? Did you just check that one file was created? Maybe you just looked at the exit code for the tool you’re using?
“You haven’t made a backup until you’ve verified it.” Good advice. In other words, every backup you make could be considered a Schr?dinger’s backup; are you sure it’s valid until you verify it?
The best practice here is to simply restore the MySQL server using the backup you created; however, you created it. The machine handling this recovery does not need to be as powerful as the source; a simple virtual machine can manage this task and can be automated well.
You can restore mysqldump using the mysql client itself:
zcat my_full_backup.sql.gz | mysql
Use mydumper/myloader:
myloader --directory dump_dir --overwrite-tables --verbose=3
Percona XtraBackup:
# 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 backup is always the fastest way to back up and the fastest way to restore.
For more technical articles, please visit: https://opensource.actionsky.com/
About SQLE
SQLE is a comprehensive SQL quality management platform that covers SQL auditing and management from development to production environments. It supports mainstream open source, commercial, and domestic databases, provides process automation capabilities for development and operation and maintenance, improves online efficiency, and improves data quality.
SQLE get
| Type | Address |
|---|---|
| Repository | https://github.com/actiontech/sqle |
| Documentation | https://actiontech.github.io/sqle-docs/ |
| Release information | https://github.com/actiontech/sqle/releases |
| Data review Plug-in development documentation | https://actiontech.github.io/sqle-docs/docs/dev-manual/plugins/howtouse |