Xtrabackup backup Principle and Application Example

1. Introduction and installation of xtrabackup

Xtrabackup is a free online open source database backup solution, applicable to all versions of MySQL and mariadb. Xtrabackup supports InnoDB hot backup and is a physical backup tool. Full backup, partial backup, Incremental backup, and differential backup can be performed on the database.

1. Implementation principle of backup

The InnoDB Storage engine stores data based on table spaces. A tablespace is divided into multiple blocks (Disk areas) and data is distributed to these disk areas, each disk area has its own number. Each time the data is modified, the log serial number increases by 1. During full backup, a physical backup is performed for all disk areas, incremental Backup backs up the modified disk according to the epoch changes caused by the modified disk area number. For example, in the disk area of a database InnoDB engine, full backup backs up all disk areas 1-5. Later, the database is modified. The modified data is data in disk areas 1 and 3, during Incremental backup, Disk 1 and Disk 3 are backed up. During restoration, 1-5 in the full backup is restored, and 1 and 3 in the Incremental Backup are restored.

It should be noted that in xtrabackup, database restoration is performed only after the full backup is merged with all the incremental backups, not during restoration, merge all data before restoration.

2. Install xtrabackup

Percona official site installation: https://www.percona.com/downloads/XtraBackup/LATEST/

After the download is complete, the dependency package is automatically installed with Yum:

~]# yum install percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm percona-xtrabackup-test-24-2.4.7-2.el7.x86_64.rpm

1 ~] # Rpm-QL percona-xtrabackup-24 2/usr/bin/innobackupex # main program, core components, this tool is generally used for backup 3/usr/bin/xbcloud 4/usr/bin/xbcloud_osenv 5/usr/bin/xbcrypt 6/usr/bin/xbstream # Streaming tools, stream processing of data, easy transfer save 7/usr/bin/xtrabackup 8/usr/share/doc/percona-xtrabackup-24-2.4.7 9/usr/share/doc/percona-xtrabackup-24-2.4.7/copying10/usr/share/man/Man1/innobackupex.1.gz11 /usr/share/man/Man1/xbcrypt.1.gz12/usr/share/man/Man1/xbstream.1.gz13/usr/share/man/Man1/xtrabackup.1.gz

2. Demonstration of xtrabackup

The core component of xtrabackup supporting Incremental backup is lsn (log serial number ). Merge operations before recovery.

1. Full backup

Full backup format:

innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/

Option:

-- No-timestamp: Save the backup directory without Timestamp

-- Defaults-file =/tmp/other-my.cnf: configuration file path

/Path/to/BACKUP-DIR/: Specifies the directory in which the backup is stored

2. Full recovery

Preparing must be performed before full recovery, and rollback or commit the backup transaction based on the actual situation:

innobackupex --apply-log /path/to/BACKUP-DIR

-- Apply-Log: determines which commits and rollback Based on the transaction log

Then execute:

Innobackupex -- copy-back BASE-DIR # BASE-DIR is the backup path

3. Incremental Backup

Incremental Backup must be implemented based on full backup. In the command for the first Incremental backup, you must specify which full backup is used for Incremental backup, subsequent Incremental backup is based on the previous Incremental backup, while each differential backup is based on full backup,

innobackupex --incremental /data/backups --incremental-basedir=BASEDIR

-- Incremental/data/backups: Specifies the path to save the Incremental backup.

-- Incremental-basedir = basedir: Specifies which full backup is used for Incremental backup.

After each backup, xtrabackup will save a file, which records the Backup Type, start point and end point of the lsn. Example:

1 backup_type = full-backuped2 from_lsn = 03 to_lsn = 1291135

4. incremental recovery

When restoring full + incremental, full transaction logs are backed up and incremental transaction logs are backed up. In this case, you need to restore all committed transactions, the uncommitted parts do not need to be processed, and are rolled back after being merged with the incremental transaction, because the full transaction may change in the incremental transaction.

Perform full operations first ,:

innobackupex --apply-log --redo-only BASE-DIR

-- Redo-only: only restore the committed

Then, merge the full and incremental operations:

innobackupex --apply-log BASE-DIR --incremental-dir=INCREMENTAL-DIR2

Last rollback:

innobackupex --apply-log BASE-DIR

Restore:

innobackupex --copy-back BASE-DIR

View the innobackupex Command help:

innobackupex --help

 

Xtrabackup backup Principle and Application Example