Notes on the MySQL database backup process

Today I looked at some things related to data backup and summarized several things to note about MySQL data backup. Let me share them briefly.

Each DBA may have a different understanding of MySQL backup. Backup can be divided into the following dimensions:

File type classification:

1. Physical backup. Physical backup represented by xtrabackup is the most commonly used backup method and is often used to back up large databases.

2. Logical backup, represented by mysqldump, is more suitable for backing up data less than 50G.

Classification by backup content:

1. Full backup, which is easy to understand, is a complete backup of the database

2. Incremental backup is based on full backup. For example, if the full backup deadline is yesterday, then the data from yesterday to today is incremental backup.

3. Log backup. Log backup is to back up the MySQL binlog based on the backup file. We know that the binlog records the DDL and DML operations of MySQL. Binlog can be used to restore an intermediate state of the database.

Today, when I was sorting out the online backup content, I found the following knowledge points that may be useful. If they are helpful to you, that would be great. If not, just treat it as my own fun~

1. When we use xtrabackup to back up, for a single machine with multiple instances, it is best to back up at different times, because backing up multiple MySQL instances at the same time will have a greater impact on disk IO. If there is other data being written at this time, it will have an impact

2. You can use NFS mounting to back up local data on a remote machine. This can alleviate the local backup pressure and ensure that problems with the local hard disk do not affect data recovery. When applying this method, you need to consider the network bandwidth between machines. The architecture diagram is as follows:

3. We know that if we use the backup file backed up by xtrabackup to restore, we need to perform two steps. The first step is apply_log and the second step is copy back. It is recommended to directly apply log after the backup is completed. If it fails, you can back it up again. Otherwise, if it is available by default, it will be difficult to deal with problems when restoring.

4. Incremental recovery based on binlog is also a more convenient approach. It not only performs full backup according to a certain period, but also regularly backs up binlog. During this period, data at any time point can be restored through the backed-up binlog.

5. If there are multiple machines online, we can use mysqldump and xtrabackup to perform backups and set a threshold, such as 50G. For data volumes below 50G, mysqldump performs better. For data volumes above 50G, consider using xtrabackup to perform backups.

6. The purpose of backing up data is to restore it. It is best to try to restore it after the backup is completed to ensure the availability of the backup data.

7. For instances with large amounts of data, sometimes the backup time is particularly long and recovery is inconvenient. You can use the cross-data center slave library method to back up data. At this time, you can build a delayed slave library so that when the main library errs, the slave library can stop in time and skip the related erroneous operation steps.

The above are the details of the precautions for the MySQL database backup process. For more information about MySQL backup, please pay attention to other related articles on 123WORDPRESS.COM!