A brief analysis of MySQL backup and recovery

1. Introduction

Data is priceless. As a database system, MySQL backup is very important and necessary. There are thousands of reasons for backup, such as preventing failures, security requirements, rollback, auditing, deletion and modification requirements, etc. The importance of backup is self-evident. In addition to the backup itself, how to use the backup to restore services is also a key issue. A backup that cannot be used for restoration is meaningless. This article will mainly give some brief introduction to the two aspects of backup and recovery.

This article is a reading note for the backup-related chapters of "High Performance MySQL".

2. Simple definition of backup and recovery

As mentioned in the introduction, backup is well known to everyone and it is easy for people to pay attention to it. It is common to write regular scripts based on demand or use other methods. But the recovery was less dramatic. For example, perhaps automatic backups are scheduled on a weekly/daily basis. But how often are restore tests of backups performed? Is the backup complete? Can it be used for recovery? If a failure occurs, is the recovery process easy to operate?

Backup is only a data source. How to use the data source to completely restore the system. It is also very important. Backup and recovery are both things you need to master in MySQL operation and maintenance.

The purpose of backup is recovery. If it cannot be restored, it is not called a backup (for example, a RAID array is not a backup. If you DROP DATABASE, the RAID array cannot be restored)

The difference between [Restore] and [Recover]:

• Restore: simply refers to extracting the contents from the backup file and loading it.
• Recovery: A series of measures, including restoring backup files, to restore the service to normal operation, such as restarting MySQL, modifying configuration, and other operations.
  

In other words, recovery is to restore all operations taken before the exception occurs (such as modifying parameters, restarting services, etc.). Do more than just restore a backup.

3. Several factors to consider in the recovery plan

When designing a recovery plan, some factors need to be considered so that better planning can be carried out according to different needs. It can assist in formulating appropriate recovery strategies based on the two requirements of RPO (recovery point objective) and RTO (recovery time objective).

• RPO (Recovery Point Objective): How much data loss can be tolerated? (Do you need to restore all data, or can you tolerate the loss of data since the last backup?)
• RTO (Recovery Time Objective): How long do you have to wait to recover the data? (To what extent can users accept it)
  

Perhaps you should also consider: What needs to be restored? (Entire server, single database, single table, or transaction)

Secondly, the recovery plan needs to be tested regularly, data should be extracted to test whether the backup is indeed effective, and a complete backup recovery should be performed to familiarize yourself with the entire recovery process to ensure that when a problem actually occurs, the recovery can be completed in an orderly manner.

4. Backup

4.1. What does the backup include?

The simplest strategy is to back up only the data and table definitions. But restoring a database requires more content, and the more sufficient the backup is, the easier it will be to restore. (Mainly based on demand)

For example, you can consider backing up the following content based on actual conditions:

1. Binlog and InnoDB transaction log.

2. Master/slave library configuration files.

3. Database operating system configuration (cron, scripts, kernel parameters)

In other words, the backup content can be expanded as needed. If there is a high demand for database recovery or even reconstruction (such as faster recovery), it is also necessary to back up more content. If you need the ability to restore the database from scratch, that requires more work.

4.2 Physical backup and logical backup

A little choice between physical backup and logical backup:

• For large databases, physical backups are a must. If logical backup is too slow, you can also consider using snapshot-based backup as an auxiliary.
• For small databases, logical backups are almost sufficient.
  

Physical backup is simple and efficient, and logical backup should also be done as much as possible. [Both are required, depending on specific needs and resource allocation]

Second: You can't assume that a backup is usable unless you've tested it. For example, use mysqlcheck -A to test the database.

4.3 Binlog backup

Binlog is also an important part of backup because it is needed for point-in-time recovery. Moreover, Binlog is generally very small, and frequent backups are easier to implement. If you have a backup of the data at a certain point in time, plus all the Binlogs since then, you can roll back all changes.

4.3.1. Some strategies for backing up Binlog

FLUSH LOGS
--log_slave_updata

It should be noted that expire_log_days is determined by the modification time of the log file, not the content. (If there is only one Binlog file, it may not be cleaned up). Therefore, be sure to use FLUSH LOGS to refresh Binlog regularly.

4.3.2. Cleaning up old Binlog

It is best to use expire_log_days for automatic cleanup and retain a certain number of days. Use cron to clean up if necessary. Then do not use the cron configured with find+rm to clean up the logs.

0 3 * * * /usr/bin/mysql /var/log/mysql -mtime +N -name "mysql-bin.[0-9]"* | xargs rm

Use the following cron instead:

0 3 * * * /usr/bin/mysql -e "PURGE MASTER LOGS BEFORE CURRENT_DATE - INTERVAL N DAY"

4.3.3. Some notes on Binlog backup

• Increasing the retention time is just a configuration and does not mean that the Binlog itself does not need to be backed up. The binlog still needs to be backed up regularly so that it can be used in conjunction with the most recent backup.
• It should be noted that the slave library also uses Binlog. Therefore, it is necessary to distinguish between the Binlog management of slave libraries and backups.
  

4.4. Incremental backup and differential backup

Incremental backup: A backup of all content that has been modified since any type of backup.

Differential backup: specifically refers to the backup of all contents changed since the last full backup.

That is, differential backups are based on full backups. Incremental backups are based on any backup (such as a specified differential backup.

Differential backup options:

• Do not back up tables that have not changed.
• Do not back up unchanged rows
  

Although doing differential backups can increase recovery speed. But a full backup is still necessary. (Full backups can be done less frequently, but they must be done).

4.5. Backup from the database

Backing up in a slave is sometimes an option that does not interfere with the master and avoids adding more load to the master. Secondly, when planning to back up from a slave, more information should be saved, such as the location (offset) of the slave relative to the master.

First of all, the slave database is not equal to the backup, and it is very common that the data of the slave database and the master database do not match. Secondly, backing up from the slave database can indeed reduce the load when backing up the master database, but it is not good enough. For the sake of stability, it is recommended to perform main database backup and full backup.

4.6 Other Notes

4.6.1. Online backup and offline backup

Offline backup is the simplest and safest. It also has the best consistency. The problem is that most databases cannot tolerate downtime for backup. Therefore, online backup is still used, or non-stop backup.

You can consider performing online backup during the off-peak period of business, which will not have much impact even if the load increases.

4.6.2 Data Consistency

Data consistency: Requirements for data consistency between multiple tables. (For example, if two logically related operations are divided into two transactions, and the backup is performed between the two transactions, it will lead to inconsistent data)

InnoDB can start a transaction when dumping a set of related tables, which can ensure data consistency to a large extent.

However, please note that if the transaction settings are not reasonable, for example, the modification of a set of related tables is divided into two transactions, this will still lead to data inconsistency. (Related operations on a set of tables need to be ensured within one transaction)

4.6.3. Perform backup and recovery tests regularly to confirm the resources required for the entire recovery process

A backup that can be restored is valuable, not just having a backup.

summary

This article explains some basic knowledge and concepts of backup, including some basic concepts, the importance of recovery, and simple strategies for backup and recovery. It also mentions the selection of backup content, differential/incremental backup, Binlog backup, etc. You will need to continue learning to understand the specific operation methods and practices of backup and recovery.

The above is a brief analysis of the details of MySQL backup and recovery. For more information about MySQL backup and recovery, please pay attention to other related articles on 123WORDPRESS.COM!