Detailed introduction to the backup principle of xtrabackup

Percona XtraBackupWorking principle

Percona XtraBackup is based on the crash recovery function of InnoDB. Copies the InnoDB data files, resulting in internally inconsistent data; but then it performs crash recovery on the files, making them a consistent, usable database again.

This is because InnoDB maintains a redo log, also called a transaction log. This contains a record of every change to InnoDB data. When InnoDB starts, it checks the data files and transaction log and performs two steps. It applies committed transaction log entries to the data files and performs undo operations on any transactions that modified the data but did not commit.

Percona XtraBackupStarts working by recording the log sequence number (LSN), and then copies the data files. This takes a short time to complete, so if the files are changing, they reflect the state of the database at different points in time. At the same time, Percona XtraBackup runs a background process that monitors the transaction log file and copies changes from it. Percona XtraBackup This needs to be done continuously because the transaction log is written in a circular fashion and can be reused after a period of time. Percona XtraBackupTransaction logging is required for each change since the start of execution of the data file.

Percona XtraBackup will use backup locks as a lightweight alternative. This feature is available in Percona Server 5.6+. Percona XtraBackup Use this to automatically copy non-InnoDB data to avoid blocking DML queries that modify InnoDB tables. When the server supports backup locks, xtrabackup will first copy the InnoDB data, run and copy the MyISAM tables and .frm files. Once completed, the backup of the files will begin. It will back up .frm, .MRG, .MYD, .MYI, .TRG, FLUSH TABLES WITH READ LOCKLOCK TABLES FOR BACKUP.par

NOTE

LOCK ONLYMyISAMAnd other non-InnoDB tables, only backed up after Percona XtraBackup completes all InnoDB/XtraDB data and logs. Percona XtraBackup will use backup locks as a lightweight alternative. This feature is available in Percona Server 5.6+. Percona XtraBackup Use this to automatically copy non-InnoDB data to avoid blocking DML queries that modify InnoDB tables. After FLUSH TABLES WITH READ LOCK

, xtrabackup will use to block all operations that may change the binary log location or or report. Then xtrabackup will finish copying the REDO log files and getting the binary log coordinates. After this is completed xtrabackup will unlock the binary log and tables. LOCK BINLOG FOR BACKUPExec_Master_Log_PosExec_Gtid_SetSHOW MASTER/SLAVE STATUS

Finally, the binary log position will be printed to STDERR and xtrabackup will exit returning 0 if everything is OK.

It should be noted that STDERR's xtrabackup is not written in any file. You have to redirect it to a file, for example. xtrabackup OPTIONS2> backupout.log

It will also create the following files in the backed up directory.

During the preparation phase, Percona XtraBackup uses the replicated transaction log files to perform crash recovery on the replicated data files. Once this is done, the database can be restored and used.

The backed up MyISAM and InnoDB tables will eventually be consistent with each other because the data for InnoDB will be rolled forward after the preparation (recovery) process to the point where the backup completes without rolling back to that point to begin. This point in time matches the position taken, so the MyISAM data and the prepared InnoDB data are in sync. FLUSH TABLES WITHREAD LOCK

In short, these tools allow you to perform operations such as streaming and incremental backups by copying data files, copying log files, and applying logs to various combinations of data.

The above is the detailed content of Detailed introduction to the backup principle of xtrabackup. For more information, please follow other related articles on the PHP Chinese website!