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

Source: Internet
Author: User
Tags mysql backup percona
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:

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.