MySQL backup and recovery-incremental backup and recovery through percona-xtrabackup _ MySQL

MySQL backup and recovery-percona-xtrabackup incremental backup and recovery bitsCN.com article 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 root 4096 Sep 10 22:13 2013-09-10_22-12-50drwxr-xr-x. 9 root root 4096 Sep 10 22:16 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)

Step 8: perform incremental backup again. One full backup, three incremental backups, so there are four backup folders

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

Step 9: simulate data loss

mysql> drop database larrydb;Query OK, 2 rows affected (0.02 sec)

Step 10: check all the data. It can be seen that the size of the disk occupied by incremental backup and full backup files is very different. Obviously, full backup occupies a large amount of disk space, while incremental backup occupies less disk space.

[root@serv01 databackup]# innobackupex --apply-log --redo-only /databackup/2013-09-10_22-12-50/InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Inc 2009-2012.  All Rights Reserved.……xtrabackup: starting shutdown with innodb_fast_shutdown = 1130910 22:23:35  InnoDB: Starting shutdown...130910 22:23:36  InnoDB: Shutdown completed; log sequence number 2098700130910 22:23:36  innobackupex: completed OK![root@serv01 databackup]# du -sh ./*22M ./2013-09-10_22-12-501.5M  ./2013-09-10_22-15-451.5M  ./2013-09-10_22-19-211.5M  ./2013-09-10_22-21-42

Step 2: merge the incremental backup data that was created for the first time to the full backup

[root@serv01 databackup]# innobackupex --apply-log --redo-only --incremental /databackup/2013-09-10_22-12-50/ --incremental-dir=/databackup/2013-09-10_22-15-45/InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Inc 2009-2012.  All Rights Reserved.……innobackupex: Copying '/databackup/2013-09-10_22-15-45/hello/db.opt' to '/databackup/2013-09-10_22-12-50/hello/db.opt'130910 22:32:26  innobackupex: completed OK!

Step 2: merge the incremental backup data for the second operation to the full backup

[root@serv01 databackup]# innobackupex --apply-log --redo-only --incremental /databackup/2013-09-10_22-12-50/ --incremental-dir=/databackup/2013-09-10_22-19-21/

Step 3: merge the incremental backup data that is made for the third time to the full backup

[root@serv01 databackup]# innobackupex --apply-log --redo-only --incremental /databackup/2013-09-10_22-12-50/ --incremental-dir=/databackup/2013-09-10_22-21-42/

Step 3: stop MySQL during restoration, so we will stop MySQL.

[root@serv01 databackup]# /etc/init.d/mysqld stop ERROR! MySQL server PID file could not be found![root@serv01 databackup]# pkill -9 mysql

Step 2: restore data. Note that the specified folder here is 2013-09-10_22-12-50.

[Root @ serv01 databackup] # innobackupex -- copy-back/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. this software is published underthe gnu general public license Version 2, June 1991. IMPORTANT: Please check that the copy-back run completes successfully. at the end of a successful copy-back run in Nobackupex prints "completed OK! ". Original data directory is not empty! At/usr/bin/innobackupex line 571. # report the above error to delete something under the data Directory [root @ serv01 data] # pwd/usr/local/mysql/data [root @ serv01 data] # lsgame ib_logfile0 mysql mysql-bin.000003 performance_schema testhello ib_logfile1 mysql-bin.000001 mysql-bin.000004 serv01.host.com. err xtrabackup_binlog_pos_innodbibdata1 mnt mysql-bin.000002 mysql-bin.index serv01.host.com. pid [root @ serv01 data] # rm-rf * # recover data again, change the owner of the database data directory and the group [root @ serv01 databackup] # innobackupex -- copy-back/databackup/2013-09-10_22-12-50/[root @ serv01 data] # lltotal 18464drwxr-xr-x. 2 root 4096 Sep 10 gamedrwxr-xr-x. 2 root 4096 Sep 10 hello-rw-r -----. 1 root 18874368 Sep 10 ibdata1drwxr-xr-x. 2 root 4096 Sep 10 larrydbdrwxr-xr-x. 2 root 4096 Sep 10 mntdrwxr-xr-x. 2 root 4096 Sep 10 mysqldrwxr-xr-x. 2 root 4096 Sep 10 performance_schemadrwxr-xr-x. 2 root 4096 Sep 10 test-rw-r --. 1 root 24 Sep 10 xtrabackup_binlog_pos_innodb [root @ serv01 data] # chown mysql. mysql/usr/local/mysql/data/-R

Step 2: start the service

[root@serv01 data]# /etc/init.d/mysqld startStarting MySQL.. SUCCESS! 

Step 2: log on to the database and view the data

[root@serv01 data]# mysql -uroot -p123456mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || game               || hello              || larrydb            || mnt                || mysql              || performance_schema || test               |+--------------------+8 rows in set (0.00 sec)mysql> select * from larrydb.class;+------+-------+| cid  | cname |+------+-------+|    1 | linux ||    2 | dab   ||    3 | Devel |+------+-------+3 rows in set (0.00 sec)mysql> select * from larrydb.stu;+------+----------+------+| sid  | sname    | cid  |+------+----------+------+|    1 | larry007 |    1 ||    2 | larry02  |    1 ||    3 | larry03  |    1 ||    4 | larry04  |    1 |+------+----------+------+4 rows in set (0.00 sec)

My mailbox: wgbno27@163.com Sina Weibo: @ jutdb public platform: JustOracle (No.: justoracle) database technology exchange group: 336882565 (when adding Group verification From csdn xxx)All is wellDecember 1, 2013By Larry Wen

BitsCN.com