Detailed explanation of MySQL backup process using Xtrabackup

01 Background

Xtrabackup is an open source MySQL hot backup tool developed by Percona. I used it frequently in my previous work, but it was only at the stage of use. I didn't do much research on the details of this tool. Today I looked at the process carefully and gained something. I wrote it down and recorded it. If there is anything wrong, please correct me.

The Xtrabackup tool can back up InnoDB, XtraDB, and MyISAM tables. It supports all versions of Percona Server and is compatible with MySQL and MariaDB. It also supports some advanced features, such as streaming backup, compression, encryption, and incremental backup.

The advantages are as follows:

1. There is no need to stop the database to perform InnoDB hot backup. It should be the preferred tool when backing up data of more than 50G.

2. Support incremental backup of MySQL and transfer it to other servers via streaming.

3. Backing up MySQL will not increase the server load.

02 Introduction

In this tool, there are two main applications, namely innobackupex and xtrabackup. The former is a perl script, and the latter is a C++ variant binary program. It should be noted that the latter cannot back up tables other than the Innodb storage engine. Innobackup is a wrapper for xtrabackup and can back up non-Innodb tables.

As mentioned above, everyone may know. It should be noted here that since some storage engines of tables in system databases such as MySQL are based on MyISAM, innobackupex is generally used to back up system libraries.

03 Workflow

The following describes the workflow of Innobackupex and Xtrabackup. There may be some overlaps, but the two work together to complete the backup task.

The main workflow is as follows:

1. After innobackupex is started, it will first create the xtrabackup process and start it, and then wait for xtrabackup to complete the backup of innodb related files.

2. When xtrabackup backs up InnoDB related files, it will start the following two threads: one is the ibd copy thread, which is responsible for copying the tablespace ibd file, and the other is the redo log copy thread, which is responsible for copying the redo log information. The redo log thread starts before the ibd thread. The redo log thread will copy the redo log from the most recent checkpoint (checkpoints have been discussed in previous articles). After the copy is complete, the ibd thread is started. It should be noted that during this process, the innobackupex process is in a waiting state, waiting to be awakened by the xtrabackup process.

3. After xtrabackup completes copying ibd (the redo log thread may still be working), it notifies the innobackupex process and enters the wait state.

4. After innobackupex receives the notification, it will execute the backup lock, channel consistency point, and then start copying non-innodb files.

5. After the non-innodb files are copied, innobackupex starts to obtain the binlog location.

6. Create the xtrabackup_binlog_info file and write the binlog point information into the file.

7. Then, innobackupex will initiate a notification to the xtrabackup process and enter the waiting state.

8. After receiving the notification, the xtrabackup process stops the redo log copy thread, informs that the redo log copy is complete, and then notifies innodbbackupex to start releasing the lock.

9. Next, there are some finishing tasks, such as releasing resources, backing up metadata information, printing the backup directory, backing up the location information of binlog, and writing xtrabackup_info file information.

10. The innobackupex process waits for the xtrabackup process to finish and then exits.

04 Some questions

1. Why do we need to copy redo logs?

This is because the ibd file may be modified during the backup process, so the backed-up file may contain dirty data. When restoring, it is necessary to recover the data through the redo log, that is, apply the transactions that have been committed and roll back those that have not been committed.

2. How do innobackupex and xtrabackup interact?

In fact, the essence is to delete and create two specific files. These two processes will monitor whether the specified directory contains specific files. If it does, they will work. If not, they will stop working and enter a waiting state.

05 Back up your files

Generally, after the backup is completed, 7 important files will be generated in the backup directory, including:

• backup-my.cnf: This file contains the options in my.cnf required for backup. When restoring, it will rely on this file.
• xtrabackup_checkpoints: records the backup type, backup status information, and incremental backup depends on this information
• xtrabackup_binlog_info: This file records the binary file information and offset location information during backup.
• xtrabackup_binlog_pos_innodb: This file records the binary files and location information of the innodb table. This file is created when --apply-log is executed.
• xtrabackup_binary: This file records the xtrabackup binary file used by the backup process.
• xtrabackup_logfile: This file records the data required for the apply log operation. If the apply log takes a long time, this file will be very large.
• xtrabackup_slave_info: This file is commonly used. It records the information required by the change master statement when establishing a master-slave relationship.

The above is the detailed content of the detailed explanation of the MySQL backup process using Xtrabackup. For more information about MySQL backup using Xtrabackup, please pay attention to other related articles on 123WORDPRESS.COM!