mysql-Physical Backup-percona xtrabackup Backup principle

Percona xtrabackup ( PXB) is a backup tool developed by Percona Company for the physical hot standby of MySQL database, supporting MySQL (Oracle), Percona Server and MariaDB, and all open source, is the industry's conscience. Tool SetAfter the package is installed, there are 4 executable files, as follows:

usr├── bin│   ├── innobackupex│   ├── xbcrypt│   ├── xbstream│   └── xtrabackup

The most important is the innobackupex and xtrabackup , the former is a Perl script, the latter is a C/s compiled binary.

xtrabackupis used to back up the InnoDB table, cannot back up non-InnoDB tables, and MYSQLD server has no interaction; innobackupex scripts are used to back up non-InnoDB tables, and commands are invoked xtrabackup to back up InnoDB tables, and MYSQLD server sends Commands to interact, such as read-Lock (FTWRL), get-bit-point (SHOW SLAVE STATUS), and so on. In simple terms innobackupex , xtrabackup a layer of encapsulation is done on top.

In general, we would like to be able to back up the MyISAM table, although we may not use the MyISAM table, but the system table under the MySQL library is MyISAM, so the backup is basically through the innobackupex command, another reason is that we may need to save the bit information.

The other 2 tools are relatively small, and xbcrypt are used for decryption; xbstream similar to tar, it is a stream file format that Percona itself implements to support concurrent writes. Both are used when backing up and decompression (if the backup uses encryption and concurrency).

The main character of the introduction of this document is innobackupex and xtrabackup .

Communication mode

The interaction and coordination between the 2 tools is achieved through the creation and deletion of control files, and the main files are:

• Xtrabackup_suspended_1
• Xtrabackup_suspended_2
• Xtrabackup_log_copied

For a chestnut, let's see how xtrabackup_suspended_2 coordinates 2 tool processes during backup.

1. innobackupexAfter starting the xtrabackup process, will be waiting for xtrabackup the backup InnoDB file, the way is to wait for xtrabackup_suspended_2 this file is created;
2. xtrabackupAfter the InnoDB data is prepared, the file is created in the specified directory, and then the file is innobackupex deleted;
3. innobackupexAfter detecting the file xtrabackup_suspended_2 is created, continue to go down;
4. innobackupexAfter backing up the non-InnoDB table, delete xtrabackup_suspended_2 This file so that the notification xtrabackup can continue, and then wait for xtrabackup_log_copied to be created;
5. xtrabackupOnce the Xtrabackup_suspended_2 file has been deleted, you can continue down.

It feels a bit strange, through the existence of files to control the process, this way is very unreliable, because it is very easy to be external interference, such as the file was mistakenly deleted by others, or 2 running backup control files mistakenly placed in the same directory, waiting for the backup to mess up, but Percona is so dry.

The reason for this, is mainly due to Perl and C-binary 2 processes, there is no easy and convenient way to communicate, make a protocol what is too much trouble. But the official also felt that this way is not reliable, 11 a blueprint to use C rewrite innobackupex , finally implemented in the 2.3 version, the innobackupex function is fully integrated into the xtrabackup inside, only a binary, in addition to the use of compatibility considerations, innobackupex as xtrabackup of a soft chain. For two development, 2.3 was freed from the burden of collaboration with the previous 2 processes, significantly better than previous versions. Considering the long-term existence of Perl + C architecture, most readers also use the basic 2.3 version, the introduction of this article is also based on the old Architecture (2.2 version), but the principle and 2.3 are the same, but the implementation of the difference.

Backup process

The entire backup process is as follows:

2. innobackupexAfter booting, a process is forked, the process is started xtrabackup , and then the xtrabackup IBD data file is waited for backup;
4. xtrabackupIn the backup InnoDB related data, there are 2 kinds of threads, 1 are redo copy thread, responsible for copying redo files, 1 kinds of IBD copy thread, responsible for copying IBD file, redo copy thread only one, start before IBD copy threads, end after IBD thread ends. xtrabackupAfter the process begins execution, the redo copy thread is started, the redo log is copied sequentially from the most recent checkpoint point, and then the IBD data copy thread is started, and during the xtrabackup copy IBD process, the innobackupex process waits (waiting for the file to be created).
6. xtrabackupAfter the copy is completed, the IDB is notified innobackupex (by creating the file), while itself enters the wait (redo thread continues to copy);
8. innobackupexAfter receiving the xtrabackup notification, execute FLUSH TABLES WITH READ LOCK (FTWRL), obtain the consistency site, and then start backing up non-InnoDB files (including frm, MYD, MYI, CSV, opt, par, etc.). Copy non-InnoDB file process, because the database is in the global read-only state, if the main library backup in the business, to be particularly careful, non-InnoDB table (mainly MyISAM) more than the whole library read-only time will be longer, this impact must be evaluated.
10. When innobackupex all non-InnoDB table files have been copied, the notification xtrabackup (by deleting the file) and entering the wait (waiting for another file to be created);
12. xtrabackupinnobackupexafter receiving the backup non-InnoDB notification, stop redo copy thread and notify innobackupex redo log Copy to complete (by creating the file);
14. innobackupexAfter receiving the redo backup completion notification, it begins to unlock and execute UNLOCK TABLES ;
16. Finally innobackupex and the xtrabackup process each completes the finishing work, such as the release of resources, write backup metadata information, etc., innobackupex waiting for the xtrabackup child process to exit.

The file copy described above, is the backup process directly through the operating system to read data files, only in the execution of SQL command and database interaction, basically do not affect the operation of the database, in the backup non-InnoDB will be a period of time only read (if there is no MyISAM table, read-only time in a few seconds or so), When backing up a InnoDB data file, there is no effect on the database, it is a true hot spare.

InnoDB and non-InnoDB files are backed up by copying files, but in different ways, the former is page-granular (), the xtrabackup cp or TAR command ( innobackupex ), which xtrabackup verifies the checksum value when reading each page. Ensure that the data block is consistent, while innobackupex the CP MyISAM file has been flush (FTWRL), the file on the disk is complete, so the final backup set of data files are written in full.

Incremental backup

PXB is supported for incremental backups, but only for InnoDB increments, InnoDB each page has an LSN number, the LSN is globally incremented, the current LSN number is recorded when the page is changed, and the greater the LSN in the page indicates that the current page is newer (recently updated). Each backup records the LSN that is currently backed up (in the Xtrabackup_checkpoints file), and the incremental backup is simply a copy of the page with the LSN greater than the last backup, skipping over the last backup, and each IBD file is eventually backed up with Delta Delta files.

MyISAM is a mechanism with no incremental, and each incremental backup is a copy of all.

The incremental backup process, like full backups, is only different on IBD file copies.

Recovery process

If you look at the log of the recovery backup set, you will find that it is very similar to mysqld startup, in fact, the recovery of the backup set is similar to mysqld crash, once crash recover.

The purpose of recovery is to restore the data in the backup set to a consistency point, the so-called consistent refers to the original database at a certain point in time the state of the engine data, such as MyISAM in the data corresponding to the 15:00 time point, the data in the InnoDB corresponds to 15:20, the state of the data is inconsistent. The PXB backup set corresponds to a consistent point, that is, when the backup FTWRL point in time, the recovered data, corresponding to the original database FTWRL state.

Because the database is read-only after FTWRL the backup, the non-InnoDB data is copied in the case of holding the global read lock, so the non-InnoDB data itself corresponds to FTWRL time point; the InnoDB ibd file copy was made before FTWRL, and the different IBD texts were copied. The last update time point is not the same, this state of IBD file is not directly used, but redo log is a continuous copy from the beginning of the backup, the last redo log point is obtained after holding FTWRL, so finally through the redo application of IBD data point of time and FTWR L the same.

So the recovery process only involves InnoDB file recovery, non-InnoDB data is not moving. Once the backup is restored, the data file can be copied to the corresponding directory and then started by mysqld.

mysql-Physical Backup-percona xtrabackup Backup principle