Principles of full backup and Incremental backup for MySQL Databases

MySQL databaseBackup operations includeFull backupAndIncremental BackupThis article describes the principles of Incremental backup and complete backup. Next, let's take a look at this part.

Principle of full backup:

For InnoDB, XtraBackup is backed up based on the crash-recovery function of InnoDB.

Crash-recovery is like this: InnoDB maintains a redo log, also known as the transaction log, also known as the transaction log, which contains all the changes to InnoDB data. When InnoDB is started, check datafile and transaction log first, then apply all committed transactions and roll back all uncommitted transactions.

XtraBackup does not lock the table during backup, but copies InnoDB data one page at a time. At the same time, XtraBackup has another thread monitoring the transactions log. Once the log changes, copy the changed log pages (because the transactions log file size is limited, after it is fully written, it will start from the beginning, and the new data may overwrite the old data, so copy it immediately once it changes ). After all data files are copied, stop copying logfiles.

XtraBackup uses its built-in InnoDB database to open InnoDB data files in read-write mode, and then reads and writes 1 MB (1 MB/16 KB = 64 page) data each time, page by page. At the same time, use the buf_page_is_upted () function of InnoDB to check whether the data on this page is normal. If the data is normal, copy the data. If the data is abnormal, re-read the data. Up to 10 re-reads, if it still fails, the backup fails to exit. The principle of copying a transactions log is the same, except that the data read and written every time is KB (KB/16 KB = 32 page.

Because XtraBackup's built-in InnoDB library is rw when opening files, users running XtraBackup must have read and write permissions on InnoDB data files.

Because XtraBackup needs to copy a large amount of data from the file system, it tries its best to use posix_fadvise () to tell the OS not to cache read data (because the data will not be reused ), this improves performance. If you want to cache a large amount of data, it will put a lot of pressure on the virtual memory of the OS. other processes, such as mysqld, may be pushed out by swap. In addition, XtraBackup tries its best to pre-read data.

Because the table is not locked, the copied data is inconsistent. Data Consistency is achieved through crash-recovery during restoration.

For MyISAM, XtraBackup locks all tables first, and then copies all files.

Principle of Incremental Backup:

In both the full backup and Incremental backup files, the xtrabackup_checkpoints file records the LSN of the checkpoint when the backup is complete. During a new Incremental backup, XtraBackup compares whether the LSN of each page in the tablespace is greater than the LSN of the last backup. If yes, it backs up the page and records the LSN of the current checkpoint.

The above is an introduction to the principles of MySQL database full backup and Incremental backup. This article will introduce you here. I hope this introduction will benefit you!