XtraBackup and InnoBackupex online backup

XtraBackup and InnoBackupex online backup

XtraBackup online backup:

• A hot standby tool dedicated to InnoDB and XtraBackup. It is a C language program dedicated to backing up InnoDB and XtraDB engine objects.
• Efficient, complete, and available backup Sets
• Transactions are not blocked during Backup Task execution.
• Saves disk space and reduces network bandwidth usage
• Automatic Backup set verification mechanism
• Faster recovery

[Root @ mysql1/] # rpm-ivh percona-xtrabackup-2.2.3-4982.el6.x86_64.rpm

[Mysql @ mysql1 ~] $ Echo "export PATH =/usr/bin: \ $ PATH">/home/mysql/. bash_profile

(System @ localhost) [(none)]> create user xtrabk @ 'localhost' identified by 'onlybackup ';

(System @ localhost) [(none)]> grant reload, lock tables, Replication client, super on *. * to xtrabk @ 'localhost ';

[Mysql @ mysql1 ~] $ Xtrabackup-help

Back up data using the xtrabackup command

The xtrabackup command has two modes: backup and prepara)

-- Backup specifies the current operation mode. backup indicates creating a backup set.

-- Target-dir specifies the storage path of the Backup set.

-- Defaults-file Read parameters from the MySQL option file

[Mysql @ mysql1 ~] $ Xtrabackup -- defaults-file =/data/mysqldata/3306/my. cnf -- backup -- target-dir =/data/mysqldata/backup/full_bak

 

Use the innobackupex command to back up non-InnoDB objects. innobackupex internally calls xtrabackup to back up the objects.

[Mysql @ mysql1 ~] $ Innobackupex -- defaults-file =/data/mysqldata/3306/my. cnf -- user = xtrabk -- password = 'onlybackup '/data/mysqldata/backup/

Create Incremental Backup:

After completing the full backup in the previous step, modify the data in the InnoDB table and perform the first Incremental backup.

[Mysql @ mysql1 ~] $ Innobackupex -- defaults-file =/data/mysqldata/3306/my. cnf -- user = xtrabk -- password = 'onlybackup' -- incremental-basedir =/data/mysqldata/backup/2014-07-10_09-29-32/data/mysqldata/backup_inc

1) after completing the first Incremental backup, continue to modify the data in the InnoDB table and perform the second Incremental backup.

[Mysql @ mysql1 ~] $ Innobackupex -- defaults-file =/data/mysqldata/3306/my. cnf -- user = xtrabk -- password = 'onlybackup' -- incremental-basedir =/data/mysqldata/backup/2014-07-10_09-29-32/data/mysqldata/backup_inc

This method corresponds to the cumulative incremental backup of Oracle

2) After completing the first Incremental backup, continue to modify the data in the InnoDB table and perform the second Incremental backup.

[Mysql @ mysql1 ~] $ Innobackupex -- defaults-file =/data/mysqldata/3306/my. cnf -- user = xtrabk -- password = 'onlybackup '-- incremental-basedir =/data/mysqldata/backup_inc/2014-07-10_09-31-36/data/mysqldata/backup_inc

This method corresponds to incremental differential backup of Oracle

-- Incremental: tells xtrabackup that the incremental backup is to be created this time.

-- Incremental-basedir: Specifies a full backup path, which serves as the basis for incremental backup.

-- Incremental-lsn: Specifies the LSN at the start of the backup.

 

Log Sequence Number of InnoDB Log serial Number LSN. Each Page of InnoDB stores an LSN, which identifies the last modification time of the Page. Incremental backup is based on this LSN, because each backup (including xtrabackup and innobackupex), XtraBackup creates an xtrabackup_checkpoints file in the backup set. The last modified LSN is recorded in the file. When creating an incremental backup set, you only need to find the xtrabackup_checkpoints file from the last backup set, read the latest LSN, and then select the page where the LSN is greater than the serial number when creating the Incremental Backup set, and the binary logs generated during this period do not even need to compare the full backup set with the data files of the current database.

 

XtraBackup backup mechanism:

XtraBackup is essentially based on the InnoDB fault recovery (crash-recovery) mechanism. It first replicates InnoDB data files and may still be reading and writing data during replication, the copied files may be inconsistent. Therefore, during the backup process, you need to regularly scan logs and make records, and then perform fault recovery through the backup log files, restore the file to a consistent state and make the database available. The core is the redo log maintained by InnoDB ). XtraBackup records the current log serial number (LSN) at startup, then begins to copy the data file, and XtraBackup runs a background process, monitors transaction logs, and copies new modifications. This operation will always be executed during the XtraBackup backup, that is, the log scanned up to information, to ensure that all database modifications occurred during the backup are recorded. The next step is the preparation process (prepare process). In this step, XtraBackup performs fault recovery on the copied data file and restores the database to the available state.

 

Perform recovery: full recovery and incremental recovery:

Prepare for recovery (prepare): Prepares for recovery. Backup sets cannot be used directly. All processes that require preparation for backup Sets

The parameter for xtrabackup is -- prepare, and the parameter for innobackupex is -- apply-log.

Copy-back: After the backup set is ready, it can be restored.

Xtrabackup does not have a special description. For simple cp/mv processes, the parameter corresponding to innobackupex is -- copy-back, which is used to restore the specified backup set to the specified path.

 

First, make preparations for full backup recovery.

[Mysql @ mysql1 ~] $ Innobackupex -- defaults-file =/data/mysqldata/3306/my. cnf -- apply-log -- redo-only/data/mysqldata/backup/2014-07-10_09-29-32/

-- Apply-log reads the configuration information from the specified option file and applies the log, which indicates that the backup set is to be restored.

-- Redo-only if there are other Incremental Backup sets to be processed after the backup set for preparation is completed, you must specify this parameter

 

Run the innobackupex command to apply Incremental backup. The backup set to be operated this time is not the last one. You need to specify the -- redo-only parameter.

[Mysql @ mysql1 ~] $ Innobackupex -- defaults-file =/data/mysqldata/3306/my. cnf -- apply-log/data/mysqldata/backup/2014-07-10_09-29-32/-- incremental-dir =/data/mysqldata/backup_inc/2014-07-10_09-31-36/

 

Run the innobackupex command to apply Incremental backup. The backup set to be operated this time is the last one. You do not need to specify the -- redo-only parameter.

[Mysql @ mysql1 ~] $ Innobackupex -- defaults-file =/data/mysqldata/3306/my. cnf -- apply-log/data/mysqldata/backup/2014-07-10_09-29-32/-- incremental-dir =/data/mysqldata/backup_inc/2014-07-10_09-35-13/

Do not interrupt the task when preparing the backup set for recovery. Otherwise, the backup set may be inconsistent. Because XtraBackup is directly prepared in the backup set, once an exception occurs, it cannot be recovered. We recommend that you back up the backup set once before performing this operation.

We recommend that you execute innobackupex -- apply-log again.

[Mysql @ mysql1 ~] $ Innobackupex -- defaults-file =/data/mysqldata/3306/my. cnf -- apply-log/data/mysqldata/backup/2014-07-10_09-29-32/

Recovery of execution files

[Mysql @ mysql1 ~] $ Innobackupex -- defaults-file =/data/mysqldata/3306/my. cnf -- copy-back/data/mysqldata/backup/2014-07-10_09-29-32/

 

The recovery operation can be performed on any machine. If you have a complete backup set, you can use XtraBackup as a database migration tool.

 

Package and compress backup Sets

XtraBackup supports stream mode, which can directly output backup data to a specified format for processing, such as tar or xbstream.

 

[Mysql @ mysql1 ~] $ Innobackupex -- defaults-file =/data/mysqldata/3306/my. cnf -- user = xtrabk -- password = onlybackup -- stream = tar/tmp | gzip->/data/mysqldata/backup/xtra_fullbackup.tar.gz

Full backup

Innobackupex -- defaults-file =/data/mysqldata/3306/my. cnf -- user = xtrabk -- password = 'onlybackup '/data/mysqldata/backup/

 

Modify data

(System @ localhost) [mysql]> create table mytable (a int );

Query OK, 0 rows affected (0.06 sec)

(System @ localhost) [mysql]> insert into mytable values (1 );

Query OK, 1 row affected (0.04 sec)

(System @ localhost) [mysql]> insert into mytable values (2 );

Query OK, 1 row affected (0.01 sec)

(System @ localhost) [mysql]> insert into mytable values (3 );

Query OK, 1 row affected (0.00 sec)

(System @ localhost) [mysql]> insert into mytable values (4 );

Query OK, 1 row affected (0.00 sec)

(System @ localhost) [mysql]> insert into mytable values (5 );

Query OK, 1 row affected (0.01 sec)

 

Perform the first Incremental Backup

Innobackupex -- defaults-file =/data/mysqldata/3306/my. cnf -- user = xtrabk -- password = 'onlybackup' -- incremental-basedir =/data/mysqldata/backup/2015-02-14_19-08-11/data/mysqldata/backup_inc

 

Modify data

(System @ localhost) [mysql]> insert into mytable values (6 );

Query OK, 1 row affected (0.01 sec)

(System @ localhost) [mysql]> insert into mytable values (7 );

Query OK, 1 row affected (0.01 sec)

(System @ localhost) [mysql]> insert into mytable values (8 );

Query OK, 1 row affected (0.00 sec)

(System @ localhost) [mysql]> insert into mytable values (9 );

Query OK, 1 row affected (0.00 sec)

(System @ localhost) [mysql]> insert into mytable values (10 );

Query OK, 1 row affected (0.00 sec)

 

(System @ localhost) [mysql]> select * from mytable;

+ ------ +

| A |

+ ------ +

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 7 |

| 8 |

| 9 |

| 10 |

+ ------ +

10 rows in set (0.00 sec)

 

Perform the second Incremental Backup

Innobackupex -- defaults-file =/data/mysqldata/3306/my. cnf -- user = xtrabk -- password = 'onlybackup '-- incremental-basedir =/data/mysqldata/backup_inc/2015-02-14_19-10-14/data/mysqldata/backup_inc

 

Restore

Process full recovery

Innobackupex -- defaults-file =/data/mysqldata/3306/my. cnf -- apply-log -- redo-only/data/mysqldata/backup/2015-02-14_19-08-11/

 

Perform the first Incremental Backup Recovery

Innobackupex -- defaults-file =/data/mysqldata/3306/my. cnf -- apply-log -- redo-only/data/mysqldata/backup/2015-02-14_19-08-11/-- incremental-dir =/data/mysqldata/backup_inc/2015-02-14_19-10-14/

 

Perform the second Incremental Backup Recovery

Innobackupex -- defaults-file =/data/mysqldata/3306/my. cnf -- apply-log -- redo-only/data/mysqldata/backup/2015-02-14_19-08-11/-- incremental-dir =/data/mysqldata/backup_inc/2015-02-14_19-12-21/

 

We recommend that you execute innobackupex -- apply-log again.

Innobackupex -- defaults-file =/data/mysqldata/3306/my. cnf -- apply-log/data/mysqldata/backup/2015-02-14_19-08-11/

Execute copy operation

Innobackupex -- defaults-file =/data/mysqldata/3306/my. cnf -- copy-back/data/mysqldata/backup/2015-02-14_19-08-11/

Data Verification

MySQL management-using XtraBackup for Hot Backup

MySQL open-source backup tool Xtrabackup backup deployment

MySQL Xtrabackup backup and recovery

Use XtraBackup to implement MySQL master-slave replication and quick deployment [master-slave table lock-free]

Install and use Xtrabackup from Percona to back up MySQL

XtraBackup details: click here
XtraBackup: click here

This article permanently updates the link address: