Backup principle of xtrabackup in MySQL

Backup principle of xtrabackup in MySQL

The official Xtrabackup is:

Http://www.percona.com/software/percona-xtrabackup.

Xtrabackup contains two main tools, namely Xtrabackup and Innobackupex, which are distinguished by the following:

1 Xtrabackup can only back up tables of InnoDB and xtradb two engines, not the MyISAM engine

2 Innobackupex is a Perl script that encapsulates Xtrabackup, which supports both InnoDB and MyISAM backups, but requires a global read lock for MyISAM backups. And there's the MyISAM. Incremental backups are not supported.

Schematic diagram of the backup process for the Innobackupex tool

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M00/9B/55/wKiom1lh5BLx9EeFAAFqJE2_UBo591.jpg "title=" 2017-07-09_155308.jpg "alt=" Wkiom1lh5blx9eefaafqje2_ubo591.jpg "/>

, when the backup started

1 First will start a xtrabackup_log background detection process, real-time detection of the changes in MySQL redo, once the discovery Redo has a new log write, immediately writes the log to the log file Xtrabackup_log 2 Copy the InnoDB data file and the system Tablespace file idbdata1 to the corresponding place with the default timestamp as the backup directory 3 after the replication finishes, execute the Flush table with the read lock action 4 copy. frm. myd. myi file 5 And at this moment to get the binary log position 6 to unlock the table unlock Tables7 stop xtrabackup_log process Full-database recovery This phase will start xtrabackup embedded InnoDB instance, Replay the Xtrabackup log xtrabackup_log, apply the committed transaction information change to InnoDB data or tablespace, and roll back uncommitted transactions

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/9B/55/wKioL1lh5E2TbZ-7AACE0FG0D4k802.jpg "title=" 2017-07-09_155622.jpg "alt=" Wkiol1lh5e2tbz-7aace0fg0d4k802.jpg "/>

Incremental backup

An incremental backup is primarily done by copying pages with changes in the InnoDB (that is, LSN is greater than the LSN number in xtrabackup_checkpoints). Incremental backups are based on a fully-prepared, first-time incremental backup is based on the last full-time, after which each increment is based on the last increment, culminating in the increase in consistency, doubling the process, and the full preparation is similar, the difference lies in the second step

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/9B/55/wKioL1lh5GuA-B2xAAFH7_lDh3U535.jpg "title=" 2017-07-09_155651.jpg "alt=" Wkiol1lh5gua-b2xaafh7_ldh3u535.jpg "/>

Recovery of incremental backups

Similar to a full-library restore, it also takes two steps

1 Recovery of data files divided into 3 parts full backup incremental and Xtrabackup_log

2 Rollback of uncommitted transactions

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/9B/55/wKioL1lh5Iix38qCAACuPmccu3o754.jpg "title=" 2017-07-09_155726.jpg "alt=" Wkiol1lh5iix38qcaacupmccu3o754.jpg "/>

Use cases of Innobackupex

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/9B/55/wKiom1lh5KKBQsjTAAA8hUfupb8778.jpg "title=" 2017-07-09_155811.jpg "alt=" Wkiom1lh5kkbqsjtaaa8hufupb8778.jpg "/>

1 Creating a backup user

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/9B/55/wKiom1lh5LWi3kmjAABIoGmhsmE760.jpg "title=" 2017-07-09_155835.jpg "alt=" Wkiom1lh5lwi3kmjaabiogmhsme760.jpg "/>

Make the database fully prepared

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/9B/55/wKioL1lh5NKhjUSUAAAimVXNPm0105.jpg "title=" 2017-07-09_155905.jpg "alt=" Wkiol1lh5nkhjusuaaaimvxnpm0105.jpg "/>

Use the following parameters for a full-library backup

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/9B/55/wKiom1lh5OTz-jCVAAHUqrChj5k724.jpg "title=" 2017-07-09_155930.jpg "alt=" Wkiom1lh5otz-jcvaahuqrchj5k724.jpg "/>

View the corresponding generated files

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/9B/55/wKioL1lh5P-i4_wOAAHcwDEQ1EY575.jpg "title=" 2017-07-09_155955.jpg "alt=" Wkiol1lh5p-i4_woaahcwdeq1ey575.jpg "/>

Several documents to be aware of

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/9B/55/wKiom1lh5RCwmHiqAAC4ogRJIJw502.jpg "title=" 2017-07-09_160018.jpg "alt=" Wkiom1lh5rcwmhiqaac4ogrjijw502.jpg "/>

Delete a database for full-Library recovery

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/9B/55/wKioL1lh5SPxPw38AAAoPzOa2jc849.jpg "title=" 2017-07-09_160041.jpg "alt=" Wkiol1lh5spxpw38aaaopzoa2jc849.jpg "/>

Close the database

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/9B/55/wKioL1lh5TOg-6CoAAB200IMMjI851.jpg "title=" 2017-07-09_160104.jpg "alt=" Wkiol1lh5tog-6coaab200immji851.jpg "/>

Recovery

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/9B/55/wKiom1lh5UqToLoUAADAU8aTrTU922.jpg "title=" 2017-07-09_160133.jpg "alt=" Wkiom1lh5uqtolouaadau8atrtu922.jpg "/>

The corresponding directory is the Innobackupex full backup to create their own directory

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/9B/55/wKioL1lh5WPAolQRAAE6q4s4Ydw620.jpg "title=" 2017-07-09_160210.jpg "alt=" Wkiol1lh5wpaolqraae6q4s4ydw620.jpg "/>

Change permissions

[Email protected]_03 tmp]# chown-r mysql.mysql/data/3306/data/

Start mysqld

[[Email protected]_03 tmp]#/etc/init.d/mysqld Start

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/9B/55/wKiom1lh5ZGBLmvuAAFEo7oZ5u0281.jpg "title=" 2017-07-09_160317.jpg "alt=" Wkiom1lh5zgblmvuaafeo7oz5u0281.jpg "/>

Discovery data is already successfully restored

Fully prepared first

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/9B/55/wKioL1lh5b6TT1a_AABsqVBfPxY623.jpg "title=" 2017-07-09_160401.jpg "alt=" Wkiol1lh5b6tt1a_aabsqvbfpxy623.jpg "/>

Incremental backup

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/9B/55/wKioL1lh5dKQleBnAAF8V7jd6wo026.jpg "title=" 2017-07-09_160431.jpg "alt=" Wkiol1lh5dkqlebnaaf8v7jd6wo026.jpg "/>

Insert data at this time

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/9B/55/wKioL1lh5fSR6wh7AAA1NTDr8Dw307.jpg "title=" 2017-07-09_160455.jpg "alt=" Wkiol1lh5fsr6wh7aaa1ntdr8dw307.jpg "/>

Incremental Backup 2

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/9B/55/wKioL1lh5gDAPIkNAAChi-o3IWw538.jpg "title=" 2017-07-09_160522.jpg "alt=" Wkiol1lh5gdapiknaachi-o3iww538.jpg "/>

Recovery of incremental backups

Recovery of incremental backups requires 3 steps

1 Recovering a full backup

2 Restore an incremental backup to a full backup (incremental backup to start recovery to add the--redo-only parameter to the last incremental backup to remove--redo-only)

3 Restore the entire full backup, roll back uncommitted data

Simulation test

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/9B/55/wKiom1lh5jzQEBjsAAD-pUJSH-k755.jpg "title=" 2017-07-09_160606.jpg "alt=" Wkiom1lh5jzqebjsaad-pujsh-k755.jpg "/>

After testing, recovery is still relatively simple.

This article is from the "Beloved Linux" blog, so be sure to keep this source http://mpflinux.blog.51cto.com/12173924/1945677

Backup principle of xtrabackup in MySQL