MySQL backup and recovery-percona-xtrabackup for Incremental backup and recovery

First, use the percona-xtrabackup tool to perform full backup of the database, and then perform Incremental backup of the data after each database data update.

First, use the percona-xtrabackup tool to perform full backup of the database, and then perform Incremental backup of the data after each database data update.

1. Review

In the previous article, we talked about the use of percona-xtrabackup software. This article describes how percona-xtrabackup achieves Incremental backup and recovery.

2. Incremental Backup

How to Implement Incremental backup and recovery using percona-xtrabackup

First, use the percona-xtrabackup tool to perform full backup for the database, and then perform Incremental backup after each database data update. Each Incremental backup is based on the previous backup. During recovery, the Incremental backup data is restored to the full backup, and the merged data is used for data recovery.

Iv. percona-xtrabackup for Incremental backup and recovery

Step 1: Full backup

[Root @ serv01 databackup] # innobackupex -- user = root -- password = 123456/databackup/

Step 2: View data

Mysql> use larrydb; Database changedmysql> select * from class; + ------ + ------- + | cid | cname | + ------ + ------- + | 1 | linux | 2 | dab | 3 | Devel | + ------ + ------- + 3 rows in set (0.00 sec) mysql> select * from stu; + ------ + ---------- + ------ + | sid | sname | cid | + ------ + ---------- + ------ + | 1 | larry007 | 1 | + ------ + ---------- + ------ + 1 row in set (0.00 sec)

Step 3: update data

Mysql> insert into stu values (2, 'larry02', 1); Query OK, 1 row affected (0.00 sec) mysql> select * from stu; + ------ + ---------- + ------ + | sid | sname | cid | + ------ + ---------- + ------ + | 1 | larry007 | 1 | 2 | larry02 | 1 | + ------ + ---------- + ------ + 2 rows in set (0.00 sec)

Step 4: perform full backup and the first Incremental backup for Incremental backup. Therefore, there are two backup folders. Each Incremental backup is for the last backup.

# -- Incremental: incremental Backup folder # -- incremental-dir: for which incremental Backup [root @ serv01 databackup] # innobackupex -- user = root -- password = 123456 -- incremental/databackup/-- incremental-dir/databackup/2013-09-10_22-12-50/InnoDB Backup Utility v1.5.1- xtrabackup; copyright 2003,200 9 Innobase Oyand Percona Inc 2009-2012. all Rights Reserved. ...... Innobackupex: Backup created in directory '/databackup/2013-09-10_22-15-45' innobackupex: MySQL binlog position: filename 'mysql-bin.000004 ', position 353130910 22:16:04 innobackupex: completed OK! [Root @ serv01 databackup] # lltotal 8drwxr-xr-x. 9 root 4096 Sep 10 2013-09-10_22-12-50drwxr-xr-x. 9 root 4096 Sep 10 2013-09-10_22-15-45

Step 5: insert data again

Mysql> insert into stu values (3, 'larry03', 1); Query OK, 1 row affected (0.00 sec) mysql> select * from stu; + ------ + ---------- + ------ + | sid | sname | cid | + ------ + ---------- + ------ + | 1 | larry007 | 1 | 2 | larry02 | 1 | 3 | larry03 | 1 | + ------ + ---------- + ------ + 3 rows in set (0.00 sec)

Step 6: Perform Incremental backup again

[Root @ serv01 databackup] # lltotal 8drwxr-xr-x. 9 root 4096 Sep 10 22:13 2013-09-10_22-12-50drwxr-xr-x. 9 root 4096 Sep 10 2013-09-10_22-15-45 [root @ serv01 databackup] # innobackupex -- user = root -- password = 123456 -- incremental/databackup/-- incremental-dir/databackup/2013-09-10_22-15-45/

Step 7: insert data again

Mysql> insert into stu values (4, 'larry04 ', 1); Query OK, 1 row affected (0.00 sec) mysql> select * from stu; + ------ + ---------- + ------ + | sid | sname | cid | + ------ + ---------- + ------ + | 1 | larry007 | 1 | 2 | larry02 | 1 | 3 | larry03 | 1 | 4 | larry04 | 1 | + ------ + ---------- + ------ + 4 rows in set (0.00 sec)

For more details, please continue to read the highlights on the next page: